MySQL JSON_TABLE() 函数使用指南

MySQL JSON_TABLE() 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。

`JSON_TABLE()` 语法

这里是 MySQL JSON_TABLE() 的语法：

JSON_TABLE(
    json,
    path COLUMNS (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[, column[, ...]])

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

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

参数

json

必需的。一个 JSON 文档。
path

必需的。一个路径表达式。
column

必需的。定义一个列。您可以使用如下 4 中方式定义一个列：
- name FOR ORDINALITY: 生成一个从 1 开始的计数器列，名字为 name。
- name type PATH string_path [on_empty] [on_error]: 将由路径表达式 string_path 指定的值放在名字为 name 的列中。
- name type EXISTS PATH string_path：根据 string_path 指定的位置是否有值将 1 或 0 放在名字为 name 的列中。
- NESTED [PATH] path COLUMNS (column[, column[, ...]]): 将内嵌的对象或者数组中的数据拉平放在一行中。
{NULL | ERROR | DEFAULT value} ON EMPTY

可选的。如果指定了，它决定了指定路径下没有数据时的返回值：
- NULL ON EMPTY: 如果指定路径下没有数据，JSON_TABLE() 函数将使用 NULL，这是默认的行为。
- DEFAULT value ON EMPTY: 如果指定路径下没有数据，JSON_TABLE() 函数将使用 value。
- ERROR ON EMPTY: 如果指定路径下没有数据，JSON_TABLE() 函数将抛出一个错误。
{NULL | ERROR | DEFAULT value} ON ERROR

可选的。如果指定了，它决定了处理错误的逻辑：
- NULL ON ERROR: 如果有错误，JSON_TABLE() 函数将使用 NULL，这是默认的行为。
- DEFAULT value ON ERROR: 如果有错误，JSON_TABLE() 函数将使用 value。
- ERROR ON ERROR: 如果有错误，JSON_TABLE() 函数将抛出一个错误。

返回值

MySQL JSON_TABLE() 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。您可以像普通的表一样使用 JSON_TABLE() 返回的表。

JSON_TABLE() 函数将在以下情况下返回错误：

如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

`JSON_TABLE()` 示例

简单示例

在本例中，将数组中的每个对象元素转为一个关系表中的每行。关系表中的列对应了每个对象中的成员。

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x": 10, "y": 11}, {"x": 20, "y": 21}]',
        '$[*]'
        COLUMNS (
            id FOR ORDINALITY,
            x INT PATH '$.x',
            y INT PATH '$.y'
        )
    ) AS t;

+------+------+------+
| id   | x    | y    |
+------+------+------+
|    1 |   10 |   11 |
|    2 |   20 |   21 |
+------+------+------+

这里， JSON 数组中有两个对象：{"x": 10, "y": 11} 和 {"x": 20, "y": 21}。

路径表达式 $[*] 则表示数组中的每个元素，也就是数组中的那两个对象。

COLUMNS 子句定义了关系表中的 3 个列：

id FOR ORDINALITY: 列名为 id，列的内容为从 1 开始的自增序列。
x INT PATH '$.x: 列名为 x，列的内容是对应了对象中的成员 x。
y INT PATH '$.y: 列名为 y，列的内容是对应了对象中的成员 y。

其中 $.x 和 $.y 中的 $ 代表了当前的上下文对象，也就是数组中的每个对象。

默认值

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]',
        '$[*]'
        COLUMNS (
            id FOR ORDINALITY,
            x INT PATH '$.x' DEFAULT '100' ON EMPTY,
            y INT PATH '$.y'
        )
    ) AS t;

+------+------+------+
| id   | x    | y    |
+------+------+------+
|    1 |   10 |   11 |
|    2 |  100 |   21 |
|    3 |   30 | NULL |
+------+------+------+

这里，注意下面的一行的列定义：

x INT PATH '$.x' DEFAULT '100' ON EMPTY,

其中 DEFAULT '100' ON EMPTY 定义了当对象中不存在成员 x 或者 x 的值为空时要使用默认值 100。

提取指定的行

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]',
        '$[1]'
        COLUMNS (
            x INT PATH '$.x',
            y INT PATH '$.y'
        )
    ) AS t;

+------+------+
| x    | y    |
+------+------+
| NULL |   21 |
+------+------+

这里，路径表达式 $[1] 指示了只提取 JSON 数组中的第 2 元素，也就是 {"y": 21}。因此，SELECT 语句只返回一行。

拉平内嵌的数组

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":[11, 12]}, {"x":20,"y":[21, 22]}]',
        '$[*]'
        COLUMNS (
            x INT PATH '$.x',
            NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')
        )
    ) AS t;

+------+------+
| x    | y    |
+------+------+
|   10 |   11 |
|   10 |   12 |
|   20 |   21 |
|   20 |   22 |
+------+------+

这里，数组中对象的成员 y 是个数组，注意下面的列定义：

NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')

这里使用了 NESTED PATH '$.y[*]' 子句展开 y 对应的数组，并将 y 数组中的每个元素放入名称为 y 的列中。

因为每个 y 数组中都有 2 个元素，因此一个 y 数组转化为关系表中的两行。

拉平内嵌的对象

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":{"a":11,"b":12}},{"x":20,"y":{"a":21,"b":22}}]',
        '$[*]'
        COLUMNS (
            x INT PATH '$.x',
            NESTED PATH '$.y' COLUMNS (
                ya INT PATH '$.a',
                yb INT PATH '$.b'
            )
        )
    ) AS t;

+------+------+------+
| x    | ya   | yb   |
+------+------+------+
|   10 |   11 |   12 |
|   20 |   21 |   22 |
+------+------+------+

这里使用了 NESTED PATH '$.y' 子句将对象 y 中成员提取到 2 列：

成员 a 被提取到列 ya
成员 b 被提取到列 yb

JSON_TABLE() 语法

参数

返回值

JSON_TABLE() 示例