使用 MySQL JSON 查询筛选嵌套字段的值

在日常开发中，随着项目需求的不断复杂化，许多表字段可能会存储 JSON 格式的数据。例如，我们有一张 site_device 表，其中有一个名为 detail 的字段，保存了设备的详细信息。这些信息存储为 JSON 数据，如下所示：

{
  "deviceType": "ammeter",
  "techParams": {
    "name": "202501241556",
    "deviceNo": "202501241556",
    "gatewayNo": "1829047495952388098",
    "ownership": "top",
    "dataReport": "1"
  },
  "deviceBrand": "HUAWEI",
  "deviceModel": "test",
  "modelConfigId": "1871021778273325058"
}

我们想要查询出 ownership 为 top 的设备。ownership 字段嵌套在 techParams 中，因此我们需要使用 MySQL 提供的 JSON 函数来实现查询。

1. 理解 JSON 数据的层级结构

在这个例子中，JSON 的结构可以分解为：

• deviceType：在 JSON 顶层。
• techParams：是一个嵌套对象，里面包含了 ownership 等字段。
• ownership：目标字段，位于 techParams 内。

我们需要从 detail 中提取出 techParams.ownership 的值。

2. 使用 MySQL JSON 查询函数

MySQL 提供了一系列函数用于处理 JSON 数据：

• JSON_EXTRACT(json_doc, path)：从 JSON 中提取值。
• JSON_UNQUOTE(json_val)：去掉 JSON 提取值的引号，返回纯文本。

对于本例来说，我们可以用以下语句来筛选出 ownership 为 top 的记录：

SELECT *
FROM site_device
WHERE JSON_UNQUOTE(JSON_EXTRACT(detail, '$.techParams.ownership')) = 'top';

语法解释

1. JSON_EXTRACT(detail, '$.techParams.ownership')
   提取 detail 中 techParams 对象内的 ownership 值。

2. JSON_UNQUOTE(...)
   去掉 JSON 提取结果的引号，使其变为普通字符串。

3. WHERE ... = 'top'
   筛选出 ownership 值等于 top 的记录。

3. 示例数据和运行结果

假设 site_device 表中的数据如下：

运行查询后，结果为：

4. 注意事项

1. JSON 路径表达式 $
   JSON 路径表达式 $ 表示 JSON 的根，嵌套字段用 . 分隔。例如：$.techParams.ownership。

2. 性能优化
   如果数据量较大，可以通过为 JSON 字段创建虚拟列（Generated Column）并加索引来提升查询性能。

   ALTER TABLE site_device
   ADD COLUMN ownership VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(detail, '$.techParams.ownership'))) STORED,
   ADD INDEX idx_ownership (ownership);
   

3. 数据规范化
   如果 JSON 数据中的字段经常被查询，考虑将这些字段拆分到独立的数据库列中，以提高查询效率。

5. 总结

MySQL 提供了强大的 JSON 查询功能，使得我们可以方便地处理结构化的 JSON 数据。在本文中，我们通过 JSON_EXTRACT 和 JSON_UNQUOTE 函数，成功筛选出了目标字段值为特定值的记录。同时，结合性能优化建议，可以让你的 JSON 查询更高效。希望本文对你有所帮助！