MySQL JSON_TABLE() 函数

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

应用：数据库字段以JSON 存储后，实际应用需要对其中一个字段进行查询

语法

JSON_TABLE(json,path COLUMNS(column[,column[,...]]))column:name
1

参数
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 将会给出错误。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

实例：

数组分别取值
JSON 数组中有两个对象。

路径表达式 $[*] 则表示数组中的每个元素。

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

id FOR ORDINALITY: 
列名为 id，列的内容为从 1 开始的自增序列。
x varchar(255) PATH '$.x': 
列名为 x，列的内容是对应了对象中的成员 x。
y INT PATH '$.y':
列名为 y，列的内容是对应了对象中的成员 y。
1
2
3
4
5
6
7
8
9
10
11
12
13

select * FROM
    JSON_TABLE(
        '[{"x": "01", "y": 11}, {"x": "02", "y": 22}]',
        '$[*]'
        COLUMNS (
            id FOR ORDINALITY,
            x varchar(255) PATH '$.x',
            y INT PATH '$.y'
        )
    ) AS t;


1
2
3
4
5
6
7
8
9
10
11
12
13

路径格式：

取所有元素：$[\ast ]，表示取所有元素；取指定单个元素：{如}^{\prime }$[0]'，表示取第一个元素； 取指定多个元素：
如$[0,2,4]，表示取第一、三、五个元素；取范围连续元素：如$[0 TO 2]，表示取第一到第三个元素；
如果不指定元素，如$[]，则会报错

直接取值
JSON 数组中有多个对象。

路径表达式 $[*] 则表示数组中的每个元素。

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

id FOR ORDINALITY: 列名为 id，列的内容为从 1 开始的自增序列。
x INT PATH ‘$’: 列名为 x，列的内容是对应了对象的值。

select * FROM
    JSON_TABLE(
        '["1","11","111"]',
        '$[*]'
        COLUMNS (
            id FOR ORDINALITY,
            x INT PATH '$'
        )
    ) AS t;

1
2
3
4
5
6
7
8
9
10