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

版本 8.4

9.0
8.0

8.0 日文

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

14.17.6 JSON 表格函數

本節包含關於將 JSON 資料轉換為表格資料的 JSON 函數資訊。MySQL 8.4 支援一個這樣的函數，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 的列時，此子句也會在該子句的欄位上被觸發。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 的使用。對應於 {"b":1} 的列對於路徑 "$.a" 為空，並且嘗試將 [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。