JSON functions

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

Categories

The JSON functions are grouped into the following categories based on their behavior:

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(
  '{"class": {"students": [{"name": "Jane"}]}}',
  '$') AS json_text_string;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[{"name":"Jane"}]}}                  |
 *-----------------------------------------------------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": []}}',
  '$') AS json_text_string;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[]}}                                 |
 *-----------------------------------------------------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
  '$') AS json_text_string;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
 *-----------------------------------------------------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "Jane"}]}}',
  '$.class.students[0]') AS first_student;

/*-----------------*
 | first_student   |
 +-----------------+
 | {"name":"Jane"} |
 *-----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": []}}',
  '$.class.students[0]') AS first_student;

/*-----------------*
 | first_student   |
 +-----------------+
 | NULL            |
 *-----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
  '$.class.students[0]') AS first_student;

/*-----------------*
 | first_student   |
 +-----------------+
 | {"name":"John"} |
 *-----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "Jane"}]}}',
  '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 *----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": []}}',
  '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 *----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "John"}, {"name": null}]}}',
  '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 *----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
  '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | "Jamie"        |
 *----------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "Jane"}]}}',
  "$.class['students']") AS student_names;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | [{"name":"Jane"}]                  |
 *------------------------------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": []}}',
  "$.class['students']") AS student_names;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | []                                 |
 *------------------------------------*/
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
  "$.class['students']") AS student_names;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | [{"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('{"class": {"students": [{"name": "Jane"}]}}', '$') AS json_text_string;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[{"name":"Jane"}]}}                  |
 *-----------------------------------------------------------*/
SELECT JSON_QUERY('{"class": {"students": []}}', '$') AS json_text_string;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[]}}                                 |
 *-----------------------------------------------------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "John"},{"name": "Jamie"}]}}',
    '$') AS json_text_string;

/*-----------------------------------------------------------*
 | json_text_string                                          |
 +-----------------------------------------------------------+
 | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
 *-----------------------------------------------------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "Jane"}]}}',
    '$.class.students[0]') AS first_student;

/*-----------------*
 | first_student   |
 +-----------------+
 | {"name":"Jane"} |
 *-----------------*/
SELECT
  JSON_QUERY('{"class": {"students": []}}', '$.class.students[0]') AS first_student;

/*-----------------*
 | first_student   |
 +-----------------+
 | NULL            |
 *-----------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
    '$.class.students[0]') AS first_student;

/*-----------------*
 | first_student   |
 +-----------------+
 | {"name":"John"} |
 *-----------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "Jane"}]}}',
    '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 *----------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": []}}',
    '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 *----------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "John"}, {"name": null}]}}',
    '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | NULL           |
 *----------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
    '$.class.students[1].name') AS second_student;

/*----------------*
 | second_student |
 +----------------+
 | "Jamie"        |
 *----------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "Jane"}]}}',
    '$.class."students"') AS student_names;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | [{"name":"Jane"}]                  |
 *------------------------------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": []}}',
    '$.class."students"') AS student_names;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | []                                 |
 *------------------------------------*/
SELECT
  JSON_QUERY(
    '{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
    '$.class."students"') AS student_names;

/*------------------------------------*
 | student_names                      |
 +------------------------------------+
 | [{"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 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"] |
 *---------------------------------*/

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

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 [-253, 253], 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"