JSON Functions

Updated on 2024-04-19 GMT+08:00

View PDF

JSON functions use JSON path expressions described in the SQL standard ISO/IEC TR 19075-6. Their syntax is inspired by ECMAScript and adopts many of its features, but is neither a subset nor a superset of it.

There are two types of path expressions: lax mode and strict mode. When omitted, it defaults to strict mode. Strict mode is intended to check data from a schema perspective and will throw an error when data does not conform to the path expression. However, functions like JSON_VALUE allow for defining fallback behavior when encountering errors. Lax mode, on the other hand, will convert errors to an empty sequence.

The special character $ represents the root node in a JSON path. Paths can access properties ($.a), array elements ($.a[0].b), or all elements in an array ($.a[*].b).

Known limitations: not all features of lax mode are currently supported correctly.

**Table 1** JSON functions
SQL Function	Description
IS JSON [ { VALUE \| SCALAR \| ARRAY \| OBJECT } ]	Determines whether a given string is a valid JSON string. Specifying an optional type parameter will impose constraints on the allowed types of JSON objects. If the string is a valid JSON but not of that type, it returns false. The default value is 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 ])	Determines whether a JSON string satisfies a given path search condition. If error behavior is ignored, FALSE ON ERROR is the default value. -- 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)	Serializes a value to JSON. This function returns a JSON string containing the serialized value. If the value is NULL, the function returns 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 <dataType>] [ { NULL \| ERROR \| DEFAULT <defaultExpr> } ON EMPTY ] [ { NULL \| ERROR \| DEFAULT <defaultExpr> } ON ERROR ])	Extracts a scalar from a JSON string. This method searches for the given path expression in the JSON string and returns the value if it is a scalar. If it is not a scalar value, it cannot be returned. By default, the value is returned as a STRING type. The returnType can be used to select a different type, supporting the following types: VARCHAR/STRING BOOLEAN INTEGER DOUBLE For empty path expressions or errors, it can be defined to return null, throw an error, or return a defined default value. If omitted, the default value is NULL ON EMPTY or NULL ON ERROR. The default value can be a literal or an expression. If the default value itself causes an error, it will execute the error behavior of ON EMPTY and 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 ])	Extracts a JSON value from a JSON string. The result is always returned as a STRING. The RETURNING clause is currently not supported. The wrappingBehavior determines whether the extracted value should be wrapped in an array unconditionally or only if the value itself is not an array. The onEmpty and onError determine the behavior when the path expression is empty or throws an error. By default, null is returned in both cases. Other options are to use an empty array, an empty object, or throw an error. -- '{ "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 ])	Builds a JSON object string from a list of key-value pairs. Note that the keys must be non-null string literals, while the values can be any expression. The function returns a JSON string. The ON NULL behavior defines how to handle NULL values. If omitted, the default is NULL ON NULL. Values created from another JSON constructor (JSON_OBJECT, JSON_ARRAY) will be inserted directly instead of being inserted as a string. This allows for building nested JSON structures. -- '{}' 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 ])	Builds a JSON object string by aggregating key-value expressions into a single JSON object. The key expression must return a non-null string. The value expression can be anything, including other JSON functions. If the value is NULL, the ON NULL behavior defines what to do. If omitted, the default is NULL ON NULL. Note that keys must be unique. If a key appears multiple times, an error will be thrown. This feature is currently not supported in OVER windows. -- '{"Apple":2,"Banana":17,"Orange":0}' SELECT JSON_OBJECTAGG(KEY product VALUE cnt) FROM orders
JSON_ARRAY([value]* [ { NULL \| ABSENT } ON NULL ])	Builds a JSON array string from a list of values. The function returns a JSON string. These values can be any expression. The ON NULL behavior defines how to handle NULL values. If omitted, the default is ABSENT ON NULL. Elements created from another JSON constructor (JSON_OBJECT, JSON_ARRAY) will be inserted directly instead of being inserted as a string. This allows for building nested JSON structures. -- '[]' 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 ])	Builds a JSON object string by aggregating elements into an array. The element expression can be anything, including other JSON functions. If the value is NULL, the ON NULL behavior defines what to do. If omitted, the default is ABSENT ON NULL. This feature is currently not supported in OVER windows, unbounded session windows, or hop windows. -- '["Apple","Banana","Orange"]' SELECT JSON_ARRAYAGG(product) FROM orders