Skip to main content

​
Scalar Functions

These functions can be applied to individual json column values, as well as array & string columns. However, for some functions to work as expected, the array or string value may need to be cast to JSON first.

JSON(expr)

Casts the given expr to JSON data typeExamples:
Function CallResult
JSON(‘[1, 2, “C”]’)[1,2,“C”]
JSON(’{“A”: “B”, “C”: {“D”: 5, “F”: 7}}’){“A”:“B”,“C”:{“D”:5,“F”:7}}
JSON(null)null

JSON_ARRAY (value[,...] <null_hand>)

Creates a JSON array out of the given list of values.An optional <null_hand> parameter can be added to the end of the list to specify handling of null values, though, at present, this only exists for compatibility with other databases’ syntax—the option will be ignored and all null values will be added to the array as null. Accepted null handling options:
  • NULL ON NULL
This function is only available in SQL or in the native API via /execute/sql.
Examples:
Function CallResult
JSON_ARRAY(1, 2, ‘C’)[1,2,“C”]
JSON_ARRAY(JSON(’{“A”: “B”}’), JSON(’{“C”: 4}’))[{“A”:“B”},{“C”:4}]
JSON_ARRAY(1, null, ‘C’ NULL ON NULL)[1,null,“C”]

JSON_ARRAY_APPEND(arr, value)

Appends the given value to the given array arr of type string, array, or JSONExamples:
Function CallResult
JSON_ARRAY_APPEND(‘[1, 2]’, ‘C’)[1,2,“C”]
JSON_ARRAY_APPEND(’{“A”: “B”}’, JSON(’{“C”: 4}’))[{“A”:“B”},{“C”:4}]

JSON_ARRAY_CONTAINS (json, path, val)

Returns whether the given JSON json contains the primitive value val at the JSON query path, pathExamples:
Function CallResult
JSON_ARRAY_CONTAINS(‘[1, 2]’, ’$’, 2)true
JSON_ARRAY_CONTAINS(‘[1, 2]’, ’$’, ‘C’)false
JSON_ARRAY_CONTAINS(’{“A”: [2, 3]}’, ’$.A’, 2)true
JSON_ARRAY_CONTAINS(’{“A”: {“B”: [3, 4]}}’, ’$[“A”][“B”]’, 4)true

JSON_CARDINALITY(json)

Alias for JSON_LENGTH

JSON_EXISTS(json, path)

Returns whether the given JSON json contains the JSON query path, pathExamples:
Function CallResult
JSON_EXISTS(‘[1, 2]’, ’$‘)true
JSON_EXISTS(‘[1, 2]’, ’$.A’)false
JSON_EXISTS(’{“A”: [2, 3]}’, ’$.A’)true
JSON_EXISTS(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)true

JSON_KEYS(json, path)

Returns the set of keys of the JSON object at the given path in json; null, if the path doesn’t exist or contains a non-object (non-map) valueExamples:
Function CallResult
JSON_KEYS(’{}’, ’$’)[]
JSON_KEYS(‘[1, 2]’, ’$.A’)null
JSON_KEYS(’{“A”: [2, 3]}’, ’$’)[“A”]
JSON_KEYS(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”]’)[“B”,“D”]
JSON_KEYS(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)null
JSON_KEYS(’{“A”: {“B”: 3, “D”: {}}}’, ’$[“A”][“D”]’)[]

JSON_LENGTH(json[, path])

Returns the number of items in the top-level object of the given json, or at the level of the optional JSON pathExamples:
Function CallResult
JSON_LENGTH(’{}‘)0
JSON_LENGTH(’[]‘)0
JSON_LENGTH(‘[1, 2]‘)2
JSON_LENGTH(’{“A”: [2, 3]}‘)1
JSON_LENGTH(’{“A”: {“B”: 3}, “D”: “E”}‘)2
JSON_LENGTH(’{“A”: [2, 3]}’, ’$.A’)2
JSON_LENGTH(’{“A”: {“B”: 3}, “D”: “E”}’, ’$.A’)1

JSON_MAKE_ARRAY(value)

Creates a JSON array out of the given valueExamples:
Function CallResult
JSON_MAKE_ARRAY(1)[1]
JSON_MAKE_ARRAY(JSON_ARRAY(1,2,3))[[1,2,3]]
JSON_MAKE_ARRAY(JSON(’{“A”: “B”}’))[{“A”:“B”}]

JSON_OBJECT (value[,...] <null_hand>)

Creates a JSON map out of the given value setAn optional <null_hand> parameter can be added to the end of the list to specify handling of null values, though, at present, this only exists for compatibility with other databases’ syntax—the option will be ignored and all null values will be added to the array as null. Accepted null handling options:
  • NULL ON NULL
This function is only available in SQL or in the native API via /execute/sql.
Examples:
Function CallResult
JSON_OBJECT(){}
JSON_OBJECT(‘A’: 2){“A”:2}
JSON_OBJECT(‘A’ VALUE 2){“A”:2}
JSON_OBJECT(KEY ‘A’ VALUE 2){“A”:2}
JSON_OBJECT(‘A’: ‘B’, ‘C’: null NULL ON NULL){“A”:“B”,“C”:null}

JSON_PRETTY(json)

Returns a formatted version of the given json objectExamples:
Function CallJSON_PRETTY(‘[1,2,“C”]‘)
Return
[
1,
2,
“C”
]
Function CallJSON_PRETTY(’{“A”: “B”, “C”: {“D”: 5, “F”: 7}}‘)
Return
{
“A”: “B”,
“C”: {
“D”: 5,
“F”: 7
}
]

JSON_QUERY (json, path [<return>])

Returns the JSON object at the given path in json; null, if the path doesn’t exist or contains a primitive (non-object) valueAn optional return clause can be added to the end of the path to specify the return type of the object found, as well as the error handling for the object lookup. The format of the clause is:
Return clause is only available in SQL or in the native API via /execute/sql.
ParameterDescription
<wrapper>Whether the returned object(s) should be wrapped in an array before being returned Valid wrapping schemes include:
  • WITHOUT [ARRAY] - no wrapper is applied (default)
  • WITH [UNCONDITIONAL] [ARRAY] - return the object(s) in an array
  • WITH CONDITIONAL [ARRAY] - if returning a single object, don’t wrap it; if returning multiple objects, wrap them in an array
<empty hand>The scheme to use for handling empty returned objects Valid schemes include:
  • NULL - return null (default)
  • ERROR - throw an error
  • EMPTY ARRAY - return an empty array
  • EMPTY OBJECT - return an empty object
<error hand>The scheme to use for handling errors in looking up the object Valid schemes include:
  • NULL - return null (default)
  • ERROR - throw an error
  • EMPTY ARRAY - return an empty array
  • EMPTY OBJECT - return an empty object
Examples:
Function CallResult
JSON_QUERY(‘[1, 2]’, ’$’)[1,2]
JSON_QUERY(‘[1, 2]’, ’$.A’)null
JSON_QUERY(’{“A”: [2, 3]}’, ’$.A’)[2,3]
JSON_QUERY(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”]’){“B”:3,“D”:5}
JSON_QUERY(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)null
JSON_QUERY(’{“A”: {“B”: 3, “D”: [5, 6]}}’, ’$[“A”][“D”]’)[5,6]
JSON_QUERY(’{“A”: []}’, ’$.B’ EMPTY ARRAY ON EMPTY)[]
JSON_QUERY(’{“A”: 0}’, ’$.A’ EMPTY OBJECT ON ERROR){}
JSON_QUERY(’{“A”: 2, “C”: 4}’, ’$.*‘)null
JSON_QUERY(’{“A”: 2, “C”: 4}’, ’$.*’ WITH WRAPPER)[2,4]

JSON_REPLACE(json, path, repl)

Returns the given json with the value at the JSON path path replaced with the replacement value replExamples:
Function CallResult
JSON_REPLACE(‘[1, 2]’, ’$’, ‘[3, 4]’)[3,4]
JSON_REPLACE(‘[1, 2]’, ’$.A’, ‘“B”’)[1,2]
JSON_REPLACE(’{“A”: [2, 3]}’, ’$.A’, ’{“D”: 5}’){“A”:{“D”:5}}
JSON_REPLACE(’{“A”: {“B”: 3}}’, ’$[“A”]’, ‘“D”’){“A”:“D”}
JSON_REPLACE(’{“A”: {“B”: [3, 4]}}’, ’$[“A”][“B”]’, 5){“A”:{“B”:5}}

JSON_TYPE(expr)

Returns the JSON type of the given expr
Expression TypeJSON Type
BOOLEANBOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • UNSIGNED BIGINT
LONG
  • REAL
  • DOUBLE
DOUBLE
  • JSON
  • VARCHAR
  • BOOLEAN - if true or false
  • LONG - if an integer value
  • DOUBLE - if a floating-point value
  • STRING - if a double-quoted literal
  • OBJECT - if a JSON map
  • ARRAY - if a JSON array
ARRAY (any)ARRAY

JSON_VALUE (json, path [return])

Returns the JSON value at the given path in json; null, if the path doesn’t exist or contains an object (non-primitive) valueAn optional return clause can be added to the end of the path to specify the return type of the value found, as well as the error handling for the value lookup. The format of the clause is:
Return clause is only available in SQL or in the native API via /execute/sql.
ParameterDescription
<type>The JSON type of the value to return; if the value is not already of that type, it will be cast to that type, if possible Valid types include:
  • BOOLEAN
  • LONG
  • DOUBLE
  • STRING
<empty hand>The scheme to use for handling empty returned values Valid schemes include:
  • NULL - return null (default)
  • ERROR - throw an error
  • DEFAULT <expr> - return the given expression expr
<error hand>The scheme to use for handling errors in looking up the value Valid schemes include:
  • NULL - return null (default)
  • ERROR - throw an error
  • DEFAULT <expr> - return the given expression expr
Examples:
Function CallResult
JSON_VALUE(‘1’, ’$‘)1
JSON_VALUE(‘[1, 2]’, ’$.A’)null
JSON_VALUE(’[“A”, “B”]’, ‘$.1’)B
JSON_VALUE(’{“A”: [2, 3]}’, ’$[“A”][“1”]‘)3
JSON_VALUE(’{“A”: {“B”: 3, “D”: 5}}’, ’$[“A”][“D”]‘)5
JSON_VALUE(’{“A”: {“B”: 3, “D”: [5, 6]}}’, ’$.A.D.1’)6
JSON_VALUE(‘1’, ’$’ RETURNING DOUBLE)1.0
JSON_VALUE(’{“A”: []}’, ’$.B’ DEFAULT ‘<null>’ ON EMPTY)<null>
JSON_VALUE(’{“A”: []}’, ’$.A’ ERROR ON ERROR)error returned

​
Aggregation Functions

The following functions can be used on JSON columns within aggregations.

JSON_ARRAYAGG(expr)

Combines column values within a group into a single JSON array of those values.See examples.

JSON_ARRAYAGG_DISTINCT(expr)

Combines column values within a group into a single JSON array of those values, removing any duplicates.See examples.

JSON_COLLECT_SET(expr)

Alias for JSON_ARRAYAGG_DISTINCT.