We've reorganized our documentation navigation structure to align directly with your operational workflows. See the release notes and the walkthrough video for more information.

JSON functions

GoogleSQL for SecOps supports the following functions, which can retrieve and transform JSON data.

Category	Functions	Description
Standard extractors	`JSON_QUERY_ARRAY` `JSON_VALUE_ARRAY`	Functions that extract JSON data.
Legacy extractors	`JSON_EXTRACT_ARRAY` `JSON_EXTRACT_STRING_ARRAY`	Functions that extract JSON data. While these functions are supported by GoogleSQL, we recommend using the standard extractor functions.
Other converters	`TO_JSON_STRING`	Other conversion functions from or to JSON.
Accessors	`JSON_TYPE`	Functions that provide access to JSON properties.
Transformers	`JSON_FLATTEN`	Functions that apply a transformation to a JSON value.

Function list

Name	Summary
`JSON_EXTRACT_ARRAY`	(Deprecated) Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` value.
`JSON_EXTRACT_STRING_ARRAY`	(Deprecated) Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value.
`JSON_FLATTEN`	Produces a new SQL `ARRAY<JSON>` value containing all non-array values that are either directly in the input JSON value or children of one or more consecutively nested arrays in the input JSON value.
`JSON_QUERY_ARRAY`	Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` value.
`JSON_TYPE`	Gets the JSON type of the outermost JSON value and converts the name of this type to a SQL `STRING` value.
`JSON_VALUE_ARRAY`	Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value.
`TO_JSON_STRING`	Converts a SQL value to a JSON-formatted `STRING` value.

`JSON_EXTRACT_ARRAY`

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

Description

Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> value. This function uses single quotes and brackets to escape invalid JSONPath characters in JSON keys. For example: ['a.b'].

Arguments:

json_string_expr: A JSON-formatted string. For example:
```
'["a", "b", {"key": "c"}]'
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter isn't provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Return type

ARRAY<JSON-formatted STRING>

Examples

This extracts the items in a JSON-formatted string to a string array:

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array;

/*--------------+
 | string_array |
 +--------------+
 | [1, 2, 3]    |
 +--------------*/

This extracts a string array and converts it to an integer array:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

/*---------------+
 | integer_array |
 +---------------+
 | [1, 2, 3]     |
 +---------------*/

This extracts string values in a JSON-formatted string to an array:

-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples", "oranges", "grapes"]', '$') AS string_array;

/*---------------------------------+
 | string_array                    |
 +---------------------------------+
 | ["apples", "oranges", "grapes"] |
 +---------------------------------*/

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

/*---------------------------+
 | string_array              |
 +---------------------------+
 | [apples, oranges, grapes] |
 +---------------------------*/

This extracts only the items in the fruit property to an array:

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit": [{"apples": 5, "oranges": 10}, {"apples": 2, "oranges": 4}], "vegetables": [{"lettuce": 7, "kale": 8}]}',
  '$.fruit'
) AS string_array;

/*-------------------------------------------------------+
 | string_array                                          |
 +-------------------------------------------------------+
 | [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
 +-------------------------------------------------------*/

These are equivalent:

SELECT JSON_EXTRACT_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$[fruits]') AS string_array;

SELECT JSON_EXTRACT_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$.fruits') AS string_array;

-- The queries above produce the following result:
/*---------------------------------+
 | string_array                    |
 +---------------------------------+
 | ["apples", "oranges", "grapes"] |
 +---------------------------------*/

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets, [' ']. For example:

SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

/*-----------+
 | hello     |
 +-----------+
 | ["world"] |
 +-----------*/

The following examples explore how invalid requests and empty arrays are handled:

If a JSONPath is invalid, an error is thrown.
If a JSON-formatted string is invalid, the output is NULL.
It's okay to have empty arrays in the JSON-formatted string.

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo", "bar", "baz"]', 'INVALID_JSONPath') AS result;

-- If the JSONPath doesn't refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a": "foo"}', '$.a') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a key that doesn't exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a": "foo"}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a": "foo", "b": []}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | []     |
 +--------*/

`JSON_EXTRACT_STRING_ARRAY`

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

Description

Extracts a JSON array of scalar values and converts it to a SQL ARRAY<STRING> value. In addition, this function:

Removes the outermost quotes and unescapes the values.
Returns a SQL NULL if the selected value isn't an array or not an array containing only scalar values.
Uses single quotes and brackets to escape invalid JSONPath characters in JSON keys. For example: ['a.b'].

Arguments:

json_string_expr: A JSON-formatted string. For example:
```
'["apples", "oranges", "grapes"]'
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter isn't provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Caveats:

A JSON null in the input array produces a SQL NULL as the output for that JSON null. If the output contains a NULL array element, an error is produced because the final output can't be an array with NULL values.
If a JSONPath matches an array that contains scalar objects and a JSON null, then the output of the function must be transformed because the final output can't be an array with NULL values.

Return type

ARRAY<STRING>

Examples

The following example compares how results are returned for the JSON_EXTRACT_ARRAY and JSON_EXTRACT_STRING_ARRAY functions.

SELECT JSON_EXTRACT_ARRAY('["apples", "oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples", "oranges"]') AS string_array;

/*-----------------------+-------------------+
 | json_array            | string_array      |
 +-----------------------+-------------------+
 | ["apples", "oranges"] | [apples, oranges] |
 +-----------------------+-------------------*/

This extracts the items in a JSON-formatted string to a string array:

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo", "bar", "baz"]', '$') AS string_array;

/*-----------------+
 | string_array    |
 +-----------------+
 | [foo, bar, baz] |
 +-----------------*/

This extracts a string array and converts it to an integer array:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_STRING_ARRAY('[1, 2, 3]', '$')
  ) AS integer_element
) AS integer_array;

/*---------------+
 | integer_array |
 +---------------+
 | [1, 2, 3]     |
 +---------------*/

These are equivalent:

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$.fruits') AS string_array;

-- The queries above produce the following result:
/*---------------------------+
 | string_array              |
 +---------------------------+
 | [apples, oranges, grapes] |
 +---------------------------*/

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets: [' ']. For example:

SELECT JSON_EXTRACT_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

/*---------+
 | hello   |
 +---------+
 | [world] |
 +---------*/

The following examples explore how invalid requests and empty arrays are handled:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo", "bar", "baz"]', 'INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}', '$') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL, '$') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath doesn't match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a": ["foo", "bar", "baz"]}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an object that isn't an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a": "foo"}', '$') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a": [{"b": "foo", "c": 1}, {"b": "bar", "c":2}], "d": "baz"}', '$.a') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a": [10, {"b": 20}]', '$.a') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a": "foo", "b": []}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | []     |
 +--------*/

-- The following query produces and error because the final output can't be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

`JSON_FLATTEN`

JSON_FLATTEN(json_expr)

Description

Produces a new SQL ARRAY<JSON> value containing all non-array values that are either directly in the input JSON value or children of one or more consecutively nested arrays in the input JSON value.

Arguments:

json_expr: JSON. For example:
```
JSON '["Jane", ["John", "Jamie"]]'
```

Details:

If json_expr is SQL NULL, the function returns SQL NULL.

Return type

ARRAY<JSON>

Examples

In the following example, there is a single non-array value that is returned.

SELECT JSON_FLATTEN(JSON '1') AS json_flatten

/*--------------+
 | json_flatten |
 +--------------+
 | [1]          |
 +--------------*/

In the following example, an input array of values is flattened.

SELECT JSON_FLATTEN(JSON '[1, 2, null]') AS json_flatten

/*--------------+
 | json_flatten |
 +--------------+
 | [1, 2, null] |
 +--------------*/

In the following example, an input array which includes nested array elements is flattened.

SELECT JSON_FLATTEN(JSON '[[[1]], 2, [3]]') AS json_flatten

/*--------------+
 | json_flatten |
 +--------------+
 | [1, 2, 3]    |
 +--------------*/

In the following example, the nested-array value in a key-value pair is not flattened because it is enclosed within a JSON object.

SELECT JSON_FLATTEN(JSON '{"a": [[1]]}') AS json_flatten

/*---------------+
 | json_flatten  |
 +---------------+
 | [{"a":[[1]]}] |
 +---------------*/

In the following example, the output contains both the flattened array elements from the input and the non-array elements from the input.

SELECT JSON_FLATTEN(JSON '[[[1, 2], 3], {"a": 4}, true]') AS json_flatten

/*---------------------------+
 | json_flatten              |
 +---------------------------+
 | [1, 2, 3, {"a": 4}, true] |
 +---------------------------*/

`JSON_QUERY_ARRAY`

JSON_QUERY_ARRAY(json_string_expr[, json_path])

Description

Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> value. In addition, this function uses double quotes to escape invalid JSONPath characters in JSON keys. For example: "a.b".

Arguments:

json_string_expr: A JSON-formatted string. For example:
```
'["a", "b", {"key": "c"}]'
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter isn't provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Return type

ARRAY<JSON-formatted STRING>

Examples

This extracts the items in a JSON-formatted string to a string array:

SELECT JSON_QUERY_ARRAY('[1, 2, 3]') AS string_array;

/*--------------+
 | string_array |
 +--------------+
 | [1, 2, 3]    |
 +--------------*/

This extracts a string array and converts it to an integer array:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_QUERY_ARRAY('[1, 2, 3]','$')
  ) AS integer_element
) AS integer_array;

/*---------------+
 | integer_array |
 +---------------+
 | [1, 2, 3]     |
 +---------------*/

This extracts string values in a JSON-formatted string to an array:

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples", "oranges", "grapes"]', '$') AS string_array;

/*---------------------------------+
 | string_array                    |
 +---------------------------------+
 | ["apples", "oranges", "grapes"] |
 +---------------------------------*/

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples", "oranges", "grapes"]', '$')) AS string_element
) AS string_array;

/*---------------------------+
 | string_array              |
 +---------------------------+
 | [apples, oranges, grapes] |
 +---------------------------*/

This extracts only the items in the fruit property to an array:

SELECT JSON_QUERY_ARRAY(
  '{"fruit": [{"apples": 5, "oranges": 10}, {"apples": 2, "oranges": 4}], "vegetables": [{"lettuce": 7, "kale": 8}]}',
  '$.fruit'
) AS string_array;

/*-------------------------------------------------------+
 | string_array                                          |
 +-------------------------------------------------------+
 | [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
 +-------------------------------------------------------*/

These are equivalent:

SELECT JSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$."fruits"') AS string_array;

-- The queries above produce the following result:
/*---------------------------------+
 | string_array                    |
 +---------------------------------+
 | ["apples", "oranges", "grapes"] |
 +---------------------------------*/

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes: " ". For example:

SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

/*-----------+
 | hello     |
 +-----------+
 | ["world"] |
 +-----------*/

The following examples show how invalid requests and empty arrays are handled:

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo", "bar", "baz"]', 'INVALID_JSONPath') AS result;

-- If the JSONPath doesn't refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a": "foo"}', '$.a') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a key that doesn't exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a": "foo"}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a": "foo", "b": []}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | []     |
 +--------*/

`JSON_TYPE`

JSON_TYPE(json_expr)

Description

Gets the JSON type of the outermost JSON value and converts the name of this type to a SQL STRING value. The names of these JSON types can be returned: object, array, string, number, boolean, null

Arguments:

json_expr: JSON. For example:
```
JSON '{"name": "sky", "color": "blue"}'
```
If this expression is SQL NULL, the function returns SQL NULL. If the extracted JSON value isn't a valid JSON type, an error is produced.

Return type

STRING

Examples

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

/*----------------------------------+---------+
 | json_val                         | type    |
 +----------------------------------+---------+
 | "apple"                          | string  |
 | 10                               | number  |
 | 3.14                             | number  |
 | null                             | null    |
 | {"State":"NY","city":"New York"} | object  |
 | ["apple","banana"]               | array   |
 | false                            | boolean |
 +----------------------------------+---------*/

`JSON_VALUE_ARRAY`

JSON_VALUE_ARRAY(json_string_expr[, json_path])

Description

Extracts a JSON array of scalar values and converts it to a SQL ARRAY<STRING> value. In addition, this function:

Removes the outermost quotes and unescapes the values.
Returns a SQL NULL if the selected value isn't an array or not an array containing only scalar values.
Uses double quotes to escape invalid JSONPath characters in JSON keys. For example: "a.b".

Arguments:

json_string_expr: A JSON-formatted string. For example:
```
'["apples", "oranges", "grapes"]'
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter isn't provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Caveats:

A JSON null in the input array produces a SQL NULL as the output for JSON null. If the output contains a NULL array element, an error is produced because the final output can't be an array with NULL values.
If a JSONPath matches an array that contains scalar objects and a JSON null, then the output of the function must be transformed because the final output can't be an array with NULL values.

Return type

ARRAY<STRING>

Examples

The following example compares how results are returned for the JSON_QUERY_ARRAY and JSON_VALUE_ARRAY functions.

SELECT JSON_QUERY_ARRAY('["apples", "oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples", "oranges"]') AS string_array;

/*-----------------------+-------------------+
 | json_array            | string_array      |
 +-----------------------+-------------------+
 | ["apples", "oranges"] | [apples, oranges] |
 +-----------------------+-------------------*/

This extracts the items in a JSON-formatted string to a string array:

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo", "bar", "baz"]', '$') AS string_array;

/*-----------------+
 | string_array    |
 +-----------------+
 | [foo, bar, baz] |
 +-----------------*/

This extracts a string array and converts it to an integer array:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_VALUE_ARRAY('[1, 2, 3]', '$')
  ) AS integer_element
) AS integer_array;

/*---------------+
 | integer_array |
 +---------------+
 | [1, 2, 3]     |
 +---------------*/

These are equivalent:

SELECT JSON_VALUE_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$."fruits"') AS string_array;

-- The queries above produce the following result:
/*---------------------------+
 | string_array              |
 +---------------------------+
 | [apples, oranges, grapes] |
 +---------------------------*/

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes: " ". For example:

SELECT JSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

/*---------+
 | hello   |
 +---------+
 | [world] |
 +---------*/

The following examples explore how invalid requests and empty arrays are handled:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo", "bar", "baz"]', 'INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}', '$') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL, '$') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath doesn't match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a": ["foo", "bar", "baz"]}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an object that isn't an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a": "foo"}', '$') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a": [{"b": "foo", "c": 1}, {"b": "bar", "c": 2}], "d": "baz"}', '$.a') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a": [10, {"b": 20}]', '$.a') AS result;

/*--------+
 | result |
 +--------+
 | NULL   |
 +--------*/

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a": "foo", "b": []}', '$.b') AS result;

/*--------+
 | result |
 +--------+
 | []     |
 +--------*/

-- The following query produces and error because the final output can't be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

`TO_JSON_STRING`

TO_JSON_STRING(value[, pretty_print])

Description

Converts a SQL value to a JSON-formatted STRING value.

Arguments:

value: A SQL value. You can review the GoogleSQL data types that this function supports and their JSON encodings in the JSON encodings section. If the value is NULL, a null string is returned.
pretty_print: Optional boolean parameter. If pretty_print is true, the returned value is formatted for easy readability. If pretty_print is NULL, the function returns NULL, regardless of the value argument.

Return type

A JSON-formatted STRING

Examples

The following query converts a STRUCT value to a JSON-formatted string:

SELECT TO_JSON_STRING(STRUCT(1 AS id, [10,20] AS coordinates)) AS json_data

/*--------------------------------+
 | json_data                      |
 +--------------------------------+
 | {"id":1,"coordinates":[10,20]} |
 +--------------------------------*/

The following query converts a STRUCT value to a JSON-formatted string that is easy to read:

SELECT TO_JSON_STRING(STRUCT(1 AS id, [10,20] AS coordinates), true) AS json_data

/*--------------------+
 | json_data          |
 +--------------------+
 | {                  |
 |   "id": 1,         |
 |   "coordinates": [ |
 |     10,            |
 |     20             |
 |   ]                |
 | }                  |
 +--------------------*/

Supplemental materials

JSON encodings

You can encode a SQL value as a JSON value with the following functions:

TO_JSON_STRING

The following SQL to JSON encodings are supported:

From SQL	To JSON	Examples
NULL	null	SQL input: `NULL` JSON output: `null`
BOOL	boolean	SQL input: `TRUE` JSON output: `true` SQL input: `FALSE` JSON output: `false`
INT64	number or string Encoded as a number when the value is in the range of [-2⁵³, 2⁵³], which is the range of integers that can be represented losslessly as IEEE 754 double-precision floating point numbers. A value outside of this range is encoded as a string.	SQL input: `9007199254740992` JSON output: `9007199254740992` SQL input: `9007199254740993` JSON output: `"9007199254740993"`
NUMERIC	number or string Encoded as a number when the value is in the range of [-2⁵³, 2⁵³] and has no fractional part. A value outside of this range is encoded as a string.	SQL input: `-1` JSON output: `-1` SQL input: `0` JSON output: `0` SQL input: `9007199254740993` JSON output: `"9007199254740993"` SQL input: `123.56` JSON output: `"123.56"`
FLOAT64	number or string `+/-inf` and `NaN` are encoded as `Infinity`, `-Infinity`, and `NaN`. Otherwise, this value is encoded as a number.	SQL input: `1.0` JSON output: `1` SQL input: `9007199254740993` JSON output: `9007199254740993` SQL input: `"+inf"` JSON output: `"Infinity"` SQL input: `"-inf"` JSON output: `"-Infinity"` SQL input: `"NaN"` JSON output: `"NaN"`
STRING	string Encoded as a string, escaped according to the JSON standard. Specifically, `"`, `\,` and the control characters from `U+0000` to `U+001F` are escaped.	SQL input: `"abc"` JSON output: `"abc"` SQL input: `"\"abc\""` JSON output: `"\"abc\""`
BYTES	string Uses RFC 4648 Base64 data encoding.	SQL input: `b"Google"` JSON output: `"R29vZ2xl"`
DATE	string	SQL input: `DATE '2017-03-06'` JSON output: `"2017-03-06"`
TIMESTAMP	string Encoded as ISO 8601 date and time, where T separates the date and time and Z (Zulu/UTC) represents the time zone.	SQL input: `TIMESTAMP '2017-03-06 12:34:56.789012'` JSON output: `"2017-03-06T12:34:56.789012Z"`
DATETIME	string Encoded as ISO 8601 date and time, where T separates the date and time.	SQL input: `DATETIME '2017-03-06 12:34:56.789012'` JSON output: `"2017-03-06T12:34:56.789012"`
TIME	string Encoded as ISO 8601 time.	SQL input: `TIME '12:34:56.789012'` JSON output: `"12:34:56.789012"`
ARRAY	array Can contain zero or more elements.	SQL input: `["red", "blue", "green"]` JSON output: `["red","blue","green"]` SQL input:`[1, 2, 3]` JSON output:`[1,2,3]`
STRUCT	object The object can contain zero or more key-value pairs. Each value is formatted according to its type. For `TO_JSON_STRING`, a field and any duplicates of this field are included in the output string. Anonymous fields are represented with `""`. Invalid UTF-8 field names might result in unparseable JSON. String values are escaped according to the JSON standard. Specifically, `"`, `\,` and the control characters from `U+0000` to `U+001F` are escaped.	SQL input: `STRUCT(12 AS purchases, TRUE AS inStock)` JSON output: `{"inStock": true,"purchases":12}`

JSONPath format

With the JSONPath format, you can identify the values you want to obtain from a JSON-formatted string.

If a key in a JSON functions contains a JSON format operator, refer to each JSON function for how to escape them.

A JSON function returns NULL if the JSONPath format doesn't match a value in a JSON-formatted string. If the selected value for a scalar function isn't scalar, such as an object or an array, the function returns NULL. If the JSONPath format is invalid, an error is produced.

Operators for JSONPath

The JSONPath format supports these operators:

Operator Description Examples

Operator	Description	Examples
`$`	Root object or element. The JSONPath format must start with this operator, which refers to the outermost level of the JSON-formatted string.	JSON-formatted string: `'{"class" : {"students" : [{"name" : "Jane"}]}}'` JSON path: `"$"` JSON result: `{"class":{"students":[{"name":"Jane"}]}}`
`.`	Child operator. You can identify child values using dot-notation.	JSON-formatted string: `'{"class" : {"students" : [{"name" : "Jane"}]}}'` JSON path: `"$.class.students"` JSON result: `[{"name":"Jane"}]`
`[]`	Subscript operator. If the object is a JSON array, you can use brackets to specify the array index.	JSON-formatted string: `'{"class" : {"students" : [{"name" : "Jane"}]}}'` JSON path: `"$.class.students[0]"` JSON result: `{"name":"Jane"}`

$

Root object or element. The JSONPath format must start with this operator, which refers to the outermost level of the JSON-formatted string.

JSON-formatted string:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON path:
"$"

JSON result:
{"class":{"students":[{"name":"Jane"}]}}

.

Child operator. You can identify child values using dot-notation.

JSON-formatted string:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON path:
"$.class.students"

JSON result:
[{"name":"Jane"}]

[]

Subscript operator. If the object is a JSON array, you can use brackets to specify the array index.

JSON-formatted string:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON path:
"$.class.students[0]"

JSON result:
{"name":"Jane"}

JSON functions Stay organized with collections Save and categorize content based on your preferences.

Categories

Function list

JSON_EXTRACT_ARRAY

JSON_EXTRACT_STRING_ARRAY

JSON_FLATTEN

JSON_QUERY_ARRAY

JSON_TYPE

JSON_VALUE_ARRAY

TO_JSON_STRING

Supplemental materials

JSON encodings

JSONPath format

Operators for JSONPath

JSON functions

`JSON_EXTRACT_ARRAY`

`JSON_EXTRACT_STRING_ARRAY`

`JSON_FLATTEN`

`JSON_QUERY_ARRAY`

`JSON_TYPE`

`JSON_VALUE_ARRAY`

`TO_JSON_STRING`