我要评论在日常开发中，随着项目需求的不断复杂化，许多表字段可能会存储 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 表中的数据如下：
| id | detail |
|---|---|
| 1 | {"devicetype": "ammeter", "techparams": {"ownership": "top", "datareport": "1"}, "devicebrand": "huawei"} |
| 2 | {"devicetype": "ammeter", "techparams": {"ownership": "bottom", "datareport": "1"}, "devicebrand": "huawei"} |
| 3 | {"devicetype": "ammeter", "techparams": {"ownership": "top", "datareport": "1"}, "devicebrand": "huawei"} |
运行查询后，结果为：
| id | detail |
|---|---|
| 1 | {"devicetype": "ammeter", "techparams": {"ownership": "top", "datareport": "1"}, "devicebrand": "huawei"} |
| 3 | {"devicetype": "ammeter", "techparams": {"ownership": "top", "datareport": "1"}, "devicebrand": "huawei"} |
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 查询更高效。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论