JSON functions

SQL Function Description

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

Determine whether a given string is valid JSON.

Specifying the optional type argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned. The default is VALUE.

-- Returns TRUE
'1' IS JSON
'[]' IS JSON
'{}' IS JSON

-- Returns TRUE
'"abc"' IS JSON
-- Returns FALSE
'abc' IS JSON
NULL IS JSON

-- Returns TRUE
'1' IS JSON SCALAR
-- Returns FALSE
'1' IS JSON ARRAY
-- Returns FALSE
'1' IS JSON OBJECT

-- Returns FALSE
'{}' IS JSON SCALAR
-- Returns FALSE
'{}' IS JSON ARRAY
-- Returns TRUE
'{}' IS JSON OBJECT
 
JSON_ARRAY([value]*
    [ { NULL | ABSENT } ON NULL ])

Builds a JSON array string from a list of value.

This function returns a JSON string. The values can be arbitrary expressions. The ON NULL behavior defines how to treat NULL values. If omitted, ABSENT ON NULL is assumed by default.

Elements which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures.

-- Returns '[]'
JSON_ARRAY()
-- Returns '[1,"2"]'
JSON_ARRAY(1, '2')

-- Returns Expressions as values
JSON_ARRAY(orders.orderId)

-- Returns ON NULL
JSON_ARRAY(CAST(NULL AS STRING) NULL ON NULL) -- '[null]'
JSON_ARRAY(CAST(NULL AS STRING) ABSENT ON NULL) -- '[]'

-- Returns '[[1]]'
JSON_ARRAY(JSON_ARRAY(1))
 
JSON_EXISTS(jsonValue, path
    [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ])

Determines whether the JSON string jsonValue satisfies a given path search criteria.

If the error behavior is omitted, FALSE ON ERROR is assumed as the default.

-- Returns TRUE
SELECT JSON_EXISTS('{"a": true}', '$.a');

-- Returns FALSE
SELECT JSON_EXISTS('{"a": true}', '$.b');

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

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

-- Returns False
SELECT JSON_EXISTS('{"a": true}', 'strict $.b' FALSE 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 keys must be non-NULL string literals, while values may be arbitrary expressions.

This function returns a JSON string. The ON NULL behavior defines how to treat NULL values. If omitted, NULL ON NULL is assumed by default.

Values which are created from another JSON construction function call (JSON_OBJECT, JSON_ARRAY) are inserted directly rather than as a string. This allows building nested JSON structures.

-- Returns '{}'
JSON_OBJECT()

-- Returns '{"K1":"V1","K2":"V2"}'
JSON_OBJECT('K1' VALUE 'V1', 'K2' VALUE 'V2')

---- Expressions as values
JSON_OBJECT('orderNo' VALUE orders.orderId)

---- ON NULL
-- Returns '{"K1":null}'
JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) NULL ON NULL)
-- Returns '{}'
JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) ABSENT ON NULL)

-- Returns '{"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-nullable character string. Value expressions can be arbitrary, including other JSON functions. If a value is NULL, the ON NULL behavior defines what to do. If omitted, NULL ON NULL is assumed by default.

Note that keys must be unique. If a key occurs multiple times, an error will be thrown.

This function is currently not supported in OVER windows.

-- Returns '{"Apple":2,"Banana":17,"Orange":0}'
SELECT
JSON_OBJECTAGG(KEY product VALUE cnt)
FROM orders
 
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 JSON values from the JSON string jsonValue based on the specified path.

The result is always returned as a STRING. The RETURNING clause is currently not supported.

The WRAPPER clause specifies whether the extracted value should be wrapped into an array, and whether to do so unconditionally or only if the value itself isn’t an array already.

The ON EMPTY and ON ERROR clauses determine the behavior in case the path expression is empty, or in case an error was raised, respectively. By default, in both cases null is returned. Other choices are to use an empty array, an empty object, or to raise an error.

-- Returns '{ "b": 1 }'
JSON_QUERY('{ "a": { "b": 1 } }', '$.a')

-- Returns '[1, 2]'
JSON_QUERY('[1, 2]', '$')

-- Returns NULL
JSON_QUERY(CAST(NULL AS STRING), '$')

-- Returns '["c1","c2"]'
JSON_QUERY('{"a":[{"c":"c1"},{"c":"c2"}]}', 'lax $.a[*].c')

---- Wrap result into an array
-- Returns '[{}]'
JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER)

-- Returns '[1, 2]'
JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER)

-- Returns '[[1, 2]]'
JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER)

---- Scalars must be wrapped to be returned
-- Returns NULL
JSON_QUERY(1, '$')

-- Returns '[1]'
JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER)

---- Behavior if path expression is empty / there is an error
-- Returns '{}'
JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY)

-- Returns '[]'
JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR)

JSON_STRING(value)

Serializes a value into JSON.

This function returns a JSON string containing the serialized value. If the value is NULL, the function returns NULL.

-- Returns NULL
JSON_STRING(CAST(NULL AS INT))

-- Returns '1'
JSON_STRING(1)

-- Returns 'true'
JSON_STRING(TRUE)

-- Returns '"Hello, World!"'
JSON_STRING('Hello, World!')

-- Returns '[1,2]'
JSON_STRING(ARRAY[1, 2])
 
JSON_VALUE(jsonValue, path [RETURNING ]
    [ { NULL | ERROR | DEFAULT } ON EMPTY ]
    [ { NULL | ERROR | DEFAULT } ON ERROR ])

Extracts a scalar from a JSON string jsonValue based on the specified path.

This method searches a JSON string for a given path expression and returns the value if the value at that path is scalar. Non-scalar values can’t be returned. By default, the value is returned as STRING. Using the RETURNING clause a different type can be chosen, with the following types being supported:

  • VARCHAR / STRING

  • BOOLEAN

  • INTEGER

  • DOUBLE

For empty path expressions or errors a behavior can be defined to either return null, raise an error or return a defined default value instead. When omitted, the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value may be a literal or an expression. If the default value itself raises an error, it falls through to the error behavior for ON EMPTY, and raises an error for ON ERROR.

-- Returns STRING: "true"
JSON_VALUE('{"a": true}', '$.a')

-- Returns BOOLEAN: true
JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN)

-- Returns STRING: "false"
JSON_VALUE('{"a": true}', 'lax $.b'
DEFAULT FALSE ON EMPTY)

-- Returns STRING: "false"
JSON_VALUE('{"a": true}', 'strict $.b'
DEFAULT FALSE ON ERROR)