MariaDB 資料庫 – JSON_KEYS , JSON_QUERY , JSON_VALUE 以及 JSON_EXTRACT 函數

測試環境為 CentOS 8 x86_64

使用關聯式資料庫（ RDBMS : Relational Database Management System ）的 MariaDB 也可以儲存與查詢 NoSQL ( Not Only SQL : 使用非關聯式資料庫的資料庫,資料儲存不需要固定的欄位 ) 的 JSON ( JavaScript Object Notation ）資料格式.

下面使用 JSON_QUERY , JSON_VALUE 以及 JSON_EXTRACT 函數,來讀取 JSON 欄位的資料,回傳值會略微為不同.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

JSON 資料類型：

1. 物件 (Object) : 使用 { } , 無序成對 (pair) 的 {鍵 Key name :值 value} 方式來儲存,鍵值之間使用逗號來分隔.
2. 陣列 (Array) : 使用 [ ] , 資料彼此間使用逗號來分割,如：[value, value] .
3. 數字 (Number) : 直接使用整數或是浮點數.
4. 字串 (String) : 使用 “” (括號)來表示字串.
5. 布林函數 (Boolean) : 使用 TRUE 或 FALSE.
6. NULL 類型.

設定 JSON 字串變數 @json , a 為字串 (String), b 為陣列 (Array), c 為物件 (Object).

MariaDB [(none)]> SET @json =
'{
     "a": "[1,2]",
     "b": [1, 2],
     "c": {"c1":[3,4] ,"c2":"[3,4]"}
}';
Query OK, 0 rows affected (0.000 sec)

• JSON_KEYS – JSON_KEYS(json_doc[, path])
  查詢 JSON 資料的鍵 Key .

  MariaDB [(none)]> SELECT JSON_KEYS(@json);
  +------------------+
  | JSON_KEYS(@json) |
  +------------------+
  | ["a", "b", "c"]  |
  +------------------+
  1 row in set (0.001 sec)
  

• JSON_QUERY – JSON_QUERY(json_doc, path)
  回傳 JSON 字串指定路徑下的物件 (Object) 或陣列 (Array).

  因為 JSON_QUERY 函數只回傳物件 (Object) 或陣列 (Array) , a 為字串所以回傳為 NULL .

  MariaDB [(none)]> SELECT JSON_QUERY(@json,'$.a'),JSON_QUERY(@json,'$.b'),JSON_QUERY(@json,'$.c') ;
  +-------------------------+-------------------------+----------------------------+
  | JSON_QUERY(@json,'$.a') | JSON_QUERY(@json,'$.b') | JSON_QUERY(@json,'$.c')    |
  +-------------------------+-------------------------+----------------------------+
  | NULL                    | [1, 2]                  | {"c1":[3,4] ,"c2":"[3,4]"} |
  +-------------------------+-------------------------+----------------------------+
  1 row in set (0.000 sec)
  

• JSON_VALUE – JSON_VALUE(json_doc, path)
  回傳 JSON 字串指定路徑下的純量值 (scalar).

  因為 JSON_VALUE 函數只回傳純量值 (scalar) , b 為陣列 (Array) 與 c 為物件 (Object) 所以回傳為 NULL .

  MariaDB [(none)]> SELECT JSON_VALUE(@json,'$.a'),JSON_VALUE(@json,'$.b'),JSON_VALUE(@json,'$.c') ;
  +-------------------------+-------------------------+-------------------------+
  | JSON_VALUE(@json,'$.a') | JSON_VALUE(@json,'$.b') | JSON_VALUE(@json,'$.c') |
  +-------------------------+-------------------------+-------------------------+
  | [1,2]                   | NULL                    | NULL                    |
  +-------------------------+-------------------------+-------------------------+
  1 row in set (0.001 sec)
  

• JSON_EXTRACT – JSON_EXTRACT(json_doc, path[, path] …)
  回傳指定路徑(可以指定多個)下的的值 (values),當回傳多個值時會自動包裝成為陣列 (Array).

  因為 JSON_EXTRACT 函數回傳值 (Value),要注意會連 “” 都會回傳,並自動包裝成為陣列 (Array).

  MariaDB [(none)]> SELECT JSON_EXTRACT(@json,'$.a','$.b','$.c') ;
  +--------------------------------------------------+
  | JSON_EXTRACT(@json,'$.a','$.b','$.c')            |
  +--------------------------------------------------+
  | ["[1,2]", [1, 2], {"c1": [3, 4], "c2": "[3,4]"}] |
  +--------------------------------------------------+
  1 row in set (0.001 sec)
  

更多關於 Json 函數,請參考 – https://mariadb.com/kb/en/json-functions/