MariaDB – JSON Path Syntax

測試環境為 CentOS 8 x86_64

path : ['lax'] '$' [step]*

lax : MariaDB 只支援這個模式,如果路徑運算式找不到,則傳回 NULL,下面我們來看一下後面 ‘$’ [step]* 使用方式.

讀取 JSON 資料主要可以區分為 值 (Value) , 物件 (Object) , 陣列 (Array),可以透過以下三個函數.

  • 如果回傳值為 值 (Value) 時使用 JSON_VALUE.
  • 但如是 物件 (Object) ,或是 陣列 (Array) 需使用 JSON_QUERY.
  • 或是使用 JSON_EXTRACT.

更多關於 JSON_QUERY , JSON_VALUE 以及 JSON_EXTRACT 函數請參考 – http://benjr.tw/103237

關於 JSONPath Expressions 說明 – https://mariadb.com/kb/en/jsonpath-expressions/

Object Member Selector

To select member(s) in a JSON object, one can use one of the following:

  • .memberName – 名稱中沒有 有效標識符 (valid identifier) 直接指定名稱即可.
  • .”memberName” – 名稱包含有效標識符 (valid identifier – space, dot, and/or other characters) 時需使用雙引號 ” (Double Quotes) 來指定.
  • .* – selects the values of all members of the object.
  • If the current item is an array (instead of an object), nothing will be selected.

下面是一個 JSON 格式(物件)的範例.


MariaDB [(none)]> SET @JOBJECT='{
    "Main Board": "ASUS",
    "CPU":
    {
    "CPU 1": "Intel",
    "CPU 2": "Intel"
    },
    "MEM":
    {
    "MEM 1": "Hynix",
    "MEM 2": "Hynix",
    "MEM 3": "Samsung",
    "MEM 4": "Samsung"
    }
}';
Query OK, 0 rows affected (0.000 sec)

去讀取 Main Board 的值 (讀值時使用 JSON_VALUE 函數).

MariaDB [(none)]> SELECT JSON_VALUE(@JOBJECT,'$.Main Board');
+------------------------------------+
| JSON_VALUE(@JVALUE,'$.Main Board') |
+------------------------------------+
| ASUS                               |
+------------------------------------+
1 row in set (0.001 sec)

去讀取 CPU 這個物件 (讀物件時使用 JSON_QUERY 函數).

MariaDB [(none)]> SELECT JSON_QUERY(@JOBJECT,'$.CPU');
+----------------------------------------------------+
| JSON_QUERY(@JVALUE,'$.CPU')                        |
+----------------------------------------------------+
| {
    "CPU 1": "Intel",
    "CPU 2": "Intel"
    } |
+----------------------------------------------------+
1 row in set (0.000 sec)

去讀取 CPU 物件裡的 CPU 1 的值 (讀值時使用 JSON_VALUE 函數).

MariaDB [(none)]> SELECT JSON_VALUE(@JOBJECT,'$.CPU.CPU 1');
+-----------------------------------+
| JSON_VALUE(@JVALUE,'$.CPU.CPU 1') |
+-----------------------------------+
| Intel                             |
+-----------------------------------+
1 row in set (0.000 sec)

當我們使用 * 號讀取多個值的時候用 JSON_EXTRACT 它可以回傳指定路徑(可以指定多個)下的的值 (values),當回傳多個值時會自動包裝成為陣列 (Array).

MariaDB [(none)]> SELECT JSON_EXTRACT(@JOBJECT,'$.*'); 
+------------------------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT(@JOBJECT,'$.*')                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------+
| ["ASUS", {"CPU 1": "Intel", "CPU 2": "Intel"}, {"MEM 1": "Hynix", "MEM 2": "Hynix", "MEM 3": "Samsung", "MEM 4": "Samsung"}] |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_EXTRACT(@JOBJECT,'$.CPU.*'); 
+----------------------------------+
| JSON_EXTRACT(@JOBJECT,'$.CPU.*') |
+----------------------------------+
| ["Intel", "Intel"]               |
+----------------------------------+
1 row in set (0.000 sec)

Array Element Selector

To select elements of an array, one can use one of the following:

  • [N] selects element number N in the array. The elements are counted from zero.
  • [*] selects all elements in the array.
  • If the current item is an object (instead of an array), nothing will be selected.

下面是一個 JSON 格式(陣列)的範例.

MariaDB [(none)]> SET @JARRAY='[
        {
            "TimeStamp": "01/07/2021 07:56:35 PM",
            "Status": "ok",
            "Temp1": 30,
            "Temp2": 30
        },
        {
            "TimeStamp": "01/07/2021 07:56:48 PM",
            "Status": "ok",
            "Temp1": 31,
            "Temp2": 31
        },
        {
            "TimeStamp": "01/07/2021 07:57:00 PM",
            "Status": "ok",
            "Temp1": 32,
            "Temp2": 32
        }
    ]';

指定讀取陣列的第一筆資料 (陣列從 0 開始).

MariaDB [(none)]> SELECT JSON_EXTRACT(@JARRAY,'$[0].*'); 
+------------------------------------------+
| JSON_EXTRACT(@JARRAY,'$[0].*')           |
+------------------------------------------+
| ["01/07/2021 07:56:35 PM", "ok", 30, 30] |
+------------------------------------------+
1 row in set (0.000 sec)

指定讀取陣列的第一筆資料 (陣列從 0 開始)的 Timestamp 值,可以使用 JSON_EXTRACT 或是 JSON_VALUE 函數.

MariaDB [(none)]> SELECT JSON_EXTRACT(@JARRAY,'$[0].TimeStamp'); 
+----------------------------------------+
| JSON_EXTRACT(@JARRAY,'$[0].TimeStamp') |
+----------------------------------------+
| "01/07/2021 07:56:35 PM"               |
+----------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_VALUE(@JARRAY,'$[0].TimeStamp'); 
+--------------------------------------+
| JSON_VALUE(@JARRAY,'$[0].TimeStamp') |
+--------------------------------------+
| 01/07/2021 07:56:35 PM               |
+--------------------------------------+
1 row in set (0.000 sec)

讀取陣列中所有 TimeStamp 的值,需使用 JSON_EXTRACT 函數.

MariaDB [(none)]> SELECT JSON_EXTRACT(@JARRAY,'$[*].TimeStamp'); 
+--------------------------------------------------------------------------------+
| JSON_EXTRACT(@JARRAY,'$[*].TimeStamp')                                         |
+--------------------------------------------------------------------------------+
| ["01/07/2021 07:56:35 PM", "01/07/2021 07:56:48 PM", "01/07/2021 07:57:00 PM"] |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)

另外一個 JSON 格式(物件+陣列)的範例.

MariaDB [(none)]> SET @JARRAY='{"Sensor": [
        {
            "TimeStamp": "01/07/2021 07:56:35 PM",
            "Status": "ok",
            "Temp1": 30,
            "Temp2": 30
        },
        {
            "TimeStamp": "01/07/2021 07:56:48 PM",
            "Status": "ok",
            "Temp1": 31,
            "Temp2": 31
        },
        {
            "TimeStamp": "01/07/2021 07:57:00 PM",
            "Status": "ok",
            "Temp1": 32,
            "Temp2": 32
        }
    ]}';

指定讀取物件內陣列第一筆資料 (陣列從 0 開始),需使用 JSON_QUERY 函數.

MariaDB [(none)]> SELECT JSON_QUERY(@JARRAY,'$.Sensor[0]'); 
+---------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_QUERY(@JARRAY,'$.Sensor[0]')                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| {
            "TimeStamp": "01/07/2021 07:56:35 PM",
            "Status": "ok",
            "Temp1": 30,
            "Temp2": 30
        } |
+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

指定讀取物件內陣列第一筆資料 (陣列從 0 開始)的 Temp1 值,,可以使用 JSON_EXTRACT 或是 JSON_VALUE 函數.

MariaDB [(none)]> SELECT JSON_VALUE(@JARRAY,'$.Sensor[0].Temp1'); 
+-----------------------------------------+
| JSON_VALUE(@JARRAY,'$.Sensor[0].Temp1') |
+-----------------------------------------+
| 30                                      |
+-----------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT JSON_EXTRACT(@JARRAY,'$.Sensor[0].Temp1'); 
+-------------------------------------------+
| JSON_EXTRACT(@JARRAY,'$.Sensor[0].Temp1') |
+-------------------------------------------+
| 30                                        |
+-------------------------------------------+
1 row in set (0.000 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料