使用MySQL JSON查询筛选嵌套字段的值方式
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1. 理解 JSON 数据的层级结构</a></li><li><a href="#_label1">2. 使用 MySQL JSON 查询函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">语法解释</a></li></ul><li><a href="#_label2">3. 示例数据和运行结果</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">4. 注意事项</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">5. 总结</a></li><ul class="second_class_ul"></ul></ul></div><p>在日常开发中，随着项目需求的不断复杂化，许多表字段可能会存储 JSON 格式的数据。</p><p>例如，我们有一张site_device表，其中有一个名为detail的字段，保存了设备的详细信息。这些信息存储为 JSON 数据，如下所示：</p>
<div class="jb51code"><pre class="brush:json;">{
"deviceType": "ammeter",
"techParams": {
"name": "202501241556",
"deviceNo": "202501241556",
"gatewayNo": "1829047495952388098",
"ownership": "top",
"dataReport": "1"
},
"deviceBrand": "HUAWEI",
"deviceModel": "test",
"modelConfigId": "1871021778273325058"
}</pre></div>
<p>我们想要查询出 <code>ownership</code> 为 <code>top</code> 的设备。<code>ownership</code> 字段嵌套在 <code>techParams</code> 中，因此我们需要使用 MySQL 提供的 JSON 函数来实现查询。</p>
<p class="maodian"><a name="_label0"></a></p><h2>1. 理解 JSON 数据的层级结构</h2>
<p>在这个例子中，JSON 的结构可以分解为：</p>
<ul><li><code>deviceType</code>：在 JSON 顶层。</li><li><code>techParams</code>：是一个嵌套对象，里面包含了 <code>ownership</code> 等字段。</li><li><code>ownership</code>：目标字段，位于 <code>techParams</code> 内。</li></ul>
<p>我们需要从 <code>detail</code> 中提取出 <code>techParams.ownership</code> 的值。</p>
<p class="maodian"><a name="_label1"></a></p><h2>2. 使用 MySQL JSON 查询函数</h2>
<p>MySQL 提供了一系列函数用于处理 JSON 数据：</p>
<ul><li><code>JSON_EXTRACT(json_doc, path)</code>：从 JSON 中提取值。</li><li><code>JSON_UNQUOTE(json_val)</code>：去掉 JSON 提取值的引号，返回纯文本。</li></ul>
<p>对于本例来说，我们可以用以下语句来筛选出 <code>ownership</code> 为 <code>top</code> 的记录：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT *
FROM site_device
WHERE JSON_UNQUOTE(JSON_EXTRACT(detail, '$.techParams.ownership')) = 'top';</pre></div>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>语法解释</h3>
<div class="jb51code"><pre class="brush:sql;">JSON_EXTRACT(detail, '$.techParams.ownership')</pre></div>
<p>提取 <code>detail</code> 中 <code>techParams</code> 对象内的 <code>ownership</code> 值。</p>
<div class="jb51code"><pre class="brush:sql;">JSON_UNQUOTE(...)</pre></div>
<p>去掉 JSON 提取结果的引号，使其变为普通字符串。</p>
<div class="jb51code"><pre class="brush:sql;">WHERE ... = 'top'</pre></div>
<p>筛选出 <code>ownership</code> 值等于 <code>top</code> 的记录。</p>
<p class="maodian"><a name="_label2"></a></p><h2>3. 示例数据和运行结果</h2>
<p>假设 <code>site_device</code> 表中的数据如下：</p>
<table><thead><tr><th>id</th><th>detail</th></tr></thead><tbody><tr><td>1</td><td>{"deviceType": "ammeter", "techParams": {"ownership": "top", "dataReport": "1"}, "deviceBrand": "HUAWEI"}</td></tr><tr><td>2</td><td>{"deviceType": "ammeter", "techParams": {"ownership": "bottom", "dataReport": "1"}, "deviceBrand": "HUAWEI"}</td></tr><tr><td>3</td><td>{"deviceType": "ammeter", "techParams": {"ownership": "top", "dataReport": "1"}, "deviceBrand": "HUAWEI"}</td></tr></tbody></table>
<p>运行查询后，结果为：</p>
<table><thead><tr><th>id</th><th>detail</th></tr></thead><tbody><tr><td>1</td><td>{"deviceType": "ammeter", "techParams": {"ownership": "top", "dataReport": "1"}, "deviceBrand": "HUAWEI"}</td></tr><tr><td>3</td><td>{"deviceType": "ammeter", "techParams": {"ownership": "top", "dataReport": "1"}, "deviceBrand": "HUAWEI"}</td></tr></tbody></table>
<p class="maodian"><a name="_label3"></a></p><h2>4. 注意事项</h2>
<p><strong>JSON 路径表达式 </strong><code>$</code></p>
<p>JSON 路径表达式 <code>$</code> 表示 JSON 的根，嵌套字段用 <code>.</code> 分隔。例如：<code>$.techParams.ownership</code>。</p>
<p><strong>性能优化</strong></p>
<p>如果数据量较大，可以通过为 JSON 字段创建虚拟列（<code>Generated Column</code>）并加索引来提升查询性能。</p>
<div class="jb51code"><pre class="brush:sql;">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);</pre></div>
<p><strong>数据规范化</strong></p>
<p>如果 JSON 数据中的字段经常被查询，考虑将这些字段拆分到独立的数据库列中，以提高查询效率。</p>
<p class="maodian"><a name="_label4"></a></p><h2>5. 总结</h2>
<p>MySQL 提供了强大的 JSON 查询功能，使得我们可以方便地处理结构化的 JSON 数据。在本文中，我们通过 <code>JSON_EXTRACT</code> 和 <code>JSON_UNQUOTE</code> 函数，成功筛选出了目标字段值为特定值的记录。同时，结合性能优化建议，可以让你的 JSON 查询更高效。</p>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]