測試環境為 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 函數請參考 – https://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 格式(物件)的範例.
[php]
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)
MariaDB [(none)]> SELECT JSON_EXTRACT(@JARRAY,'$[0]');
+-----------------------------------------------------------------------------------+
| JSON_EXTRACT(@JARRAY,'$[0]') |
+-----------------------------------------------------------------------------------+
| {"TimeStamp": "01/07/2021 07:56:35 PM", "Status": "ok", "Temp1": 30, "Temp2": 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)
沒有解決問題,試試搜尋本站其他內容