MySQL查询JSON|极客笔记

MySQL查询JSON

在MySQL 5.7版本以及更新的版本中，引入了对JSON数据类型的支持。这使得MySQL可以方便地存储和查询JSON格式的数据。在实际应用中，JSON数据的使用越来越广泛，因此能够灵活操作JSON数据在数据库中变得更加重要。

本文将介绍如何在MySQL中查询JSON数据。我们将会探讨如何使用JSON函数进行查询、过滤、排序和连接等操作。同时，我们也会演示一些示例代码，让你更加直观地了解如何操作JSON数据。

准备工作

在进行JSON查询之前，首先确保你的MySQL版本在5.7及以上，并且已经创建了包含JSON数据的表。在本文的示例中，我们将使用一个名为employees的表，表结构如下所示：

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

INSERT INTO employees VALUES 
    (1, '{"name": "Alice", "age": 30, "department": "Engineering"}'),
    (2, '{"name": "Bob", "age": 35, "department": "Sales"}'),
    (3, '{"name": "Charlie", "age": 40, "department": "Marketing"}');

以上代码创建了一个名为employees的表，表中包含了三条记录，每条记录都有一个data字段，存储了JSON格式的数据。

查询JSON数据

读取单个JSON字段的值

我们可以使用JSON_EXTRACT函数来读取单个JSON字段的值。假设我们想要查询id为1的员工的姓名，可以执行如下SQL语句：

SELECT JSON_EXTRACT(data, '$.name') AS name
FROM employees
WHERE id = 1;

上述SQL语句中的JSON_EXTRACT(data, '$.name')表示从data字段中提取name字段的值。执行以上SQL语句，将会得到结果：

+-------+
| name  |
+-------+
| Alice |
+-------+

读取多个JSON字段的值

如果需要读取多个JSON字段的值，可以使用逗号分隔多个JSON_EXTRACT函数。例如，我们想要查询所有员工的姓名和部门，可以执行如下SQL语句：

SELECT 
    JSON_EXTRACT(data, ' $.name') AS name, JSON_EXTRACT(data, '$ .department') AS department
FROM employees;

执行以上SQL语句，将会得到结果：

+---------+------------+
| name    | department |
+---------+------------+
| Alice   | Engineering|
| Bob     | Sales      |
| Charlie | Marketing  |
+---------+------------+

JSON查询函数

MySQL提供了一系列的JSON函数，用于对JSON数据进行操作。以下是一些常用的JSON查询函数：

JSON_EXTRACT(json_doc, path)：从JSON文档中提取指定路径的值。
JSON_SET(json_doc, path, val[, path, val]...)：设置JSON文档中指定路径的值。
JSON_INSERT(json_doc, path, val[, path, val]...)：在JSON文档中插入指定路径的值。
JSON_REPLACE(json_doc, path, val[, path, val]...)：替换JSON文档中指定路径的值。
JSON_REMOVE(json_doc, path[, path]...)：从JSON文档中删除指定路径的值。
JSON_CONTAINS(json_doc, val[, path])：检查JSON文档是否包含指定的值。

过滤JSON数据

条件过滤

与传统的SQL查询类似，我们也可以在查询JSON数据时进行条件过滤。例如，我们想要查询年龄大于等于35岁的员工，可以执行如下SQL语句：

SELECT *
FROM employees
WHERE JSON_EXTRACT(data, '$.age') >= 35;

执行以上SQL语句，将会得到结果：

+----+--------------------------------------------------------+
| id | data                                                   |
+----+--------------------------------------------------------+
|  2 | {"name": "Bob", "age": 35, "department": "Sales"}      |
|  3 | {"name": "Charlie", "age": 40, "department": "Marketing"}|
+----+--------------------------------------------------------+

JSON字段过滤

如果需要根据JSON字段的值进行过滤，可以直接在JSON_EXTRACT函数中进行条件判断。例如，我们想要查询部门为”Sales”的员工，可以执行如下SQL语句：

SELECT *
FROM employees
WHERE JSON_EXTRACT(data, '$.department') = 'Sales';

执行以上SQL语句，将会得到结果：

+----+--------------------------------------------------------+
| id | data                                                   |
+----+--------------------------------------------------------+
|  2 | {"name": "Bob", "age": 35, "department": "Sales"}      |
+----+--------------------------------------------------------+

排序JSON数据

可以使用JSON_EXTRACT函数作为排序条件对JSON数据进行排序。例如，我们想要按照年龄从大到小的顺序对员工进行排序，可以执行如下SQL语句：

SELECT *
FROM employees
ORDER BY JSON_EXTRACT(data, '$.age') DESC;

执行以上SQL语句，将会得到结果：

+----+--------------------------------------------------------+
| id | data                                                   |
+----+--------------------------------------------------------+
|  3 | {"name": "Charlie", "age": 40, "department": "Marketing"}|
|  2 | {"name": "Bob", "age": 35, "department": "Sales"}      |
|  1 | {"name": "Alice", "age": 30, "department": "Engineering"}|
+----+--------------------------------------------------------+

连接JSON数据

可以使用JSON_EXTRACT函数将多个JSON字段的值连接成一个字段。例如，我们想要查询员工的姓名和部门，并将二者合并成一个字段，可以执行如下SQL语句：

SELECT 
    CONCAT(
        JSON_EXTRACT(data, ' $.name'), ' - ', JSON_EXTRACT(data, '$ .department')
    ) AS employee_info
FROM employees;

执行以上SQL语句，将会得到结果：

+----------------------------+
| employee_info              |
+----------------------------+
| Alice - Engineering        |
| Bob - Sales                |
| Charlie - Marketing        |
+----------------------------+