MySQL查询JSON的详解

一、MySQL查询JSON内字段

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它广泛用于前后端数据交互。MySQL从5.7.8版本开始支持JSON数据类型，使得我们可以将JSON数据存储在数据库中，并且使用SQL查询语句对其进行操作。查询JSON内的字段非常简单，只需使用->或者->>操作符即可。

SELECT json_column->'$.field_name' FROM table_name;
SELECT json_column->>'$.field_name' FROM table_name;

其中，->操作符返回的是JSON对象或者数组的某个字段的JSON值，而->>操作符返回的是该字段的文本值。

例如，我们有以下的JSON数据：

{
   "id": 1,
   "name": "John",
   "age": 25,
   "address": {
      "city": "New York",
      "state": "NY",
      "zip": "10001"
   }
}

如果我们想查询该JSON数据中的name字段，那么可以使用以下查询语句：

SELECT json_data->'$.name' FROM my_table;

这样就可以得到查询结果为John。

二、MySQL查询JSON数组的值

查询JSON数组的值需要使用JSON_EXTRACT函数，该函数取JSON文本和一个JSON路径表达式作为参数，并返回匹配该路径表达式的JSON值。

SELECT JSON_EXTRACT(json_column, '$[index]') FROM table_name;

其中，index表示JSON数组中所要查询的值的索引。

例如，我们有以下的JSON数组：

[
   {
      "name": "John",
      "age": 25
   },
   {
      "name": "David",
      "age": 30
   },
   {
      "name": "Lucy",
      "age": 28
   }
]

我们想查询该数组中第二个元素的姓名，那么可以使用以下查询语句：

SELECT JSON_EXTRACT(json_data, '$[1].name') FROM my_table;

这样就可以得到查询结果为David。

三、MySQL查询JSON中的数据

查询JSON中的数据需要使用JSON_CONTAINS函数或者JSON_SEARCH函数。

JSON_CONTAINS函数可以判断一个JSON文本或者JSON对象是否包含一个指定的值，返回bool类型的结果。

JSON_CONTAINS(json_column, '["value"]')

JSON_SEARCH函数在JSON文本中查找包含给定字符串的JSON路径，并返回该路径。

JSON_SEARCH(json_column, 'one', 'value')

其中，第一个参数是要查询的JSON文本，第二个参数是搜索类型，可以是’one’, ‘all’, ‘any’，第三个参数是搜索的字符串。

例如，我们有以下的JSON数据：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      "reading",
      "swimming",
      "traveling"
   ]
}

如果我们想查询该JSON数据中是否包含”swimming”这个值，可以使用以下查询语句：

SELECT JSON_CONTAINS(json_data->'$.hobbies', '["swimming"]') FROM my_table;
或者
SELECT JSON_SEARCH(json_data->'$.hobbies', 'one', 'swimming') FROM my_table;

如果查询结果为1，说明该JSON数据中包含”swimming”这个值。

四、MySQL查询JSON的英文字段

查询JSON的英文字段也可以使用->或者->>操作符来实现。例如，我们有以下的JSON数据：

{
   "姓名": "张三",
   "年龄": 25,
   "性别": "男"
}

如果我们想查询该JSON数据中的”年龄”字段，可以使用以下查询语句：

SELECT json_data->'$.年龄' FROM my_table;
或者
SELECT json_data->>'$.年龄' FROM my_table;

这样就可以得到查询结果为25。

五、MySQL查询JSON数组内字段模糊

查询JSON数组内字段模糊需要使用JSON_EXTRACT函数结合LIKE运算符实现。

SELECT * FROM table_name WHERE JSON_EXTRACT(json_column, '$[*].field_name') LIKE '%value%';

其中，$[*].field_name表示JSON数组内的所有field_name字段。

例如，我们有以下的JSON数组：

[
   {
      "name": "John",
      "age": 25
   },
   {
      "name": "David",
      "age": 30
   },
   {
      "name": "Lucy",
      "age": 28
   }
]

如果我们想查询该数组中姓名包含”oh”的元素，可以使用以下查询语句：

SELECT * FROM my_table WHERE JSON_EXTRACT(json_data, '$[*].name') LIKE '%oh%';

这样就可以得到查询结果为John和David。

六、MySQL查询JSON中数组的一个值

查询JSON中数组的一个值需要使用JSON_TABLE函数。

SELECT * FROM JSON_TABLE(json_column, '$.field[*]' COLUMNS(field_name field_path VARCHAR(100) PATH '$', field_value VARCHAR(100) PATH '$')) AS T WHERE T.field_name = 'value';

其中，$.field[*]表示JSON中的一个数组，field_name表示数组元素的键，field_value表示数组元素的值。

例如，我们有以下的JSON数据：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      "reading",
      "swimming",
      "traveling"
   ]
}

如果我们想查询该JSON数据中的”swimming”这个值，可以使用以下查询语句：

SELECT field_value FROM JSON_TABLE(json_data->'$.hobbies', '$[*]' COLUMNS (field_value VARCHAR(50) PATH '$')) AS T WHERE T.field_value = 'swimming';

这样就可以得到查询结果为”swimming”。

七、MySQL查询JSON数据

查询JSON数据可以直接使用SELECT语句，例如：

SELECT json_column FROM table_name;

八、MySQL查询JSON字段数组

查询JSON字段数组需要使用JSON_TABLE函数，例如：

SELECT * FROM table_name, JSON_TABLE(json_column, '$.field[*]' COLUMNS(field_name VARCHAR(100) PATH '$', field_value VARCHAR(100) PATH '$')) AS T WHERE T.field_name = 'value';

其中，$.field[*]表示JSON中的一个数组，field_name表示数组元素的键，field_value表示数组元素的值。

例如，我们有以下的JSON数据：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      {
         "name": "reading",
         "level": "high"
      },
      {
         "name": "swimming",
         "level": "medium"
      },
      {
         "name": "traveling",
         "level": "low"
      }
   ]
}

如果我们想查询该JSON数据中hobbies数组中所有元素的”name”和”level”字段，可以使用以下查询语句：

SELECT T.field_value FROM my_table, JSON_TABLE(json_data->'$.hobbies', '$[*]' COLUMNS (field_name VARCHAR(50) PATH '$.name', field_value VARCHAR(50) PATH '$.level')) AS T WHERE T.field_name = 'name' OR T.field_name = 'level';

这样就可以得到查询结果为：

+-----------+
| field_value |
+-----------+
| reading   |
| high      |
| swimming  |
| medium    |
| traveling |
| low       |
+-----------+

九、MySQL查询JSON字段中的属性

查询JSON字段中的属性需要使用->或者->>操作符来实现。例如，我们有以下的JSON数据：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      {
         "name": "reading",
         "level": "high"
      },
      {
         "name": "swimming",
         "level": "medium"
      },
      {
         "name": "traveling",
         "level": "low"
      }
   ]
}

如果我们想查询该JSON数据中hobbies数组中第一个元素的”name”字段，可以使用以下查询语句：

SELECT json_data->'$.hobbies[0].name' FROM my_table;
或者
SELECT json_data->>'$.hobbies[0].name' FROM my_table;

这样就可以得到查询结果为reading。