MariaDB JSON_TABLE() 函数使用指南

在 MariaDB 中,JSON_TABLE() 是一个内置函数, 它从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。该函数是在 MariaDB 10.6.0 中引入的。

MariaDB JSON_TABLE() 语法

这里是 MariaDB 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 指定的位置是否有值将 10 放在名字为 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() 函数将抛出一个错误。

返回值

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

MariaDB JSON_TABLE() 示例

这里有几个展示了 MariaDB JSON_QUOTE() 函数基本用法的示例。

简单示例

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

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 中的 $ 代表了当前的上下文对象,也就是数组中的每个对象。

默认值

MariaDB JSON_TABLE() 允许您为那些空的列值指定默认值。

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

提取指定的行

MariaDB JSON_TABLE() 允许您只提取通过表达式指定的 JSON 成员。

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 语句只返回一行。

拉平内嵌的数组

MariaDB JSON_TABLE() 允许您使用 NESTED 子句提取内嵌的 JSON 数组成员。

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 数组转化为关系表中的两行。

拉平内嵌的对象

MariaDB JSON_TABLE() 允许您使用 NESTED 子句提取内嵌的 JSON 对象成员。

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

结论

在 MariaDB 中,JSON_TABLE() 是一个内置函数, 它从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。