JSON Functions

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