MySQL :: MySQL 9.0 參考手冊 :: 14.17.6 JSON 表格函數

版本 9.0

8.4 目前版本
8.0

8.0 日文

MySQL 9.0 參考手冊 / ... / JSON 表格函數

14.17.6 JSON 表格函數

本節包含有關將 JSON 資料轉換為表格資料的 JSON 函數資訊。MySQL 9.0 支援一個此類函數，JSON_TABLE()。

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

從 JSON 文件中擷取資料，並將其以具有指定欄位的關係表格形式傳回。此處顯示此函數的完整語法

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

expr：這是傳回 JSON 資料的運算式。這可以是常數（'{"a":1}'），欄位（t1.json_data，假設表格 t1 在 FROM 子句中的 JSON_TABLE() 之前指定），或函數呼叫（JSON_EXTRACT(t1.json_data,'$.post.comments')）。

path：套用至資料來源的 JSON 路徑運算式。我們將符合路徑的 JSON 值稱為列來源；這用於產生關係資料的列。COLUMNS 子句評估列來源，在列來源中尋找特定的 JSON 值，並將這些 JSON 值以 SQL 值形式傳回，作為關係資料的個別欄位。

需要 alias。表格別名的慣用規則適用（請參閱第 11.2 節，「結構描述物件名稱」）。

此函數以不區分大小寫的方式比較欄位名稱。

JSON_TABLE() 支援四種類型的欄位，如下列清單所述

name FOR ORDINALITY：此類型會列舉 COLUMNS 子句中的列；名為 name 的欄位是一個計數器，其類型為 UNSIGNED INT，且初始值為 1。這相當於在 CREATE TABLE 陳述式中將欄位指定為 AUTO_INCREMENT，並且可用於區分具有相同值之父列，這些父列是透過 NESTED [PATH] 子句產生的多列。
name type PATH string_path [on_empty] [on_error]：此類型的欄位用於擷取 string_path 指定的值。type 是 MySQL 純量資料類型（也就是說，它不能是物件或陣列）。JSON_TABLE() 擷取 JSON 格式的資料，然後使用套用至 MySQL 中 JSON 資料的正常自動類型轉換，將其強制轉換為欄位類型。遺失的值會觸發 on_empty 子句。儲存物件或陣列會觸發選用的 on error 子句；當從 JSON 儲存的值強制轉換至表格欄位時發生錯誤時，也會發生這種情況，例如嘗試將字串 'asd' 儲存到整數欄位。
name type EXISTS PATH path：如果 path 指定的位置存在任何資料，則此欄位傳回 1，否則傳回 0。type 可以是任何有效的 MySQL 資料類型，但通常應指定為某些 INT 變體。
NESTED [PATH] path COLUMNS (column_list)：這會將 JSON 資料中的巢狀物件或陣列與父物件或陣列中的 JSON 值一起扁平化為單一列。使用多個 PATH 選項可將多個巢狀層級的 JSON 值投影到單一列中。
path 相對於 JSON_TABLE() 的父路徑列路徑，或是在巢狀路徑的情況下，相對於父 NESTED [PATH] 子句的路徑。

如果指定 on empty，則會判斷在資料遺失時 JSON_TABLE() 的處理方式（取決於類型）。當 NESTED PATH 子句沒有相符項目，且為其產生補值的 NULL 列時，也會在 NESTED PATH 子句中的欄位上觸發此子句。on empty 採用下列其中一個值

NULL ON EMPTY：欄位設定為 NULL；這是預設行為。
DEFAULT json_string ON EMPTY：只要提供的 json_string 有效，就會將其剖析為 JSON，並儲存以取代遺失的值。欄位類型規則也適用於預設值。
ERROR ON EMPTY：會擲回錯誤。

如果使用，on_error 會採用下列其中一個值，並產生如下所示的對應結果

NULL ON ERROR：欄位設定為 NULL；這是預設行為。
DEFAULT json string ON ERROR：json_string 會剖析為 JSON（前提是它有效），並儲存以取代物件或陣列。
ERROR ON ERROR：會擲回錯誤。

在 ON EMPTY 之前指定 ON ERROR 並非標準做法，且在 MySQL 中已棄用；嘗試這樣做會導致伺服器發出警告。預計在未來版本的 MySQL 中將會移除對非標準語法的支援。

當儲存到欄位的值被截斷時，例如將 3.14159 儲存到 DECIMAL(10,1) 欄位中，無論是否有使用 ON ERROR 選項，都會發出警告。當單一語句中有多個值被截斷時，警告只會發出一次。

當傳遞給此函數的表達式和路徑解析為 JSON null 時，根據 SQL 標準，JSON_TABLE() 會返回 SQL NULL，如下所示

mysql> SELECT *
    ->   FROM
    ->     JSON_TABLE(
    ->       '[ {"c1": null} ]',
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

以下查詢示範了 ON EMPTY 和 ON ERROR 的用法。路徑 "$.a" 對應於 {"b":1} 的列為空，並且嘗試將 [1,2] 儲存為純量會產生錯誤；這些列在顯示的輸出中會被強調。

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)

欄位名稱受限於管理表格欄位名稱的通常規則和限制。請參閱第 11.2 節「綱要物件名稱」。

所有 JSON 和 JSON 路徑表達式都會檢查有效性；任何一種無效的表達式都會導致錯誤。

在 COLUMNS 關鍵字之前的 path 的每個匹配項，都會對應到結果表中的單獨一行。例如，以下查詢會產生此處顯示的結果

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
+------+------+

表達式 "$[*]" 會匹配陣列的每個元素。您可以透過修改路徑來篩選結果中的列。例如，使用 "$[1]" 會將提取限制為作為來源使用的 JSON 陣列的第二個元素，如下所示

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[1]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 3    | 7    |
+------+------+

在欄位定義中，"$" 會將整個匹配項傳遞給欄位；"$.x" 和 "$.y" 只會傳遞該匹配項中對應於鍵 x 和 y 的值。如需更多資訊，請參閱JSON 路徑語法。

NESTED PATH（或簡稱為 NESTED；PATH 是可選的）會為它所屬的 COLUMNS 子句中的每個匹配項產生一組記錄。如果沒有匹配項，則巢狀路徑的所有欄位都會設定為 NULL。這會實作最上層子句和 NESTED [PATH] 之間的外部聯結。可以透過在 WHERE 子句中套用適當的條件來模擬內部聯結，如下所示

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
    ->     '$[*]' COLUMNS(
    ->             a INT PATH '$.a',
    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
    ->            )
    ->    ) AS jt
    -> WHERE b IS NOT NULL;

+------+------+
| a    | b    |
+------+------+
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
+------+------+

同級巢狀路徑（即，同一個 COLUMNS 子句中存在兩個或多個 NESTED [PATH] 實例）會一個接一個地依序處理。當一個巢狀路徑正在產生記錄時，任何同級巢狀路徑表達式的欄位都會設定為 NULL。這表示單一包含 COLUMNS 子句中單一匹配項的總記錄數是 NESTED [PATH] 修飾詞所產生所有記錄的總和，而不是乘積，如下所示

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
    ->     '$[*]' COLUMNS(
    ->         a INT PATH '$.a',
    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
    ->     )
    -> ) AS jt;

+------+------+------+
| a    | b1   | b2   |
+------+------+------+
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
+------+------+------+

FOR ORDINALITY 欄位會列舉 COLUMNS 子句產生的記錄，並可用於區分巢狀路徑的父記錄，尤其是在父記錄中的值相同時，如下所示

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": "a_val",
    '>       "b": [{"c": "c_val", "l": [1,2]}]},
    '>     {"a": "a_val",
    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
    ->     '$[*]' COLUMNS(
    ->       top_ord FOR ORDINALITY,
    ->       apath VARCHAR(10) PATH '$.a',
    ->       NESTED PATH '$.b[*]' COLUMNS (
    ->         bpath VARCHAR(10) PATH '$.c',
    ->         ord FOR ORDINALITY,
    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
    ->         )
    ->     )
    -> ) as jt;

+---------+---------+---------+------+-------+
| top_ord | apath   | bpath   | ord  | lpath |
+---------+---------+---------+------+-------+
|       1 |  a_val  |  c_val  |    1 | 1     |
|       1 |  a_val  |  c_val  |    1 | 2     |
|       2 |  a_val  |  c_val  |    1 | 11    |
|       2 |  a_val  |  c_val  |    2 | 22    |
+---------+---------+---------+------+-------+

來源文件包含兩個元素的陣列；這些元素中的每一個都會產生兩列。在整個結果集中，apath 和 bpath 的值是相同的；這表示它們不能用於判斷 lpath 值是來自相同還是不同的父項。ord 欄位的值與 top_ord 等於 1 的記錄集保持不變，因此這兩個值來自單一物件。剩餘的兩個值來自不同的物件，因為它們在 ord 欄位中的值不同。

通常，您不能聯結依賴於同一個 FROM 子句中前面表格的欄位的衍生表。根據 SQL 標準，MySQL 對於表格函數例外處理；這些函數被視為側向衍生表。這是隱含的，因此根據標準，不允許在 JSON_TABLE() 之前使用。

假設您有一個使用此處顯示的語句建立和填入的表格 t1

CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);

INSERT INTO t1 () VALUES
	ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
	ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
	ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
	ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
	ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
;

然後您可以執行聯結，例如這個聯結，其中 JSON_TABLE() 作為衍生表，同時它又參考先前參照的表格中的欄位

SELECT c1, c2, JSON_EXTRACT(c3, '$.*') 
FROM t1 AS m 
JOIN 
JSON_TABLE(
  m.c3, 
  '$.*' 
  COLUMNS(
    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY, 
    bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY, 
    ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
  )
) AS tt
ON m.c1 > tt.at;

嘗試對此查詢使用 LATERAL 關鍵字會引發 ER_PARSE_ERROR。

上一個首頁向上下一個