MySQL查询JSON|极客笔记

MySQL查询JSON

1. 简介

MySQL是一种流行的关系型数据库管理系统，支持存储和查询结构化数据。从MySQL 5.7版本开始，引入了对JSON数据类型的支持，使得MySQL可以存储和查询JSON格式的数据。

本文将详细介绍如何在MySQL中查询JSON数据，包括JSON的存储、查询和操作等方面。

2. JSON数据类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，具有易读易写的特点。在MySQL中，JSON被定义为一种特殊的数据类型，可以用来存储和操作JSON格式的数据。

2.1 JSON数据类型定义

在MySQL中，可以使用JSON关键字定义一个字段为JSON数据类型。例如，下面的语句定义一个名为data的JSON类型字段：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    data JSON
);

2.2 JSON数据格式

JSON数据格式由键值对组成，使用大括号{}包围。每个键值对由冒号:分隔，多个键值对之间使用逗号,分隔。键通常是一个字符串，值可以是字符串、数字、布尔值、数组或嵌套的JSON对象。

例如，下面是一个简单的JSON对象的示例：

{
    "name": "John",
    "age": 30,
    "email": "john@example.com"
}

3. 查询JSON数据

3.1 基本查询

在MySQL中，可以使用->和->>操作符从JSON字段中提取数据。

->操作符用于提取JSON对象或数组的属性值，返回一个JSON对象或数组。
->>操作符用于提取JSON对象或数组的属性值，并将其转换为文本格式。返回一个文本字符串。

考虑以下示例，其中data字段存储了一个JSON对象：

INSERT INTO my_table (id, data)
VALUES (1, '{"name": "John", "age": 30, "email": "john@example.com"}');

要查询data字段中的属性值，可以使用如下SQL语句：

SELECT data->" $.name" AS name, data->"$ .age" AS age,
       data->"$.email" AS email
FROM my_table;

运行以上SQL语句，将返回以下结果：

+------+-----+------------------+
| name | age | email            |
+------+-----+------------------+
| John |  30 | john@example.com |
+------+-----+------------------+

3.2 条件查询

除了基本查询，还可以在JSON字段上使用条件查询。通过使用MySQL的内置函数，可以在WHERE子句中使用JSON路径来过滤匹配的数据。

例如，考虑以下示例，其中data字段存储了多个JSON对象：

INSERT INTO my_table (id, data)
VALUES (2, '{"name": "Alice", "age": 25, "email": "alice@example.com"}'),
       (3, '{"name": "Bob", "age": 35, "email": "bob@example.com"}'),
       (4, '{"name": "Charlie", "age": 40, "email": "charlie@example.com"}');

要查询年龄大于等于30的记录，可以使用如下SQL语句：

SELECT *
FROM my_table
WHERE data->"$.age" >= 30;

运行以上SQL语句，将返回以下结果：

+------+----------------------------------------------------+
| id   | data                                               |
+------+----------------------------------------------------+
|    2 | {"name": "Alice", "age": 25, "email": "alice@ex... |
|    3 | {"name": "Bob", "age": 35, "email": "bob@example... |
|    4 | {"name": "Charlie", "age": 40, "email": "charlie... |
+------+----------------------------------------------------+

3.3 函数查询

MySQL提供了多个内置函数来操作JSON数据。以下是一些常用的函数：

JSON_OBJECT(key1, value1, key2, value2, ...)
该函数可以创建一个JSON对象。key和value参数成对出现，可以是常量值、表达式或列名。

例如，要查询name和age字段，并将其合并为一个新的JSON对象，可以使用如下SQL语句：
```
SELECT JSON_OBJECT("name", data->" $.name", "age", data->"$ .age") AS user_info
FROM my_table;
```
运行以上SQL语句，将返回以下结果：

    +----------------------------------------------------+
    | user_info                                           |
    +----------------------------------------------------+
    | {"name": "John", "age": 30}                         |
    | {"name": "Alice", "age": 25}                        |
    | {"name": "Bob", "age": 35}                          |
    | {"name": "Charlie", "age": 40}
    +----------------------------------------------------+

JSON_ARRAY(value1, value2, ...)
该函数可以创建一个JSON数组。value参数可以是常量值、表达式或列名。

例如，要查询name字段，并将其合并为一个新的JSON数组，可以使用如下SQL语句：
```
SELECT JSON_ARRAY(data->"$.name") AS names
FROM my_table;
```
运行以上SQL语句，将返回以下结果：

    +----------------+
    | names          |
    +----------------+
    | ["John"]       |
    | ["Alice"]      |
    | ["Bob"]        |
    | ["Charlie"]
    +----------------+

JSON_CONTAINS(path, value, [path2, value2, ...])
该函数用于检查指定的JSON数组或对象是否包含给定的值。

以下示例检查data字段中是否包含age为30的条目：
```
SELECT *
FROM my_table
WHERE JSON_CONTAINS(data, '30', '$.age');
```
运行以上SQL语句，将返回以下结果：

    +------+------+------------------+
    | id   | data |                  |
    +------+------+------------------+
    |    1 | ...  |                  |
    +------+------+------------------+