更新时间:2026-11-06 GMT+08:00
分享

JSON函数

概述

JSON函数用于对JSON格式的字符串进行解析、查询、构造和验证操作。AI DataLake支持SQL标准(ISO/IEC TR 19075-6)中定义的JSON路径表达式,支持lax和strict两种模式,涵盖JSON有效性判断、路径查询、标量提取、JSON对象与数组构造等完整的JSON处理能力。

更多内容请参考JSON Functions。

适用场景

  • JSON数据验证:使用IS JSON判断字符串是否为合法JSON,或是否为特定JSON类型。
  • JSON路径查询:使用JSON_EXISTS判断路径是否存在,使用JSON_VALUE提取标量值,使用JSON_QUERY提取JSON片段。
  • JSON序列化:使用JSON_STRING将值序列化为JSON字符串。
  • JSON构造:使用JSON_OBJECT和JSON_ARRAY动态构建JSON对象和数组。

约束与限制

  • JSON路径表达式使用符号表示根节点,支持属性访问(符号表示根节点,支持属性访问(.a)、数组元素访问(.a[0].b)和数组遍历(.a[0].b)和数组遍历(.a[*].b)。
  • 路径模式分为lax和strict两种:lax模式对错误更宽容,将错误转为空序列;strict模式从模式角度检查数据,遇到不符合路径表达式的数据会抛出错误。省略时默认为strict模式。
  • JSON_VALUE仅能提取标量值,非标量值无法返回;支持VARCHAR/STRING、BOOLEAN、INTEGER、DOUBLE四种返回类型。
  • JSON_OBJECT的键必须为非NULL的字符串字面量,值可以为任意表达式。
  • JSON_ARRAY省略ON NULL时默认为ABSENT ON NULL(忽略NULL值),而JSON_OBJECT省略ON NULL时默认为NULL ON NULL(将NULL作为null值包含)。
  • Lax模式的部分功能当前未完全支持(上游缺陷CALCITE-4717),非标准行为不保证。

语法结构

语法

返回类型

说明

示例

IS JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ]

BOOLEAN

判断给定字符串是否为合法JSON。

指定可选的type参数可约束允许的JSON对象类型。如果字符串是合法JSON但不是指定类型,则返回false。默认为VALUE。

-- TRUE '1' IS JSON '[]' IS JSON '{}' IS JSON -- TRUE
'"abc"' IS JSON
-- FALSE
'abc' IS JSON
NULL IS JSON
-- TRUE
'1' IS JSON SCALAR
-- FALSE
'1' IS JSON ARRAY
-- FALSE
'1' IS JSON OBJECT
-- FALSE
'{}' IS JSON SCALAR
-- FALSE
'{}' IS JSON ARRAY
-- TRUE
'{}' IS JSON OBJECT

JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ])

BOOLEAN

判断JSON字符串是否满足给定的路径搜索条件。

如果省略错误处理行为,默认为FALSE ON ERROR。

-- TRUE
SELECT JSON_EXISTS('{"a": true}', '$.a');
-- FALSE
SELECT JSON_EXISTS('{"a": true}', '$.b');
-- TRUE
SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}',
  '$.a[0].b');

-- TRUE
SELECT JSON_EXISTS('{"a": true}', 'strict $.b' TRUE ON ERROR);
-- FALSE
SELECT JSON_EXISTS('{"a": true}', 'strict $.b' FALSE ON ERROR);

JSON_STRING(value)

STRING

将值序列化为JSON。

该函数返回包含序列化值的JSON字符串。如果值为NULL,则函数返回NULL。

-- NULL JSON_STRING(CAST(NULL AS INT)) -- '1'
JSON_STRING(1)
-- 'true'
JSON_STRING(TRUE)
-- '"Hello, World!"'
JSON_STRING('Hello, World!')
-- '[1,2]'
JSON_STRING(ARRAY[1, 2])

JSON_VALUE(jsonValue, path [RETURNING ] [ { NULL | ERROR | DEFAULT } ON EMPTY ] [ { NULL | ERROR | DEFAULT } ON ERROR ])

STRING / BOOLEAN / INTEGER / DOUBLE

从JSON字符串中提取标量值。

该方法在JSON字符串中搜索给定的路径表达式,如果路径处的值为标量则返回该值。非标量值无法返回。默认情况下,值以STRING类型返回。使用dataType可选择不同的类型,支持的类型包括:VARCHAR / STRING、BOOLEAN、INTEGER、DOUBLE。

对于空路径表达式或错误,可以定义行为来返回null、抛出错误或返回定义的默认值。省略时默认分别为NULL ON EMPTY或NULL ON ERROR。默认值可以是字面量或表达式。如果默认值本身抛出错误,则回退到ON EMPTY的错误行为,并对ON ERROR抛出错误。

对于路径中包含空格等特殊字符的情况,可以使用['property']或["property"]来选择父对象中的指定属性。务必在属性名两侧加上单引号或双引号。在SQL中使用JSON_VALUE时,路径是一个已加单引号的字符参数,因此需要转义属性名周围的单引号,例如JSON_VALUE('{"a b": "true"}', '$.[''a b'']')。

-- "true" JSON_VALUE('{"a": true}', '$.a') -- TRUE
JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN)
-- "false"
JSON_VALUE('{"a": true}', 'lax $.b'
DEFAULT FALSE ON EMPTY)
-- "false"
JSON_VALUE('{"a": true}', 'strict $.b'
DEFAULT FALSE ON ERROR)
-- 0.998D
JSON_VALUE('{"a.b": [0.998,0.996]}','$.["a.b"][0]'
RETURNING DOUBLE)
-- "right"
JSON_VALUE('{"contains blank": "right"}', 'strict $.[''contains blank'']' NULL ON EMPTY DEFAULT 'wrong' ON ERROR)

JSON_QUERY(jsonValue, path [RETURNING ] [ { WITHOUT | WITH CONDITIONAL | WITH UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON EMPTY ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ])

STRING / ARRAY<STRING>

从JSON字符串中提取JSON值。

结果以STRING或ARRAY<STRING>类型返回。可以通过RETURNING子句控制。

wrappingBehavior决定提取的值是否应包装为数组,以及是无条件包装还是仅当值本身不是数组时才包装。

onEmpty和onError分别决定路径表达式为空或发生错误时的行为。默认情况下,两种情况都返回null。其他选项包括使用空数组、空对象或抛出错误。

-- '{ "b": 1 }'
JSON_QUERY('{ "a": { "b": 1 } }', '$.a')
-- '[1, 2]'
JSON_QUERY('[1, 2]', '$')
-- NULL
JSON_QUERY(CAST(NULL AS STRING), '$')
-- '["c1","c2"]'
JSON_QUERY('{"a":[{"c":"c1"},{"c":"c2"}]}',
    'lax $.a[*].c')

-- 将结果包装到数组中
-- '[{}]'
JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER)
-- '[1, 2]'
JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER)
-- '[[1, 2]]'
JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER)

-- 必须包装标量才能返回
-- NULL
JSON_QUERY(1, '$')
-- '[1]'
JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER)

-- 路径表达式为空/存在错误时的行为
-- '{}'
JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY)
-- '[]'
JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR)

JSON_OBJECT([[KEY] key VALUE value]* [ { NULL | ABSENT } ON NULL ])

STRING

从键值对列表构建JSON对象字符串。

注意键必须为非NULL的字符串字面量,而值可以为任意表达式。

该函数返回JSON字符串。ON NULL行为定义如何处理NULL值。省略时默认为NULL ON NULL。

从其他JSON构造函数调用(JSON_OBJECT、JSON_ARRAY)创建的值会直接插入而非作为字符串插入。这允许构建嵌套的JSON结构。

-- '{}' JSON_OBJECT() -- '{"K1":"V1","K2":"V2"}'
JSON_OBJECT('K1' VALUE 'V1', 'K2' VALUE 'V2')
-- Expressions as values
JSON_OBJECT('orderNo' VALUE orders.orderId)
-- ON NULL
JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) NULL ON NULL)   -- '{"K1":null}'
JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) ABSENT ON NULL) -- '{}'
-- '{"K1":{"K2":"V"}}'
JSON_OBJECT(
KEY 'K1'
VALUE JSON_OBJECT(
KEY 'K2'
VALUE 'V'
)
)

JSON_ARRAY([value]* [ { NULL | ABSENT } ON NULL ])

STRING

从值列表构建JSON数组字符串。

该函数返回JSON字符串。值可以为任意表达式。ON NULL行为定义如何处理NULL值。省略时默认为ABSENT ON NULL。

从其他JSON构造函数调用(JSON_OBJECT、JSON_ARRAY)创建的元素会直接插入而非作为字符串插入。这允许构建嵌套的JSON结构。

-- '[]' JSON_ARRAY() -- '[1,"2"]' JSON_ARRAY(1, '2') -- Expressions as values
JSON_ARRAY(orders.orderId)
-- ON NULL
JSON_ARRAY(CAST(NULL AS STRING) NULL ON NULL) -- '[null]'
JSON_ARRAY(CAST(NULL AS STRING) ABSENT ON NULL) -- '[]'
-- '[[1]]'
JSON_ARRAY(JSON_ARRAY(1))

相关文档