使用 JSON_TABLE

JSON_TABLE 表函数将 JSON 文档转换为关系表。

我们将使用下表 EMP，其中包含四行，每行一个 JSON 对象。通过使用 JSON_TABLE 函数，我们将抽取数据，以便将其视为关系数据。


CREATE TABLE emp(jsondoc VARCHAR(32000) CCSID 1208);

INSERT INTO emp VALUES 
'{"id":901, "name" : { "first":"John", "last":"Doe" }, "office" : "E-334", 
"phones" : [ { "type":"home", "number":"555-3762" }, 
             { "type":"work", "number":"555-7242" } ], 
"accounts" : [ { "number":"36232"}, { "number":"73263"}] }';

INSERT INTO emp VALUES
'{"id":902, "name" : { "first":"Peter", "last":"Pan" }, "office" : "E-216", 
"phones" : [ { "type":"work", "number":"555-8925" } ], 
"accounts" : [ { "number":"76232"}, {"number":"72963"}] }';

INSERT INTO emp VALUES
'{"id":903, "name" : { "first":"Mary", "last":"Jones" }, "office" : "E-739", 
"phones" : [ { "type":"work", "number":"555-4311" }, 
             { "type":"home", "number":"555-6312" } ], }';

INSERT INTO emp VALUES
'{"id":904, "name" : { "first":"Sally", "last":"Smith" } }';

JSON_TABLE 函数由三个部分组成。

要解构的 JSON 对象。
从 JSON 对象生成零行或更多行的路径表达式。
要返回的结果列的定义。这包括列名，结果数据类型以及用于查找列信息的路径表达式。

返回简单信息

首先，我们将使用 JSON_TABLE 从存储在 EMP 表中的 JSON 中抽取员工姓名和办公号。

SELECT t.first, t.last, t.office 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (             
                            first VARCHAR(10) PATH 'lax $.name.first',
                            last VARCHAR(10) PATH 'lax $.name.last',
                            office VARCHAR(10) PATH 'lax $.office'
                            )
                   ) AS t;

在此示例中，第一个自变量指示要处理的 JSON 的源，在本例中，这是表 EMP 中的列 JSONDOC。第二个参数是 JSON 文档中起点的路径。 $ 指示在开始时启动。接下来是结果列的定义。每个都具有名称，数据类型以及用于在 JSON 对象中查找列数据的路径。对于其中每个列，将指定列路径以使用当前上下文 ($) ，后跟值的键名。

此查询的结果为:

FIRST	LAST	办公室
John	DOE	E-334
Peter	平移	E-216
马雷	Jones	E-739
Sally	Smith	(空)

请注意，由于 Sally Smith 没有办公室而导致的结构错误将作为空值返回。影响此行为的因素有两个。由于 lax 用于 office 列的路径，因此忽略了结构错误并返回了空值。如果 strict 已用于 Office 路径，那么路径导航将返回错误。当针对列返回错误时， JSON_TABLE 的缺省行为是返回空值。您可以通过将 ERROR ON ERROR 子句添加到列定义来覆盖 strict 方式。

返回 JSON 格式化数据

JSON_TABLE 能够返回包含格式化为 JSON 的数据的列。通过在列定义中使用关键字 FORMAT JSON 来完成此操作。结果必须包含单个值 :JSON 对象， JSON 数组或标量值。

以下是使用 JSON_TABLE 将员工姓名信息抽取为 JSON 数据的示例。

SELECT t.id, t.name, t.office 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (             
                            id INTEGER PATH 'lax $.id',
                            name VARCHAR(100) FORMAT JSON PATH 'lax $.name',
                            office VARCHAR(10) FORMAT JSON PATH 'lax $.office'
                            )
                   ) AS t;

此查询将生成以下结果：

标识	名称	办公室
901	{"first":"John","last":"Doe"}	"E-334"
902	{"first":"Peter","last":"Pan"}	"E-216"
903	{"first":"Mary","last":"Jones"}	"E-739"
904	{"first":"Sally","last":"Smith"}	(空)

请注意， NAME 列返回表示 JSON 格式化对象的字符串。

当 FORMAT JSON 列的路径生成字符串值时，缺省行为是返回字符串值的引号。这是针对 OFFICE 列显示的结果。可以使用省略 SCALAR STRING 子句从标量字符串中除去引号。

此处未演示在返回 FORMAT JSON 数据时适用的另一个选项。如果路径找到 JSON 对象序列，那么必须将这些对象合并到数组中，才能作为 JSON 值成功返回。可以使用 WITH ARRAY WRAPPER 子句来完成此操作。缺省情况是不添加包装器，这将导致错误。

处理 JSON 数组

从 JSON 数组返回信息时，每个数组元素将作为结果表中的单独行返回。现在，我们将使用 JSON_TABLE 来抽取 JSON 数组中的电话类型和号码。

SELECT t.type, t.number 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $.phones[*]'               
                   COLUMNS (             
                            type VARCHAR(20) PATH 'lax $.type',
                            number VARCHAR(20) PATH 'lax $.number'
                            )
                   ) AS t;

在此示例中，路径表达式是 $.phones[*] 表示 phones 数组的所有元素。用于在 JSON 对象中查找列数据的列路径表达式是上下文项 $，后跟要返回的值的键名称。在这种情况下，上下文项是父路径表达式 $.phones[*]的结果。

此查询的结果为:

类型	编号
主页	555-3762
远程工作	555-7242
远程工作	555-8925
远程工作	555-4311
主页	555-6312

处理嵌套信息

在上一个示例中，仅返回电话号码不是很有用，因为不会返回与该号码关联的人员的相关信息。为了获取这些信息，我们需要定义一个嵌套的列。嵌套列允许数组值与多级 JSON 对象中较高级别存在的数据项相关联。

在此示例中，我们使用嵌套列来返回与电话号码关联的名称。

SELECT t.* 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (
                            outer_ordinality FOR ORDINALITY,
                            first VARCHAR(10) PATH 'lax $.name.first',
                            last VARCHAR(10) PATH 'lax $.name.last',
                            NESTED PATH 'lax $.phones[*]'
                                 COLUMNS (             
                                          nested_ordinality FOR ORDINALITY,
                                          type VARCHAR(20) PATH 'lax $.type',
                                          number VARCHAR(20) PATH 'lax $.number'
                                          )
                            )
                   ) AS t;

行路径表达式为 $，表示 JSON 对象的顶级。第一列和第二列返回名字和姓氏。后跟嵌套列定义。嵌套路径的路径 lax $.phone[*] 表示处理电话数组的所有元素。在该数组中，数组元素的 type 和 number 值将作为表中的最后两列返回。

此查询还演示了有序列的概念。这是一个从 1 开始为 JSON_TABLE 的每个调用的每个结果行生成数字的列。

此查询的结果为:

OUTER_	FIRST	LAST	NESTED_	类型	编号
1	John	DOE	1	主页	555-3762
1	John	DOE	2	远程工作	555-7242
1	Peter	平移	1	远程工作	555-8925
1	马雷	Jones	1	远程工作	555-4311
1	马雷	Jones	2	主页	555-6312
1	Sally	Smith	1	(空)	(空)

在此示例中，嵌套级别之间存在父/子关系。 LEFT OUTER JOIN 用于组合父/子关系中的信息。由于 Sally Smith 没有电话信息，因此 LEFT OUTER JOIN 将为电话列返回 NULL 值。

现在，让我们检查两个顺序列。在父级别，每行都具有相同的序数值。虽然您可能期望看到按顺序编号的每一行，但此查询将对每个 JSONDOC 行执行单独的 JSON_TABLE 调用。对于每个调用，编号从 1 开始，因此结果中的每一行都以 OUTER_ordALITY 的 1 结束。如果用于此示例的 JSON 是包含所有四个员工的一个对象，那么对于每个员工对象， OUTER_ordALITY 都将递增。对于NESTED_命中率，每次父级更改时，编号将在 1 处重新启动。

同代嵌套

嵌套列可以存在于同一级别。以下示例使用同代嵌套列来返回电话和帐户信息。第一个嵌套列子句访问电话信息。第二个嵌套列子句访问帐户信息。

SELECT t.first, t.last, t.type, t.number, t.account 
    FROM emp, 
        JSON_TABLE( 
                   emp.jsondoc,          
                   'lax $'               
                   COLUMNS (
                            first VARCHAR(10) PATH 'lax $.name.first',
                            last VARCHAR(10) PATH 'lax $.name.last',
                            NESTED PATH 'lax $.phones[*]'
                                 COLUMNS (             
                                          type VARCHAR(20) PATH 'lax $.type',
                                          number VARCHAR(20) PATH 'lax $.number'
                                          ),
                            NESTED PATH 'lax $.accounts[*]'
                                 COLUMNS (             
                                          account VARCHAR(20) PATH 'lax $.number'
                                          )
                            )
                   ) AS t;

此查询的结果为:

FIRST	LAST	类型	编号	帐户
John	DOE	主页	555-3762	(空)
John	DOE	远程工作	555-7242	(空)
John	DOE	(空)	(空)	36232
John	DOE	(空)	(空)	73263
Peter	平移	远程工作	555-8925	(空)
Peter	平移	(空)	(空)	76232
Peter	平移	(空)	(空)	72963
马雷	Jones	远程工作	555-4311	(空)
马雷	Jones	主页	555-6312	(空)
马雷	Jones	(空)	(空)	(空)
Sally	Smith	(空)	(空)	(空)
Sally	Smith	(空)	(空)	(空)

在此示例中，电话和帐户信息之间存在同代关系。对于同代相关嵌套，将使用 UNION 来组合信息。由于电话和帐户信息在 JSON 文档中处于同一级别，因此没有包含这两种信息的结果行。