# JSON函数 JSON函数使用SQL标准的ISO/IEC TR 19075-6中描述的JSON路径表达式。它们的语法受到ECMAScript的启发并采用了ECMAScript的许多特性，但既不是其子集，也不是其超集。 路径表达式有两种，一种是宽松模式，另一种是严格模式。当省略时，它默认为严格模式。严格模式旨在从模式的角度检查数据，当数据不符合路径表达式时将抛出错误。但是，像JSON_VALUE这样的函数允许在遇到错误时定义回退行为。但是宽松模式会将错误转换为空序列。 特殊字符$表示JSON路径中的根节点。路径可以访问属性（$.a）、数组元素（$.a\[0\].b）或数组中的所有元素（$.a\[\*\].b）。 已知限制：当前并非所有宽松模式的特性都得到了正确的支持。 表1JSON函数 | SQL函数 | 描述 | |:---|:---| | IS JSON \[ { VALUE \| SCALAR \| ARRAY \| OBJECT } \] | 判断给定的字符串是否是有效的JSON字符串。 指定可选类型参数会对允许类型的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 \]) | 判断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) | 将该值序列化为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 \]) | 从JSON字符串中提取标量。 此方法在JSON字符串中搜索给定的路径表达式，如果该路径上的值是标量，则返回该值。如果不是标量值，则无法返回。默认情况下，该值以STRING类型返回。使用returnType可以选择不同的类型，支持以下类型： VARCHAR / STRING BOOLEAN INTEGER DOUBLE 对于空路径表达式或错误，可以定义为返回null、报错或返回定义的默认值。省略时，默认值为NULL ON EMPTY或NULL ON ERROR。默认值可以是字面量或表达式。如果默认值本身引发错误，那么它将执行ON EMPTY和ON ERROR的错误行为。 ``` -- "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) ``` | | JSON_QUERY(jsonValue, path \[ { WITHOUT \| WITH CONDITIONAL \| WITH UNCONDITIONAL } \[ ARRAY \] WRAPPER \] \[ { NULL \| EMPTY ARRAY \| EMPTY OBJECT \| ERROR } ON EMPTY \] \[ { NULL \| EMPTY ARRAY \| EMPTY OBJECT \| ERROR } ON ERROR \]) | 从JSON字符串中提取JSON值。 结果始终以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') -- Wrap result into an array -- '[{}]' 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) -- Scalars must be wrapped to be returned -- NULL JSON_QUERY(1, '$') -- '[1]' JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER) -- Behavior if path expression is empty / there is an error -- '{}' 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 \]) | 从键值对列表构建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_OBJECTAGG(\[KEY\] key VALUE value \[ { NULL \| ABSENT } ON NULL \]) | 通过将键值表达式聚合到单个JSON对象中来构建JSON对象字符串。 键表达式必须返回一个不可为空的字符串。值表达式可以是任意的，包括其他JSON函数。如果值为NULL，则ON NULL行为定义要执行的操作。如果省略，则默认为NULL ON NULL。 注意key必须是唯一的。如果一个key出现多次，则会抛出错误。 目前在OVER窗口中不支持此功能。 ``` -- '{"Apple":2,"Banana":17,"Orange":0}' SELECT JSON_OBJECTAGG(KEY product VALUE cnt) FROM orders ``` | | JSON_ARRAY(\[value\]\* \[ { NULL \| ABSENT } ON NULL \]) | 从值列表构建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)) ``` | | JSON_ARRAYAGG(items \[ { NULL \| ABSENT } ON NULL \]) | 通过将元素聚合到数组中来构建JSON对象字符串。 元素表达式可以是任意的，包括其他JSON函数。如果值为NULL，则ON NULL行为定义要执行的操作。如果省略，则默认为ABSENT ON NULL。 目前在OVER窗口、无界session窗口或hop窗口中不支持此功能。 ``` -- '["Apple","Banana","Orange"]' SELECT JSON_ARRAYAGG(product) FROM orders ``` |