在日常開發(fā)中，隨著項目需求的不斷復雜化，許多表字段可能會存儲 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 函數來實現(xiàn)查詢。

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 提取結果的引號，使其變?yōu)槠胀ㄗ址?/p>

WHERE ... = 'top'

篩選出 ownership 值等于 top 的記錄。

3. 示例數據和運行結果

假設 site_device 表中的數據如下：

運行查詢后，結果為：

4. 注意事項

JSON 路徑表達式 $

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

性能優(yōu)化

如果數據量較大，可以通過為 JSON 字段創(chuàng)建虛擬列（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);

數據規(guī)范化

如果 JSON 數據中的字段經常被查詢，考慮將這些字段拆分到獨立的數據庫列中，以提高查詢效率。

5. 總結

MySQL 提供了強大的 JSON 查詢功能，使得我們可以方便地處理結構化的 JSON 數據。在本文中，我們通過 JSON_EXTRACT 和 JSON_UNQUOTE 函數，成功篩選出了目標字段值為特定值的記錄。同時，結合性能優(yōu)化建議，可以讓你的 JSON 查詢更高效。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論