MySQL查詢JSON的詳解

一、MySQL查詢JSON內字段

JSON（JavaScript Object Notation）是一種輕量級的數據交換格式，它廣泛用於前後端數據交互。MySQL從5.7.8版本開始支持JSON數據類型，使得我們可以將JSON數據存儲在數據庫中，並且使用SQL查詢語句對其進行操作。查詢JSON內的字段非常簡單，只需使用->或者->>操作符即可。

SELECT json_column->'$.field_name' FROM table_name;
SELECT json_column->>'$.field_name' FROM table_name;

其中，->操作符返回的是JSON對象或者數組的某個字段的JSON值，而->>操作符返回的是該字段的文本值。

例如，我們有以下的JSON數據：

{
   "id": 1,
   "name": "John",
   "age": 25,
   "address": {
      "city": "New York",
      "state": "NY",
      "zip": "10001"
   }
}

如果我們想查詢該JSON數據中的name字段，那麼可以使用以下查詢語句：

SELECT json_data->'$.name' FROM my_table;

這樣就可以得到查詢結果為John。

二、MySQL查詢JSON數組的值

查詢JSON數組的值需要使用JSON_EXTRACT函數，該函數取JSON文本和一個JSON路徑表達式作為參數，並返回匹配該路徑表達式的JSON值。

SELECT JSON_EXTRACT(json_column, '$[index]') FROM table_name;

其中，index表示JSON數組中所要查詢的值的索引。

例如，我們有以下的JSON數組：

[
   {
      "name": "John",
      "age": 25
   },
   {
      "name": "David",
      "age": 30
   },
   {
      "name": "Lucy",
      "age": 28
   }
]

我們想查詢該數組中第二個元素的姓名，那麼可以使用以下查詢語句：

SELECT JSON_EXTRACT(json_data, '$[1].name') FROM my_table;

這樣就可以得到查詢結果為David。

三、MySQL查詢JSON中的數據

查詢JSON中的數據需要使用JSON_CONTAINS函數或者JSON_SEARCH函數。

JSON_CONTAINS函數可以判斷一個JSON文本或者JSON對象是否包含一個指定的值，返回bool類型的結果。

JSON_CONTAINS(json_column, '["value"]')

JSON_SEARCH函數在JSON文本中查找包含給定字符串的JSON路徑，並返回該路徑。

JSON_SEARCH(json_column, 'one', 'value')

其中，第一個參數是要查詢的JSON文本，第二個參數是搜索類型，可以是’one’, ‘all’, ‘any’，第三個參數是搜索的字符串。

例如，我們有以下的JSON數據：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      "reading",
      "swimming",
      "traveling"
   ]
}

如果我們想查詢該JSON數據中是否包含”swimming”這個值，可以使用以下查詢語句：

SELECT JSON_CONTAINS(json_data->'$.hobbies', '["swimming"]') FROM my_table;
或者
SELECT JSON_SEARCH(json_data->'$.hobbies', 'one', 'swimming') FROM my_table;

如果查詢結果為1，說明該JSON數據中包含”swimming”這個值。

四、MySQL查詢JSON的英文字段

查詢JSON的英文字段也可以使用->或者->>操作符來實現。例如，我們有以下的JSON數據：

{
   "姓名": "張三",
   "年齡": 25,
   "性別": "男"
}

如果我們想查詢該JSON數據中的”年齡”字段，可以使用以下查詢語句：

SELECT json_data->'$.年齡' FROM my_table;
或者
SELECT json_data->>'$.年齡' FROM my_table;

這樣就可以得到查詢結果為25。

五、MySQL查詢JSON數組內字段模糊

查詢JSON數組內字段模糊需要使用JSON_EXTRACT函數結合LIKE運算符實現。

SELECT * FROM table_name WHERE JSON_EXTRACT(json_column, '$[*].field_name') LIKE '%value%';

其中，$[*].field_name表示JSON數組內的所有field_name字段。

例如，我們有以下的JSON數組：

[
   {
      "name": "John",
      "age": 25
   },
   {
      "name": "David",
      "age": 30
   },
   {
      "name": "Lucy",
      "age": 28
   }
]

如果我們想查詢該數組中姓名包含”oh”的元素，可以使用以下查詢語句：

SELECT * FROM my_table WHERE JSON_EXTRACT(json_data, '$[*].name') LIKE '%oh%';

這樣就可以得到查詢結果為John和David。

六、MySQL查詢JSON中數組的一個值

查詢JSON中數組的一個值需要使用JSON_TABLE函數。

SELECT * FROM JSON_TABLE(json_column, '$.field[*]' COLUMNS(field_name field_path VARCHAR(100) PATH '$', field_value VARCHAR(100) PATH '$')) AS T WHERE T.field_name = 'value';

其中，$.field[*]表示JSON中的一個數組，field_name表示數組元素的鍵，field_value表示數組元素的值。

例如，我們有以下的JSON數據：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      "reading",
      "swimming",
      "traveling"
   ]
}

如果我們想查詢該JSON數據中的”swimming”這個值，可以使用以下查詢語句：

SELECT field_value FROM JSON_TABLE(json_data->'$.hobbies', '$[*]' COLUMNS (field_value VARCHAR(50) PATH '$')) AS T WHERE T.field_value = 'swimming';

這樣就可以得到查詢結果為”swimming”。

七、MySQL查詢JSON數據

查詢JSON數據可以直接使用SELECT語句，例如：

SELECT json_column FROM table_name;

八、MySQL查詢JSON字段數組

查詢JSON字段數組需要使用JSON_TABLE函數，例如：

SELECT * FROM table_name, JSON_TABLE(json_column, '$.field[*]' COLUMNS(field_name VARCHAR(100) PATH '$', field_value VARCHAR(100) PATH '$')) AS T WHERE T.field_name = 'value';

其中，$.field[*]表示JSON中的一個數組，field_name表示數組元素的鍵，field_value表示數組元素的值。

例如，我們有以下的JSON數據：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      {
         "name": "reading",
         "level": "high"
      },
      {
         "name": "swimming",
         "level": "medium"
      },
      {
         "name": "traveling",
         "level": "low"
      }
   ]
}

如果我們想查詢該JSON數據中hobbies數組中所有元素的”name”和”level”字段，可以使用以下查詢語句：

SELECT T.field_value FROM my_table, JSON_TABLE(json_data->'$.hobbies', '$[*]' COLUMNS (field_name VARCHAR(50) PATH '$.name', field_value VARCHAR(50) PATH '$.level')) AS T WHERE T.field_name = 'name' OR T.field_name = 'level';

這樣就可以得到查詢結果為：

+-----------+
| field_value |
+-----------+
| reading   |
| high      |
| swimming  |
| medium    |
| traveling |
| low       |
+-----------+

九、MySQL查詢JSON字段中的屬性

查詢JSON字段中的屬性需要使用->或者->>操作符來實現。例如，我們有以下的JSON數據：

{
   "id": 1,
   "name": "John",
   "hobbies": [
      {
         "name": "reading",
         "level": "high"
      },
      {
         "name": "swimming",
         "level": "medium"
      },
      {
         "name": "traveling",
         "level": "low"
      }
   ]
}

如果我們想查詢該JSON數據中hobbies數組中第一個元素的”name”字段，可以使用以下查詢語句：

SELECT json_data->'$.hobbies[0].name' FROM my_table;
或者
SELECT json_data->>'$.hobbies[0].name' FROM my_table;

這樣就可以得到查詢結果為reading。