> ## Documentation Index > Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt > Use this file to discover all available pages before exploring further. # JSON Functions ## 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. Casts the given `expr` to JSON data type Examples:
Function Call Result
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*
Creates a JSON array out of the given list of values. An optional `` 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](/content/api/rest/execute_sql_rest). Examples:
Function Call Result
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"]
Appends the given `value` to the given array `arr` of type string, array, or JSON Examples:
Function Call Result
JSON\_ARRAY\_APPEND('\[1, 2]', 'C') \[1,2,"C"]
JSON\_ARRAY\_APPEND('\{"A": "B"}', JSON('\{"C": 4}')) \[\{"A":"B"},\{"C":4}]
Returns whether the given JSON `json` contains the primitive value `val` at the JSON query path, `path` Examples:
Function Call Result
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
Alias for `JSON_LENGTH` Returns whether the given JSON `json` contains the JSON query path, `path` Examples:
Function Call Result
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
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) value Examples:
Function Call Result
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"]') \[]
Returns the number of items in the top-level object of the given `json`, or at the level of the optional JSON `path` Examples:
Function Call Result
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
Creates a JSON array out of the given `value` Examples:
Function Call Result
JSON\_MAKE\_ARRAY(1) \[1]
JSON\_MAKE\_ARRAY(JSON\_ARRAY(1,2,3)) \[\[1,2,3]]
JSON\_MAKE\_ARRAY(JSON('\{"A": "B"}')) \[\{"A":"B"}]
Creates a JSON map out of the given `value` set An optional `` 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](/content/api/rest/execute_sql_rest). Examples:
Function Call Result
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}
Returns a formatted version of the given `json` object Examples:
**Function Call** JSON\_PRETTY('\[1,2,"C"]')
**Return** 
\[
            1,
            2,
            "C"
            ]
**Function Call** JSON\_PRETTY('\{"A": "B", "C": \{"D": 5, "F": 7}}')
**Return** 
\{
            "A": "B",
            "C": \{
            "D": 5,
            "F": 7
            }
            ]
Returns the JSON object at the given `path` in `json`; null, if the path doesn't exist or contains a primitive (non-object) value An 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: ``` [ WRAPPER] [ ON EMPTY] [ ON ERROR] ``` Return clause is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest).
Parameter Description
\ 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
\ 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
\ 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 Call Result
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]
Returns the given `json` with the value at the JSON path `path` replaced with the replacement value `repl` Examples:
Function Call Result
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}}
Returns the JSON type of the given `expr`
Expression Type JSON Type
BOOLEAN BOOLEAN
  • 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
Returns the JSON value at the given `path` in `json`; null, if the path doesn't exist or contains an object (non-primitive) value An 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: ``` [RETURNING ] [ ON EMPTY] [ ON ERROR] ``` Return clause is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest).
Parameter Description
\ 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
\ The scheme to use for handling empty returned values Valid schemes include:
  • NULL - return *null* *(default)*
  • ERROR - throw an error
  • DEFAULT \ - return the given expression expr
\ The scheme to use for handling errors in looking up the value Valid schemes include:
  • NULL - return *null* *(default)*
  • ERROR - throw an error
  • DEFAULT \ - return the given expression expr
Examples:
Function Call Result
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 '\' ON EMPTY) \
JSON\_VALUE('\{"A": \[]}', '\$.A' ERROR ON ERROR) *error returned*
## Aggregation Functions The following functions can be used on [JSON](/content/concepts/json) columns within [aggregations](/content/sql/query/aggregation#sql-aggregation). Combines column values within a group into a single JSON array of those values. See [examples](/content/concepts/json#json-func-agg-ex-agg). Combines column values within a group into a single JSON array of those values, removing any duplicates. See [examples](/content/concepts/json#json-func-agg-ex-agg-dist). Alias for `JSON_ARRAYAGG_DISTINCT`.