使用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';

语法解释

json_extract(detail, '$.techparams.ownership')

提取 detail 中 techparams 对象内的 ownership 值。

json_unquote(...)

去掉 json 提取结果的引号，使其变为普通字符串。

where ... = 'top'

筛选出 ownership 值等于 top 的记录。

3. 示例数据和运行结果

假设 site_device 表中的数据如下：

运行查询后，结果为：

4. 注意事项

json 路径表达式 $

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

性能优化

如果数据量较大，可以通过为 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);

数据规范化

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

5. 总结

mysql 提供了强大的 json 查询功能，使得我们可以方便地处理结构化的 json 数据。在本文中，我们通过 json_extract 和 json_unquote 函数，成功筛选出了目标字段值为特定值的记录。同时，结合性能优化建议，可以让你的 json 查询更高效。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。