> ## 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 ## JSON Type The *JSON* [data type](/content/concepts/types#types-chart) provides the capability to store native JSON in a single column and apply JSON-specific functions to it. ### Create Table ```sql SQL theme={null} CREATE TABLE example.js ( j JSON ) ``` ```python Python theme={null} table_name = 'example.js' columns = [ ['j', 'string', 'json'] ] gpudb.GPUdbTable(columns, table_name, db = kinetica) ``` ### Insert Data ```sql SQL theme={null} INSERT INTO example.js VALUES ( '[1, 2, "C"]' ), ( '{"A": "B", "C": {"D": 5, "F": 7}}' ) ``` ```python Python (as strings) theme={null} data = [ [ '[1, 2, "C"]' ], [ '{"A": "B", "C": {"D": 5, "F": 7}}' ] ] t = gpudb.GPUdbTable(name = 'example.js', db = kinetica) t.insert_records(data) ``` ```python Python (as native types) theme={null} data = [ [ '[1, 2, "C"]' ], [ {"A": "B", "C": {"D": 5, "F": 7}} ] ] t = gpudb.GPUdbTable(name = 'example.js', db = kinetica) t.insert_records(data) ``` ### Retrieve Data To render a JSON value as a single-line string value: ```sql SQL theme={null} SELECT j FROM example.js ``` ```python Python theme={null} t = gpudb.GPUdbTable(name = 'example.js', db = kinetica) records = t.get_records_by_column(["j"]) for record in records['j']: print(f'{record}') ``` ```python Python (w/ native type conversions) theme={null} t = gpudb.GPUdbTable(name = 'example.js', db = kinetica, convert_special_types_on_retrieval = True) records = t.get_records_by_column(["j"], encoding = 'json') for record in records['j']: print(f'{record}') ``` JSON array and JSON map output: ``` +-------------------------------+ | j | +-------------------------------+ | [1,2,"C"] | | {"A":"B","C":{"D":5,"F":7}} | +-------------------------------+ ``` To render a JSON value as a formatted structure: ```sql SQL theme={null} SELECT JSON_PRETTY(j) AS j FROM example.js ``` ```python Python (via SQL) theme={null} t = gpudb.GPUdbTable(name = 'example.js', db = kinetica) records = t.get_records_by_column(["JSON_PRETTY(j) AS j"]) for record in records['j']: print(f'{record}') ``` ```python Python (via native library) theme={null} t = gpudb.GPUdbTable(name = 'example.js', db = kinetica) records = t.get_records_by_column(["j"]) for record in records['j']: print(json.dumps(json.loads(record), indent=4)) ``` JSON array and JSON map output: ``` +---------------------+ | j | +---------------------+ | [ 1, 2, "C" ] | | { "A": "B", "C": { "D": 5, "F": 7 } } | +---------------------+ ``` ## JSON Operators JSON operators can be used to determine whether a given `value` meets (or does `NOT` meet) the criteria for being of a certain JSON type `category`. Note that the following syntax differs between SQL and the native APIs (like Python) and that the `category` is specified differently between the two. ```sql SQL theme={null} IS [NOT] JSON [] ``` ```python Native API theme={null} IS_JSON( [,]) NOT(IS_JSON( [,])) ``` The given `category code` (APIs) or `category name` (SQL) will check the given value against the following JSON types: | Category Code | Category Name | JSON Types | | ------------- | ------------- | ------------------------------------------------------------------- | | `0` | *N/A* | JSON array or object *(API default)* | | `1` | `VALUE` | Any JSON type *(SQL default)* | | `2` | `ARRAY` | JSON array | | `3` | `OBJECT` | JSON object (map) | | `4` | `SCALAR` | \* `BOOLEAN`
\* `LONG`
\* `DOUBLE`
\* `STRING` | ### Example To pretty-print only JSON objects (maps) in a given JSON column: ```sql SQL theme={null} SELECT JSON_PRETTY(j) FROM example.js WHERE j IS JSON OBJECT ``` ```python Python theme={null} t = gpudb.GPUdbTable(name = 'example.js', db = kinetica) records = t.get_records_by_column( ["JSON_PRETTY(j) AS j"], options = {"expression": "IS_JSON(j, 3)"} ) ``` ## 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 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`. #### Examples

JSON\_ARRAYAGG

```Input theme={null} +------+---------+---------------------------------+ | id | name | j | +------+---------+---------------------------------+ | 1 | Joe | {} | | 2 | Jane | {"A":"B"} | | 3 | Jeff | [1,2,3] | | 4 | Joyce | {"A":2,"C":{"D":5,"F":[7,8]}} | +------+---------+---------------------------------+ ``` ```sql JSON Array Aggregation Query theme={null} SELECT JSON_ARRAYAGG(id) AS id_list, JSON_ARRAYAGG(name) AS name_list, JSON_ARRAYAGG(j) AS json_list FROM example.js_arr_agg ``` ```Output theme={null} +-------------+---------------------------------+--------------------------------------------------------+ | id_list | name_list | json_list | +-------------+---------------------------------+--------------------------------------------------------+ | [2,1,3,4] | ["Jane","Joe","Jeff","Joyce"] | [{"A":"B"},{},[1,2,3],{"A":2,"C":{"D":5,"F":[7,8]}}] | +-------------+---------------------------------+--------------------------------------------------------+ ```

JSON\_ARRAYAGG\_DISTINCT

```Input theme={null} +------+---------+---------------------------------+ | id | name | j | +------+---------+---------------------------------+ | 1 | Joe | {} | | 1 | Joyce | [1,2,3] | | 2 | Jane | {"A":"B"} | | 2 | Jeff | {"A":2,"C":{"D":5,"F":[7,8]}} | | 3 | Jane | {} | | 3 | Jeff | [1,2,3] | | 4 | Joe | {"A":"B"} | | 4 | Joyce | {"A":2,"C":{"D":5,"F":[7,8]}} | +------+---------+---------------------------------+ ``` ```sql Distinct JSON Array Aggregation Query theme={null} SELECT JSON_ARRAYAGG_DISTINCT(id) AS id_list, JSON_ARRAYAGG_DISTINCT(name) AS name_list, JSON_ARRAYAGG_DISTINCT(j) AS json_list FROM example.js_arr_agg ``` ```Output theme={null} +-------------+---------------------------------+--------------------------------------------------------+ | id_list | name_list | json_list | +-------------+---------------------------------+--------------------------------------------------------+ | [1,2,3,4] | ["Jane","Jeff","Joe","Joyce"] | [[1,2,3],{},{"A":"B"},{"A":2,"C":{"D":5,"F":[7,8]}}] | +-------------+---------------------------------+--------------------------------------------------------+ ``` ### UNNEST\_JSON\_ARRAY The `UNNEST_JSON_ARRAY` function transposes array values into columnar data. The two forms of the `UNNEST_JSON_ARRAY` function follow, one for cases where only one column needs to be unnested and one for cases where more than one column needs to be unnested. See [examples](/content/concepts/json#json-func-unnest-array-ex). ```sql title="UNNEST_JSON_ARRAY for 1 Column Syntax" theme={null} SELECT [, ordinality] FROM UNNEST_JSON_ARRAY(SELECT FROM ) [WITH ORDINALITY] [] ``` Column alias to use for the resulting unnested data column; if no alias specified, use `*` to select the column Name of the table containing the JSON array column Name of the JSON array column to unnest Also return the 0-based index in the source JSON arrays for each transposed array value; the index value will be returned as the column alias `ordinality` ```sql title="UNNEST_JSON_ARRAY for Multiple Columns Syntax" theme={null} SELECT [, ] FROM
, UNNEST_JSON_ARRAY() [WITH ORDINALITY]
``` Column aliases given to the resulting unnested data columns, specified in `
` Column alias given to the resulting ordinality column in `
`, if `WITH ORDINALITY` is specified Name of the table containing the JSON array column(s) Alias to use for the table containing the JSON array column(s) Comma-separated list of JSON array columns to unnest Alias to use for the virtual table and columns being used to return the unnested data (including any ordinality alias, if specified), using the form: ```
( [,]) ``` For example, to return two columns of unnested JSON array data as `uai` & `uav`, respectively, and the corresponding ordinality index as `index`, all within the virtual table `unn` use: ``` unn(uai, uav, index) ``` Also return the 0-based index in the source JSON arrays for each transposed array value; the index value will be returned in an alias specified in `
` #### Examples The following examples use this data set as a baseline: ``` +-----------+-----------------+ | ai | av | +-----------+-----------------+ | [1,2,3] | ["A","B","C"] | | [4,5,6] | ["D","E","F"] | | [7,8,9] | ["D","E","F"] | | [1,2,3] | ["G","H","I"] | +-----------+-----------------+ ``` To turn the string arrays in the `av` JSON column in the `example.js_unn_arr` table into a column of values aliased as `uav`: ```sql theme={null} SELECT uav FROM UNNEST_JSON_ARRAY(SELECT av FROM example.js_unn_arr) uav ``` ``` +-------+ | uav | +-------+ | A | | B | | C | | D | | D | | E | | E | | F | | F | | G | | H | | I | +-------+ ``` To turn the integer arrays in the `ai` JSON column in the `example.js_unn_arr` table into a column of values aliased as `uai` and the corresponding index in the original json for each as a column of indices named `ordinality`: ```sql theme={null} SELECT uai, ordinality FROM UNNEST_JSON_ARRAY(SELECT ai FROM example.js_unn_arr) WITH ORDINALITY uai ``` ``` +-------+--------------+ | uai | ordinality | +-------+--------------+ | 1 | 0 | | 1 | 0 | | 2 | 1 | | 2 | 1 | | 3 | 2 | | 3 | 2 | | 4 | 0 | | 5 | 1 | | 6 | 2 | | 7 | 0 | | 8 | 1 | | 9 | 2 | +-------+--------------+ ``` To turn the integer & string arrays in the `ai` & `av` JSON columns in the `example.js_unn_arr` table into two columns of values aliased as `uai` & `uav` in a virtual table with alias `t`, respectively: ```sql theme={null} SELECT uai, uav FROM example.js_unn_arr jua, UNNEST_JSON_ARRAY(jua.ai, jua.av) t(uai, uav) ``` ``` +-------+-------+ | uai | uav | +-------+-------+ | 1 | A | | 1 | G | | 2 | B | | 2 | H | | 3 | C | | 3 | I | | 4 | D | | 5 | E | | 6 | F | | 7 | D | | 8 | E | | 9 | F | +-------+-------+ ``` To turn the integer & string arrays in the `ai` & `av` JSON columns in the `example.js_unn_arr` table into two columns of values aliased as `uai` & `uav` in a virtual table with alias `t`, respectively, and adding the corresponding index in the original array for each as a column of indices aliased as `index`: ```sql theme={null} SELECT uai, uav, index FROM example.js_unn_arr jua, UNNEST_JSON_ARRAY(jua.ai, jua.av) WITH ORDINALITY t(uai, uav, index) ``` ``` +-------+-------+---------+ | uai | uav | index | +-------+-------+---------+ | 1 | A | 0 | | 1 | G | 0 | | 2 | B | 1 | | 2 | H | 1 | | 3 | C | 2 | | 3 | I | 2 | | 4 | D | 0 | | 5 | E | 1 | | 6 | F | 2 | | 7 | D | 0 | | 8 | E | 1 | | 9 | F | 2 | +-------+-------+---------+ ``` To turn the integer arrays in the `ai` JSON column in the `example.js_unn_arr` table into a column of values aliased as `uai`, removing any duplicates across all arrays: ```sql theme={null} SELECT DISTINCT * FROM UNNEST_JSON_ARRAY(SELECT ai FROM example.js_unn_arr) ``` ``` +-------+ | uai | +-------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +-------+ ``` ### UNNEST\_JSON\_OBJECT The `UNNEST_JSON_OBJECT` function transposes JSON objects (maps) into columnar data. Each source object is split out into two columns: a string column containing the top-level object keys, and a JSON column containing the value objects for those keys. The two forms of the `UNNEST_JSON_OBJECT` function follow, one for cases where only one column needs to be unnested and one for cases where more than one column needs to be unnested. See [examples](/content/concepts/json#json-func-unnest-object-ex). ```sql title="UNNEST_JSON_OBJECT for 1 Column Syntax" theme={null} SELECT [, ordinality] FROM UNNEST_JSON_OBJECT(SELECT FROM
) [WITH ORDINALITY] [
] ``` Column aliases given to the resulting unnested key & value data columns specified in `
`; if no aliases are specified, use `*` to select the columns Name of the table containing the JSON object (map) column Name of the JSON object (map) column to unnest Alias to use for the virtual table and key/value columns being used to return the unnested data (including any ordinality alias, if specified), using the form: ```
(, [,]) ``` For example, to return a column as unnested JSON map data, referenced as the key/value pairs `j_k` & `j_v`, respectively, with the corresponding ordinality index as `index`, all within the virtual table `unn`, use: ``` unn(j_k, j_v, index) ``` If no aliases are given, the two key/value columns will be returned as `KEY_0` & `VALUE_0`, respectively. Also return the 0-based index in the source maps for each transposed map value; the index value will be returned as the column alias `ordinality` unless overridden in `
` ```sql title="UNNEST_JSON_OBJECT for Multiple Columns Syntax" theme={null} SELECT [, ] FROM
, UNNEST_JSON_OBJECT() [WITH ORDINALITY]
``` Column aliases given to the resulting unnested key & value data columns specified in `
` Column alias given to the resulting ordinality column in `
`, if `WITH ORDINALITY` is specified Name of the table containing the JSON object (map) column(s) Alias to use for the table containing the JSON object (map) column(s) Comma-separated list of JSON object (map) columns to unnest Alias to use for the virtual table and columns being used to return the unnested data (including any ordinality alias, if specified), using the form: ```
(, [,...] [,]) ``` For example, to return two JSON map data columns as unnested, referenced as the key/value pairs `j1_k` & `j1_v` and `j2_k` & `j2_v`, respectively, with the corresponding ordinality index as `index`, all within the virtual table `unn`, use: ``` unn(j1_k, j1_v, j2_k, j2_v, index) ``` Also return the 0-based index in the source maps for each transposed map value; the index value will be returned in an alias specified in `
` #### Examples The following examples use this data set as a baseline: ``` +-----------------------------+------------------------------------------+ | j1 | j2 | +-----------------------------+------------------------------------------+ | {"A":"B","C":4,"E":5} | {"A":{"B":"C"},"D":5,"F":[7,8,9]} | | {"A":2,"C":"D","E":"F"} | {"A":[2,3],"D":"E","F":{"G":"H"}} | | {"G":"H","I":10,"K":"12"} | {"I":{"J":"K"},"L":[13,14,15],"P":"Q"} | | {"A":"B","C":4,"E":5} | {"A":{"B":"C"},"D":5,"F":[7,8,9]} | +-----------------------------+------------------------------------------+ ``` To turn the JSON maps in the `j1` JSON column in the `example.js_unn_obj` table into key/value pair columns of values aliased as `j1_k` & `j1_v`, respectively: ```sql theme={null} SELECT j1_k, j1_v FROM UNNEST_JSON_OBJECT(SELECT j1 FROM example.js_unn_obj) t(j1_k, j1_v) ``` ``` +--------+--------+ | j1_k | j1_v | +--------+--------+ | A | "B" | | A | "B" | | A | 2 | | C | "D" | | C | 4 | | C | 4 | | E | "F" | | E | 5 | | E | 5 | | G | "H" | | I | 10 | | K | "12" | +--------+--------+ ``` To turn the JSON maps in the `j2` JSON column in the `example.js_unn_obj` table into key/value pair columns of values aliased as `j2_k` & `j2_v`, and the corresponding index in the original map for each as a column of indices aliased as `index`: ```sql theme={null} SELECT j2_k, j2_v, index FROM UNNEST_JSON_OBJECT(SELECT j2 FROM example.js_unn_obj) WITH ORDINALITY t(j2_k, j2_v, index) ``` ``` +--------+--------------+---------+ | j2_k | j2_v | index | +--------+--------------+---------+ | A | [2,3] | 0 | | A | {"B":"C"} | 0 | | A | {"B":"C"} | 0 | | D | "E" | 1 | | D | 5 | 1 | | D | 5 | 1 | | F | [7,8,9] | 2 | | F | [7,8,9] | 2 | | F | {"G":"H"} | 2 | | I | {"J":"K"} | 0 | | L | [13,14,15] | 1 | | P | "Q" | 2 | +--------+--------------+---------+ ``` To turn the JSON maps in the `j1` & `j2` JSON columns in the `example.js_unn_obj` table into two sets of key/value pair columns of values aliased as `j1_k` & `j1_v` and `j2_k` & `j2_v`, respectively, in a virtual table with alias `t`: ```sql theme={null} SELECT j1_k, j1_v, j2_k, j2_v FROM example.js_unn_obj juo, UNNEST_JSON_OBJECT(juo.j1, juo.j2) t(j1_k, j1_v, j2_k, j2_v) ``` ``` +--------+--------+--------+--------------+ | j1_k | j1_v | j2_k | j2_v | +--------+--------+--------+--------------+ | A | "B" | A | {"B":"C"} | | A | "B" | A | {"B":"C"} | | A | 2 | A | [2,3] | | C | "D" | D | "E" | | C | 4 | D | 5 | | C | 4 | D | 5 | | E | "F" | F | {"G":"H"} | | E | 5 | F | [7,8,9] | | E | 5 | F | [7,8,9] | | G | "H" | I | {"J":"K"} | | I | 10 | L | [13,14,15] | | K | "12" | P | "Q" | +--------+--------+--------+--------------+ ``` To turn the JSON maps in the `j1` & `j2` JSON columns in the `example.js_unn_obj` table into two sets of key/value pair columns of values aliased as `j1_k` & `j1_v` and `j2_k` & `j2_v`, respectively, in a virtual table with alias `t`, and adding the corresponding index in the original map for each as a column of indices aliased as `index`: ```sql theme={null} SELECT j1_k, j1_v, j2_k, j2_v, index FROM example.js_unn_obj juo, UNNEST_JSON_OBJECT(juo.j1, juo.j2) WITH ORDINALITY t(j1_k, j1_v, j2_k, j2_v, index) ``` ``` +--------+--------+--------+--------------+---------+ | j1_k | j1_v | j2_k | j2_v | index | +--------+--------+--------+--------------+---------+ | A | "B" | A | {"B":"C"} | 0 | | A | "B" | A | {"B":"C"} | 0 | | A | 2 | A | [2,3] | 0 | | C | "D" | D | "E" | 1 | | C | 4 | D | 5 | 1 | | C | 4 | D | 5 | 1 | | E | "F" | F | {"G":"H"} | 2 | | E | 5 | F | [7,8,9] | 2 | | E | 5 | F | [7,8,9] | 2 | | G | "H" | I | {"J":"K"} | 0 | | I | 10 | L | [13,14,15] | 1 | | K | "12" | P | "Q" | 2 | +--------+--------+--------+--------------+---------+ ```