JSON_TABLE

在线编辑

JSON_TABLE 表函数返回计算 SQL/JSON 路径表达式的结果表。行 SQL/JSON path expression 的结果序列中的每一项都表示结果表中的一行或多行。

注：

¹ 可以选择在 COLUMNS 子句之前指定 ON ERROR 子句。

json-table-正则-column-definition

json-table-格式化-column-definition

json-table-命中率-column-definition

json-table-嵌套-column-definition

数据类型

ccsid-子句

normalize-子句

JSON 表达式

指定返回字符，图形或二进制字符串值的表达式。如果返回字符或图形值，那么它必须包含格式正确的 JSON 对象。如果返回二进制值，那么它必须包含 JSON 对象的 BSON 表示。

JSON-expression 指定 sql-json-path-expression的初始上下文项，在 sql-json-path-expression 中标识为 $。

如果 JSON-expression 是 JSON 数组，那么将使用密钥名称 SYSIBM_ROOT_ARRAY 将其隐式包装，将 JSON-expression 修改为 JSON 对象。为 JSON_TABLE 提供的任何路径表达式都将隐式修改以考虑此附加键。

如果 JSON-expression 未包含正确格式的数据，那么 JSON_TABLE 将返回空表，除非使用 ON ERROR 子句指定其他错误行为。

FORMAT JSON 或 FORMAT BSON

指定如何解释 JSON-expression 。

格式 JSON: 指示 JSON-expression 包含 JSON 数据。如果 JSON-expression 是二进制数据，那么会将数据解释为 UTF-8 或 UTF-16。不能使用 EBCDIC CCSID 对二进制数据进行编码。
格式 Bson: 指示 JSON-expression 包含 JSON 数据的 BSON 表示。指定 FORMAT BSON 时， JSON-expression 必须是二进制字符串数据类型。

如果未指定 FORMAT 子句，并且 JSON-expression 是字符或图形字符串，那么会将 JSON-expression 视为 JSON。如果 JSON-expression 是二进制字符串，那么 JSON-expression 将被视为 BSON。

sql-json-path-expression

指定解释为 SQL/JSON 路径表达式的字符或图形字符串表达式。必须以小写形式指定 sql-json-path-expression 中的关键字。此表达式返回一个输出序列，其中序列中的每个项都由列定义用于在输出表中生成一行或多行。如果输出序列为空，那么 JSON_TABLE 的结果为空表。 sql-json-path-expression 不能是空字符串或全部为空白的字符串。

有关 SQL/JSON 路径表达式的内容的信息，请参阅 sql-json-path-expression。

AS 路径名

指定用于标识 sql-json-path-expression的名称。

EMPTY ON ERROR 或 ERROR ON ERROR

指定迂到表级别错误时 JSON_TABLE 的期望行为。

EMPTY ON 错误: 迂到表级别错误时，将返回空表。这是缺省情况。
错误开启错误: 迂到表级别错误时，将返回错误。

列

指定结果表的输出列，包括列名，数据类型以及如何计算每行的列值。

所有结果列长度的总和不能超过 64K 字节。有关根据数据类型的列字节计数的信息，请参阅最大行大小。此函数最多可以返回大约 200 列，具体取决于列名的长度和列路径的长度。

json-table-正则-column-definition

指定结果表的输出列，包括列名，数据类型和 SQL/JSON 路径表达式，以从行的序列项中抽取值。

列名称

指定结果表中的列名。不能对该名称进行限定，并且不能对结果表的多列使用相同的名称。

数据类型

指定列的数据类型。对于 CHAR 和 VARCHAR 列， CCSID 不能是 65535。

路径 column-path-expression-constant

指定解释为 SQL/JSON 路径的字符或图形字符串常量。

column-path-expression-constant 指定一个 SQL/JSON 路径表达式，该表达式确定对 sql-json-path-expression中的 SQL/JSON 路径表达式求值的结果项的列值。给定将 sql-json-path-expression 作为外部提供的上下文项进行处理的结果中的项，将对 column-path-expression-constant 进行求值并返回输出序列。如果 JSON 对象包含多个具有相同键的值，那么将仅针对输出序列返回键的其中一个值。

根据此输出序列确定列值，如下所示:

如果返回空序列，那么 ON EMPTY 子句将提供列的值。如果指定了 ERROR ON EMPTY ，那么将发出错误。
如果返回空序列并且未指定 ON EMPTY 子句，那么会将空值分配给该列。
如果返回单个元素序列，并且该元素的类型不是 JSON 数组或 JSON 对象，那么该值将转换为针对该列指定的 data-type 。
如果返回单个元素序列，并且该元素的类型为 JSON 数组或 JSON 对象，那么将返回错误。
如果返回具有多个元素的序列，那么将返回错误。
如果发生错误，那么 ON ERROR 子句指定列的值。

column-path-expression-constant 的值不得是空字符串或所有空格的字符串。如果未指定 PATH 子句，那么会将 column-path-expression-constant 定义为以 column-name开头的 "$."。

空的

指定为列返回空序列时的行为。

空空空: 返回 SQL 空值。这是缺省情况。
EMPTY 上出错: 返回了错误。
缺省缺省-表达式 ON EMPTY: 返回由 default-expression 指定的值。

发生错误时

指定对列返回错误时的行为。

空开启错误: 返回 SQL 空值。这是缺省情况。
错误开启错误: 返回了错误。
缺省值 default-expression ON 错误: 返回由 default-expression 指定的值。

如果未指定此子句:

如果指定了表级别 ERROR ON ERROR 子句，那么将返回错误。
否则，将返回 SQL 空值。

json-table-格式化-column-definition

指定结果表的输出列，包括列名，数据类型和 SQL/JSON 路径表达式，以从行的序列项中抽取值。抽取的值将格式化为 JSON 字符串。

列名称

指定结果表中的列名。不能对该名称进行限定，并且不能对结果表的多列使用相同的名称。

数据类型

指定列的数据类型。数据类型必须是字符或图形类型。对于 CHAR 和 VARCHAR 列， CCSID 不能是 65535。

格式 JSON

指示应将检索到的数据格式化为 JSON 字符串。

路径 column-path-expression-constant

指定解释为 SQL/JSON 路径的字符或图形字符串常量。

column-path-expression-constant 指定一个 SQL/JSON 路径表达式，该表达式确定通过对 sql-json-path-expression 中的 SQL/JSON 路径表达式以及先前嵌套 PATHs 指定的所有路径进行求值而产生的项的列值。给定将 sql-json-path-expression 作为外部提供的上下文项进行处理的结果中的项，将对 column-path-expression-constant 进行求值并返回输出序列。如果 JSON 对象包含多个具有相同键的值，那么将仅针对输出序列返回键的其中一个值。

根据此输出序列确定列值，如下所示:

如果返回空序列，那么 ON EMPTY 子句将提供列的值。如果指定了 ERROR ON EMPTY ，那么将返回错误。
如果返回空序列并且未指定 ON EMPTY 子句，那么会将空值分配给该列。
如果发生错误，那么 ON ERROR 子句指定列的值。

column-path-expression-constant 的值不能是空字符串或所有空格的字符串。如果未指定 PATH 子句，那么会将 column-path-expression-constant 定义为以 column-name开头的 "$."。

WITHOUT ARRAY WRAPPER 或 WITH ARRAY WRAPPER

指定是否应将输出值包装在 JSON 数组中。

无数组包装程序: 指示结果未回绕。这是缺省情况。使用导致两个或多个 SQL/JSON 元素的序列的 SQL/JSON 路径会导致错误。
使用无条件数组包装程序: 指示结果用方括号括起来以创建 JSON 数组。
使用条件数组包装程序: 指示如果返回了多个 SQL/JSON 元素，那么会将结果括在方括号中以创建 JSON 数组。

保留引号或省略引号

指定返回标量字符串时是否应除去周围的引号。

保留引号: 指示未从标量字符串中除去引号。这是缺省情况。
省略引号: 指示从标量字符串中除去引号。指定略去引号时，不能指定 WITH ARRAY WRAPPER 子句

空的

指定对列返回空序列时的行为。

空空空: 返回 SQL 空值。这是缺省情况。
EMPTY 上出错: 返回了错误。
空的数组: 返回空的 JSON 数组。
空对象: 返回空的 JSON 对象。

发生错误时

指定对列返回错误时的行为。

空开启错误: 返回 SQL 空值。
错误开启错误: 返回了错误。
出错时数组为空: 返回空的 JSON 数组。
出错时对象为空: 返回空的 JSON 对象。

如果未指定此子句:

如果指定了表级别 ERROR ON ERROR 子句，那么将返回错误。
否则，将返回 SQL 空值。

json-table-命中率-column-definition

指定结果表的序数列。

列名称

指定结果表中的列名。不能对该名称进行限定，并且不能对结果表的多列使用相同的名称。

针对有序 ALITY

指定 column-name 是外层嵌套级别的结果表的序列。此列的数据类型为 BIGINT。

如果序数列不在嵌套列定义中，那么将从 1 开始按顺序对结果表的行进行编号，并且序数列包含当前行的序号。
如果序数列在嵌套列定义中，那么由外层嵌套列定义生成的行将按顺序编号 (从 1 开始) ，并且序数列包含当前行的序号。如果外层嵌套列定义位于外层嵌套列定义中，那么每次将外层嵌套列定义的路径表达式应用于外层嵌套列定义的结果时，编号将重新启动 1。

json-table-嵌套-column-definition

指定在当前级别嵌套的一个或多个列。

嵌套路径嵌套路径表达式常量: 指定解释为 SQL/JSON 路径的字符或图形字符串常量。 嵌套路径表达式常量 指定一个 SQL/JSON 路径表达式，该表达式确定对 sql-json-path-expression 中的 SQL/JSON 路径表达式以及先前的嵌套 PATHs 中的 嵌套路径表达式常量 进行求值所产生的项的列值。根据将 sql-json-path-expression 作为外部提供的上下文项和先前嵌套 PATHs 中的 嵌套-path-expression-constant 处理结果中的项，将对 嵌套-path-expression-constant 进行求值，并将其用作嵌套列的上下文。
AS 嵌套路径名: 指定当前路径的名称。
列: 指定要包含在此嵌套级别中的列。

表 1. 受支持的 JSON 到 SQL 结果列转换
JSON 类型	SQL 类型	注意
编号	SMALLINT 整数 BIGINT	如果源值超出目标数据类型的范围，那么将返回溢出错误。
编号	十进制 NUMERIC	如果需要，会将生成的数字值转换为目标数据类型的精度和小数位。添加或除去必需的前导零数。在数字的小数部分中，添加了必需的尾数零或消除了必需的位数。此截断行为类似于从 DECIMAL 到 DECIMAL 的强制类型转换行为。
编号	浮点 DOUBLE REAL DECFLOAT	如果源值超出目标数据类型的范围，那么将返回溢出错误。如果源值包含比目标数据类型的精度更重要的数字，那么会将源值舍入为目标数据类型的精度。
字符串或数字	char varchar clob graphic vargraphic dbclob	必要时，会使用分配的转换规则中描述的规则将生成的值转换为目标数据类型的 CCSID ，然后再将其转换为长度有限的目标类型。如果指定的长度限制小于 CCSID 转换后生成的字符串的长度，那么将发生截断。如果任何非空白字符被截断，那么会发生警告。如果目标类型是固定长度的字符串 (CHAR 或 GRAPHIC) ，并且目标类型的指定长度大于 CCSID 转换生成的字符串的长度，那么将在末尾填充空白。这种截断和填充行为类似于字符或图形字符串的检索分配。
字符串或数字	BOOLEAN	生成的值将按照布尔赋值中描述的布尔赋值规则转换为布尔值。
字符串	日期	JSON 字符串将转换为日期值。 JSON 字符串必须采用下列其中一种格式: ISO yyyy-mm-dd USA mm/dd/yyyy EUR dd.mm.yyyy JIS yyyy-mm-dd
字符串	时间	JSON 字符串将转换为时间值。 JSON 字符串必须采用下列其中一种格式: ISO hh.mm.ss EUR hh.mm.ss JIS hh:mm:ss HMS hh:mm:ss
字符串	TIMESTAMP	JSON 字符串将转换为时间戳记值。 JSON 字符串必须采用下列其中一种格式: ISO 年月日 hh:mm:ss.nnnnnn IBM SQL yyyy-mm-dd-hh.mm.ss.nnnnnn ISO-8601 yyyy-mm-ddThh:mm:ss.nnnnnn<+/-Offset> 如果 ISO-8601 时间戳记包含偏移量，那么将使用该偏移量来调整时间戳记。例如， 2021-03-18T03:00:00.0-02:00 将转换为 2021-03-18 05:00:00.000000。
布尔值	BOOLEAN	JSON 布尔值作为布尔值返回。
布尔值	char varchar clob graphic vargraphic dbclob	JSON 布尔值将转换为 `true` 或 `false` 字符串，并使用 JSON 字符串的转换规则作为字符串返回。
布尔值	smallint integer bigint decfloat	JSON 布尔值作为数字值 0 (false) 或 1 (true) 返回。
数组或对象	char varchar clob graphic vargraphic dbclob	必须使用 FORMAT JSON 列返回。 JSON 数组或对象作为 JSON 格式的字符串返回。生成的转换与字符串类型相同。
空	任何	将 JSON 空值转换为 SQL 空值

示例

这些示例对以下 JSON 文档执行操作:

{ 
  "id" : 901,
  "name" : { "first":"John", "last":"Doe" },
  "phones": [ { "type":"home", "number":"555-3762"},
              { "type":"work", "number":"555-8792"}]
}

列出员工标识，名字，姓氏以及第一个电话类型和号码:

SELECT U."id", U."first name",U."last name",U."phone type",U."phone number"
  FROM EMPLOYEE_TABLE E,
    JSON_TABLE(E.jsondoc, 
               'lax $'
               COLUMNS( "id" INTEGER,
                        "first name"  VARCHAR(20) PATH 'lax $.name.first',
                        "last name"   VARCHAR(20) PATH 'lax $.name.last',
                        "phone type"  VARCHAR(20) PATH 'lax $.phones[0].type',
                        "phone number"  VARCHAR(20) PATH 'lax $.phones[0].number')
                      ) AS U

id    first name    last name    phone type    phone number
---   ----------    ---------    ----------    ------------
901   John          Doe          home          555-3762

列出员工标识，名字，姓氏以及所有可用的电话类型和号码:

SELECT U."id", U."first name",U."last name",U."phone type",U."number" AS "phone number"
  FROM EMPLOYEE_TABLE E,
    JSON_TABLE(E.jsondoc, 
               'lax $'
                COLUMNS( "id" INTEGER,
                         "first name"  VARCHAR(20) PATH 'lax $.name.first',
                         "last name"   VARCHAR(20) PATH 'lax $.name.last',
                         NESTED PATH 'lax $.phones[*]'
                           COLUMNS (
                                    "phone type"  VARCHAR(20) PATH 'lax $.type',
                                    "number"  VARCHAR(20) )
                                        )
                        ) AS U

id    first name    last name    phone type    phone number
---   ----------    ---------    ----------    ------------
901   John          Doe          home          555-3762
901   John          Doe          work          555-8792