JSON functions

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

Category	Functions	Description
Standard extractors	`JSON_QUERY` `JSON_VALUE` `JSON_QUERY_ARRAY`	Functions that extract JSON data.
Legacy extractors	`JSON_EXTRACT` `JSON_EXTRACT_SCALAR`	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.

Function list

Name	Summary
`JSON_EXTRACT`	(Deprecated) Extracts a JSON value and converts it to a SQL JSON-formatted `STRING` value.
`JSON_EXTRACT_SCALAR`	(Deprecated) Extracts a JSON scalar value and converts it to a SQL `STRING` value.
`JSON_QUERY`	Extracts a JSON value and converts it to a SQL JSON-formatted `STRING` value.
`JSON_QUERY_ARRAY`	Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` value.
`JSON_VALUE`	Extracts a JSON scalar value and converts it to a SQL `STRING` value.
`TO_JSON_STRING`	Converts a SQL value to a JSON-formatted `STRING` value.

`JSON_EXTRACT`

JSON_EXTRACT(json_string_expr, json_path)

Description

Extracts a JSON value and converts it to a SQL 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:
```
'{"class": {"students": [{"name": "Jane"}]}}'
```
Extracts a SQL NULL when a JSON-formatted string null is encountered. For example:
```
SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input.

Return type

A JSON-formatted STRING

Examples

In the following examples, JSON data is extracted and returned as JSON-formatted strings.

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[{"name":"Jane"}]}}                  |
 | {"class":{"students":[]}}                                 |
 | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
 *-----------------------------------------------------------*/

SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*-----------------*
 | first_student   |
 +-----------------+
 | {"name":"Jane"} |
 | NULL            |
 | {"name":"John"} |
 *-----------------*/

SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": null}]}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 | NULL           |
 | NULL           |
 | "Jamie"        |
 *----------------*/

SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | [{"name":"Jane"}]                  |
 | []                                 |
 | [{"name":"John"},{"name":"Jamie"}] |
 *------------------------------------*/

SELECT JSON_EXTRACT('{"a": null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a": null}', "$.b"); -- Returns a SQL NULL

`JSON_EXTRACT_SCALAR`

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

Description

Extracts a JSON scalar value and converts it to a SQL STRING value. In addition, this function:

Removes the outermost quotes and unescapes the return values.
Returns a SQL NULL if a non-scalar value is selected.
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:
```
'{"name": "Jane", "age": "6"}'
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

If json_path returns a JSON null or a non-scalar value (in other words, if json_path refers to an object or an array), then a SQL NULL is returned.

Return type

STRING

Examples

The following example compares how results are returned for the JSON_EXTRACT and JSON_EXTRACT_SCALAR functions.

SELECT JSON_EXTRACT('{"name": "Jakob", "age": "6" }', '$.name') AS json_name,
  JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }', '$.name') AS scalar_name,
  JSON_EXTRACT('{"name": "Jakob", "age": "6" }', '$.age') AS json_age,
  JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }', '$.age') AS scalar_age;

/*-----------+-------------+----------+------------*
 | json_name | scalar_name | json_age | scalar_age |
 +-----------+-------------+----------+------------+
 | "Jakob"   | Jakob       | "6"      | 6          |
 *-----------+-------------+----------+------------*/

SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

/*--------------------+---------------------*
 | json_extract       | json_extract_scalar |
 +--------------------+---------------------+
 | ["apple","banana"] | NULL                |
 *--------------------+---------------------*/

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_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello;

/*-------*
 | hello |
 +-------+
 | world |
 *-------*/

`JSON_QUERY`

JSON_QUERY(json_string_expr, json_path)

Description

Extracts a JSON value and converts it to a SQL JSON-formatted STRING value. 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:
```
'{"class": {"students": [{"name": "Jane"}]}}'
```
Extracts a SQL NULL when a JSON-formatted string null is encountered. For example:
```
SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input.

Return type

A JSON-formatted STRING

Examples

In the following examples, JSON data is extracted and returned as JSON-formatted strings.

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[{"name":"Jane"}]}}                  |
 | {"class":{"students":[]}}                                 |
 | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
 *-----------------------------------------------------------*/

SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*-----------------*
 | first_student   |
 +-----------------+
 | {"name":"Jane"} |
 | NULL            |
 | {"name":"John"} |
 *-----------------*/

SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": null}]}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 | NULL           |
 | NULL           |
 | "Jamie"        |
 *----------------*/

SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}]}}',
  '{"class": {"students": []}}',
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | [{"name":"Jane"}]                  |
 | []                                 |
 | [{"name":"John"},{"name":"Jamie"}] |
 *------------------------------------*/

SELECT JSON_QUERY('{"a": null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a": null}', "$.b"); -- Returns a SQL NULL

`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 is not 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 does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a": "foo"}', '$.a') AS result;

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

-- If a key that does not 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_VALUE`

JSON_VALUE(json_string_expr[, json_path])

Description

Extracts a JSON scalar value and converts it to a SQL STRING value. In addition, this function:

Removes the outermost quotes and unescapes the values.
Returns a SQL NULL if a non-scalar value is selected.
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:
```
'{"name": "Jakob", "age": "6"}'
```
json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

If json_path returns a JSON null or a non-scalar value (in other words, if json_path refers to an object or an array), then a SQL NULL is returned.

Return type

STRING

Examples

The following example compares how results are returned for the JSON_QUERY and JSON_VALUE functions.

SELECT JSON_QUERY('{"name": "Jakob", "age": "6"}', '$.name') AS json_name,
  JSON_VALUE('{"name": "Jakob", "age": "6"}', '$.name') AS scalar_name,
  JSON_QUERY('{"name": "Jakob", "age": "6"}', '$.age') AS json_age,
  JSON_VALUE('{"name": "Jakob", "age": "6"}', '$.age') AS scalar_age;

/*-----------+-------------+----------+------------*
 | json_name | scalar_name | json_age | scalar_age |
 +-----------+-------------+----------+------------+
 | "Jakob"   | Jakob       | "6"      | 6          |
 *-----------+-------------+----------+------------*/

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

/*--------------------+------------*
 | json_query         | json_value |
 +--------------------+------------+
 | ["apple","banana"] | NULL       |
 *--------------------+------------*/

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

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

/*-------*
 | hello |
 +-------+
 | world |
 *-------*/

`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 here.
pretty_print: Optional boolean parameter. If pretty_print is true, the `returned value is formatted for easy readability.

Return type

A JSON-formatted STRING

Examples

Convert rows in a table to JSON-formatted strings.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10, 20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30, 40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50, 60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable AS t;

/*----+-------------+--------------------------------*
 | id | coordinates | json_data                      |
 +----+-------------+--------------------------------+
 | 1  | [10, 20]    | {"id":1,"coordinates":[10,20]} |
 | 2  | [30, 40]    | {"id":2,"coordinates":[30,40]} |
 | 3  | [50, 60]    | {"id":3,"coordinates":[50,60]} |
 *----+-------------+--------------------------------*/

Convert rows in a table to JSON-formatted strings that are easy to read.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10, 20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30, 40] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable AS t;

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

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"`
FLOAT32 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"`
ENUM	string Invalid enum values are encoded as their number, such as 0 or 42.	SQL input: `Color.Red` JSON output: `"Red"`
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"`
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 does not match a value in a JSON-formatted string. If the selected value for a scalar function is not 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
`$`	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"}`
`[][]` `[][][]...`	Child subscript operator. If the object is a JSON array within an array, you can use as many additional brackets as you need to specify the child array index.	JSON-formatted string: `'{"a": [["b", "c"], "d"], "e":"f"}'` JSON path: `"$.a[0][1]"` JSON result: `"c"`

JSON functions

Categories

Function list

JSON_EXTRACT

JSON_EXTRACT_SCALAR

JSON_QUERY

JSON_QUERY_ARRAY

JSON_VALUE

TO_JSON_STRING

JSON encodings

JSONPath format

Operators for JSONPath

`JSON_EXTRACT`

`JSON_EXTRACT_SCALAR`

`JSON_QUERY`

`JSON_QUERY_ARRAY`

`JSON_VALUE`

`TO_JSON_STRING`