首頁 > 軟體

MySQL中的JSON欄位List成員檢查

2022-08-25 14:00:50

JSON欄位List成員檢查

檔案

方法

JSON_CONTAINS(target, candidate[, path])

value MEMBER OF(json_array)

查詢範例

mysql> set @list = JSON_ARRAY(1, 2);
Query OK, 0 rows affected (0.01 sec)

mysql> select 1 MEMBER OF(@list);
+--------------------+
| 1 MEMBER OF(@list) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)


mysql> SELECT JSON_CONTAINS(@list, '1');
+---------------------------+
| JSON_CONTAINS(@list, '1') |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.01 sec)

MySQL中JSON欄位操作

Mysql 自5.7之後增加了很多函數用於處理Json字串。方便實用。

基本改變

Json陣列:

["abc", 10, null, true, false]

Json物件:

{"k1": "value", "k2": 10}

JSON陣列元素和JSON物件鍵值內允許巢狀

Json函數

  • JSON_TYPE():返回json字串型別
mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+
 
mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+
 
mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

 MySQL使用utf8mb4字元集和utf8mb4_bin排序規則處理JSON上下文中使用的字串 。其他字元集中的字串將utf8mb4根據需要轉換。(對於ascii或 utf8字元集中的字串,無需進行轉換,因為ascii和utf8是的子集utf8mb4。一般將資料庫字元集設定為utf8mb4)

  • JSON_ARRAY():將數值包裝為json陣列
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
  • JSON_OBJECT():將數值包裝為json物件
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+
  • JSON_MERGE():將多個json合併為一個
mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

Json字串中的key-value存取

使用column-path運運算元 ->得到欄位中json某個key的值,但是值中的"""和""都會顯示,如果不想顯示,使用->>。

#建立表
CREATE TABLE facts (ids JSON);
 
#插入資料  這裡要注意是否啟用了伺服器SQL模式,插入資料時要注意特殊符號
INSERT INTO facts VALUES (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
 
#檢視資料
mysql> select ids from facts;
+---------------------------------------------------------+
| ids                                                     |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named "Sakila"."} |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ids->'$.mascot' FROM facts;
+---------------------------------------------+
| ids->'$.mascot'                             |
+---------------------------------------------+
| "Our mascot is a dolphin named "Sakila"." |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT ids->>'$.mascot' FROM facts;
+-----------------------------------------+
| ids->>'$.mascot'                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+
 
#注意,這裡存取json欄位key使用單引號還是雙引號取決於sql模式

讀取json欄位中某個key的值

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+
 
#去除值雙引號
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'));
+-----------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name')) |
+-----------------------------------------------------------------------+
| Aztalan                                                               |
+-----------------------------------------------------------------------+

小結:

‘$.*’返回全部json
‘$.title’返回key=”title”的資料
‘$**.text’返回所有最底層key=”text”的資料
‘$.content[*].item1[*]’返回key=content的list的key=item1的list的所有內容

官網參考地址

以上為個人經驗,希望能給大家一個參考,也希望大家多多支援it145.com。 


IT145.com E-mail:sddin#qq.com