mysql json多层查询

### 如何在 MySQL 中执行多层 JSON 查询

#### 创建带有 JSON 列的数据表
为了展示如何进行多层 JSON 查询，先创建一个包含 JSON 类型列 `data` 的表格 `json_data`[^1]。

CREATE TABLE json_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON NOT NULL
);

#### 插入多层结构的 JSON 数据
向上述创建好的 `json_data` 表格中插入一些具有嵌套层次结构的 JSON 文档作为测试数据：

INSERT INTO json_data (data) VALUES ('{
  "employee": {
    "name": "张三",
    "department": "研发部",
    "address": {
      "city": "北京",
      "street": "中关村大街"
    },
    "projects": [
      {"project_name": "项目A", "start_date": "2023-01-01"},
      {"project_name": "项目B", "start_date": "2023-06-01"}
    ]
  }
}');

#### 执行单层路径查询
对于简单的键值对查询，可以直接利用箭头操作符 (`->`) 来访问指定路径下的值。例如要获取员工的名字可以这样做:

SELECT data -> '$.employee.name' AS employee_name FROM json_data;

这将会返回如下结果：
| employee_name |
|---------------|
| "张三" |

注意这里的字符串会被加上双引号表示这是一个 JSON 字符串类型的结果；如果想要去掉这些额外的引号，则可使用 `->>` 运算符代替 `->`.

#### 多层路径查询
当涉及到更深层次的对象属性时，只需继续扩展路径即可。比如要获得城市名称：

SELECT data ->> '$.employee.address.city' as city FROM json_data;

此命令将输出：
| city |
|----------|
| 北京 |

#### 数组中的对象查询
假设现在希望找到参与特定项目的员工姓名，即遍历数组并匹配某个条件。此时需要用到函数 `JSON_CONTAINS_PATH()` 和/或其他辅助函数如 `JSON_UNQUOTE()`, `JSON_EXTRACT()` 等来处理复杂情况。下面是一个例子，它会找出所有参加了“项目A”的员工名字：

SELECT JSON_UNQUOTE(data->>'$.employee.name') AS name 
FROM json_data 
WHERE JSON_CONTAINS(JSON_EXTRACT(data,'$.employee.projects[*].project_name'), '"项目A"', '');

这段SQL语句首先提取了所有的子项列表，接着判断是否存在目标字符串，并最终取出了符合条件记录里的员工姓名。