JSON functions in GoogleSQL

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

Category	Functions	Description
Extractors	`JSON_QUERY` `JSON_VALUE` `JSON_QUERY_ARRAY` `JSON_VALUE_ARRAY`	Functions that extract JSON data.
Lax converters	`LAX_BOOL` `LAX_FLOAT64` `LAX_INT64` `LAX_STRING`	Functions that flexibly convert a JSON value to a SQL value without returning errors.
Converters	`BOOL` `BOOL_ARRAY` `FLOAT64` `FLOAT64_ARRAY` `FLOAT32` `FLOAT32_ARRAY` `INT64` `INT64_ARRAY` `STRING` `STRING_ARRAY`	Functions that convert a JSON value to a SQL value.
Other converters	`PARSE_JSON` `TO_JSON` `SAFE_TO_JSON` `TO_JSON_STRING`	Other conversion functions from or to JSON.
Constructors	`JSON_ARRAY` `JSON_OBJECT`	Functions that create JSON.
Accessors	`JSON_TYPE`	Functions that provide access to JSON properties.

Function list

Name	Summary
`BOOL`	Converts a JSON boolean to a SQL `BOOL` value.
`BOOL_ARRAY`	Converts a JSON array of booleans to a SQL `ARRAY<BOOL>` value.
`FLOAT64`	Converts a JSON number to a SQL `FLOAT64` value.
`FLOAT64_ARRAY`	Converts a JSON array of numbers to a SQL `ARRAY<FLOAT64>` value.
`FLOAT32`	Converts a JSON number to a SQL `FLOAT32` value.
`FLOAT32_ARRAY`	Converts a JSON array of numbers to a SQL `ARRAY<FLOAT32>` value.
`INT64`	Converts a JSON number to a SQL `INT64` value.
`INT64_ARRAY`	Converts a JSON array of numbers to a SQL `ARRAY<INT64>` value.
`JSON_ARRAY`	Creates a JSON array.
`JSON_OBJECT`	Creates a JSON object.
`JSON_QUERY`	Extracts a JSON value and converts it to a SQL JSON-formatted `STRING` or `JSON` value.
`JSON_QUERY_ARRAY`	Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` or `ARRAY<JSON>` 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`	Extracts a JSON scalar value and converts it to a SQL `STRING` value.
`JSON_VALUE_ARRAY`	Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value.
`LAX_BOOL`	Attempts to convert a JSON value to a SQL `BOOL` value.
`LAX_FLOAT64`	Attempts to convert a JSON value to a SQL `FLOAT64` value.
`LAX_INT64`	Attempts to convert a JSON value to a SQL `INT64` value.
`LAX_STRING`	Attempts to convert a JSON value to a SQL `STRING` value.
`PARSE_JSON`	Converts a JSON-formatted `STRING` value to a `JSON` value.
`SAFE_TO_JSON`	Similar to the `TO_JSON` function, but for each unsupported field in the input argument, produces a JSON null instead of an error.
`STRING` (JSON)	Converts a JSON string to a SQL `STRING` value.
`STRING_ARRAY`	Converts a JSON array of strings to a SQL `ARRAY<STRING>` value.
`TO_JSON`	Converts a SQL value to a JSON value.
`TO_JSON_STRING`	Converts a `JSON` value to a SQL JSON-formatted `STRING` value.

`BOOL`

BOOL(json_expr)

Description

Converts a JSON boolean to a SQL BOOL value.

Arguments:

json_expr: JSON. For example:
```
JSON 'true'
```
If the JSON value is not a boolean, an error is produced. If the expression is SQL NULL, the function returns SQL NULL.

Return type

BOOL

Examples

SELECT BOOL(JSON 'true') AS vacancy;

/*---------*
 | vacancy |
 +---------+
 | true    |
 *---------*/

SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

/*---------*
 | vacancy |
 +---------+
 | true    |
 *---------*/

The following examples show how invalid requests are handled:

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throws an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

`BOOL_ARRAY`

BOOL_ARRAY(json_expr)

Description

Converts a JSON array of booleans to a SQL ARRAY<BOOL> value.

Arguments:

json_expr: JSON. For example:
```
JSON '[true]'
```
If the JSON value is not an array of booleans, an error is produced. If the expression is SQL NULL, the function returns SQL NULL.

Return type

ARRAY<BOOL>

Examples

SELECT BOOL_ARRAY(JSON '[true, false]') AS vacancies;

/*---------------*
 | vacancies     |
 +---------------+
 | [true, false] |
 *---------------*/

The following examples show how invalid requests are handled:

-- An error is thrown if the JSON is not an array of booleans.
SELECT BOOL_ARRAY(JSON '[123]') AS result; -- Throws an error
SELECT BOOL_ARRAY(JSON '[null]') AS result; -- Throws an error
SELECT BOOL_ARRAY(JSON 'null') AS result; -- Throws an error

`FLOAT64`

FLOAT64(
  json_expr
  [, wide_number_mode => { 'exact' | 'round' } ]
)

Description

Converts a JSON number to a SQL FLOAT64 value.

Arguments:

json_expr: JSON. For example:
```
JSON '9.8'
```
If the JSON value is not a number, an error is produced. If the expression is a SQL NULL, the function returns SQL NULL.
wide_number_mode: A named argument with a STRING value. Defines what happens with a number that can't be represented as a FLOAT64 without loss of precision. This argument accepts one of the two case-sensitive values:
- exact: The function fails if the result cannot be represented as a FLOAT64 without loss of precision.
- round (default): The numeric value stored in JSON will be rounded to FLOAT64. If such rounding is not possible, the function fails.

Return type

FLOAT64

Examples

SELECT FLOAT64(JSON '9.8') AS velocity;

/*----------*
 | velocity |
 +----------+
 | 9.8      |
 *----------*/

SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

/*---------*
 | vo2_max |
 +---------+
 | 39.1    |
 *---------*/

SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

/*------------------------*
 | result                 |
 +------------------------+
 | 1.8446744073709552e+19 |
 *------------------------*/

SELECT FLOAT64(JSON '18446744073709551615') as result;

/*------------------------*
 | result                 |
 +------------------------+
 | 1.8446744073709552e+19 |
 *------------------------*/

The following examples show how invalid requests are handled:

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

`FLOAT64_ARRAY`

FLOAT64_ARRAY(
  json_expr
  [, wide_number_mode => { 'exact' | 'round' } ]
)

Description

Converts a JSON array of numbers to a SQL ARRAY<FLOAT64> value.

Arguments:

json_expr: JSON. For example:
```
JSON '[9.8]'
```
If the JSON value is not an array of numbers, an error is produced. If the expression is a SQL NULL, the function returns SQL NULL.
wide_number_mode: A named argument that takes a STRING value. Defines what happens with a number that can't be represented as a FLOAT64 without loss of precision. This argument accepts one of the two case-sensitive values:
- exact: The function fails if the result cannot be represented as a FLOAT64 without loss of precision.
- round (default): The numeric value stored in JSON will be rounded to FLOAT64. If such rounding is not possible, the function fails.

Return type

ARRAY<FLOAT64>

Examples

SELECT FLOAT64_ARRAY(JSON '[9, 9.8]') AS velocities;

/*-------------*
 | velocities  |
 +-------------+
 | [9.0, 9.8]  |
 *-------------*/

SELECT FLOAT64_ARRAY(JSON '[18446744073709551615]', wide_number_mode=>'round') as result;

/*--------------------------*
 | result                   |
 +--------------------------+
 | [1.8446744073709552e+19] |
 *--------------------------*/

SELECT FLOAT64_ARRAY(JSON '[18446744073709551615]') as result;

/*--------------------------*
 | result                   |
 +--------------------------+
 | [1.8446744073709552e+19] |
 *--------------------------*/

The following examples show how invalid requests are handled:

-- An error is thrown if the JSON is not an array of numbers.
SELECT FLOAT64_ARRAY(JSON '["strawberry"]') AS result;
SELECT FLOAT64_ARRAY(JSON '[null]') AS result;
SELECT FLOAT64_ARRAY(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64_ARRAY(JSON '[123.4]', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64_ARRAY(JSON '[123.4]', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64_ARRAY(JSON '[18446744073709551615]', wide_number_mode=>'exact') as result;

`FLOAT32`

FLOAT32(
  json_expr
  [, [ wide_number_mode => ] { 'exact' | 'round' } ]
)

Description

Converts a JSON number to a SQL FLOAT32 value.

Arguments:

json_expr: JSON. For example:
```
JSON '9.8'
```
If the JSON value is not a number, an error is produced. If the expression is a SQL NULL, the function returns SQL NULL.
wide_number_mode: A named argument with a STRING value. Defines what happens with a number that cannot be represented as a FLOAT32 without loss of precision. This argument accepts one of the two case-sensitive values:
- exact: The function fails if the result cannot be represented as a FLOAT32 without loss of precision.
- round (default): The numeric value stored in JSON will be rounded to FLOAT32. If such rounding is not possible, the function fails.

Return type

FLOAT32

Examples

SELECT FLOAT32(JSON '9.8') AS velocity;

/*----------*
 | velocity |
 +----------+
 | 9.8      |
 *----------*/

SELECT FLOAT32(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

/*---------*
 | vo2_max |
 +---------+
 | 39.1    |
 *---------*/

SELECT FLOAT32(JSON '16777217', wide_number_mode=>'round') as result;

/*------------*
 | result     |
 +------------+
 | 16777216.0 |
 *------------*/

SELECT FLOAT32(JSON '16777216') as result;

/*------------*
 | result     |
 +------------+
 | 16777216.0 |
 *------------*/

The following examples show how invalid requests are handled:

-- An error is thrown if JSON is not of type FLOAT32.
SELECT FLOAT32(JSON '"strawberry"') AS result;
SELECT FLOAT32(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT32(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT32(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to FLOAT without loss of precision
SELECT FLOAT32(JSON '16777217', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT32(JSON '"strawberry"') AS result;

`FLOAT32_ARRAY`

FLOAT32_ARRAY(
  json_expr
  [, wide_number_mode => { 'exact' | 'round' } ]
)

Description

Converts a JSON array of numbers to a SQL ARRAY<FLOAT32> value.

Arguments:

json_expr: JSON. For example:
```
JSON '[9.8]'
```
If the JSON value is not an array of numbers, an error is produced. If the expression is a SQL NULL, the function returns SQL NULL.
wide_number_mode: A named argument with a STRING value. Defines what happens with a number that can't be represented as a FLOAT32 without loss of precision. This argument accepts one of the two case-sensitive values:
- exact: The function fails if the result cannot be represented as a FLOAT32 without loss of precision.
- round (default): The numeric value stored in JSON will be rounded to FLOAT32. If such rounding is not possible, the function fails.

Return type

ARRAY<FLOAT32>

Examples

SELECT FLOAT32_ARRAY(JSON '[9, 9.8]') AS velocities;

/*-------------*
 | velocities  |
 +-------------+
 | [9.0, 9.8]  |
 *-------------*/

SELECT FLOAT32_ARRAY(JSON '[16777217]', wide_number_mode=>'round') as result;

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

SELECT FLOAT32_ARRAY(JSON '[16777216]') as result;

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

The following examples show how invalid requests are handled:

-- An error is thrown if the JSON is not an array of numbers in FLOAT32 domain.
SELECT FLOAT32_ARRAY(JSON '["strawberry"]') AS result;
SELECT FLOAT32_ARRAY(JSON '[null]') AS result;
SELECT FLOAT32_ARRAY(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT32_ARRAY(JSON '[123.4]', wide_number_mode=>'EXACT') as result;
SELECT FLOAT32_ARRAY(JSON '[123.4]', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to FLOAT without loss of precision
SELECT FLOAT32_ARRAY(JSON '[16777217]', wide_number_mode=>'exact') as result;

`INT64`

INT64(json_expr)

Description

Converts a JSON number to a SQL INT64 value.

Arguments:

json_expr: JSON. For example:
```
JSON '999'
```
If the JSON value is not a number, or the JSON number is not in the SQL INT64 domain, an error is produced. If the expression is SQL NULL, the function returns SQL NULL.

Return type

INT64

Examples

SELECT INT64(JSON '2005') AS flight_number;

/*---------------*
 | flight_number |
 +---------------+
 | 2005          |
 *---------------*/

SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

/*---------------*
 | flight_number |
 +---------------+
 | 2005          |
 *---------------*/

SELECT INT64(JSON '10.0') AS score;

/*-------*
 | score |
 +-------+
 | 10    |
 *-------*/

The following examples show how invalid requests are handled:

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

`INT64_ARRAY`

INT64_ARRAY(json_expr)

Description

Converts a JSON array of numbers to a SQL INT64_ARRAY value.

Arguments:

json_expr: JSON. For example:
```
JSON '[999]'
```
If the JSON value is not an array of numbers, or the JSON numbers are not in the SQL INT64 domain, an error is produced. If the expression is SQL NULL, the function returns SQL NULL.

Return type

ARRAY<INT64>

Examples

SELECT INT64_ARRAY(JSON '[2005, 2003]') AS flight_numbers;

/*----------------*
 | flight_numbers |
 +----------------+
 | [2005, 2003]   |
 *----------------*/

SELECT INT64_ARRAY(JSON '[10.0]') AS scores;

/*--------*
 | scores |
 +--------+
 | [10]   |
 *--------*/

The following examples show how invalid requests are handled:

-- An error is thrown if the JSON is not an array of numbers in INT64 domain.
SELECT INT64_ARRAY(JSON '[10.1]') AS result;  -- Throws an error
SELECT INT64_ARRAY(JSON '["strawberry"]') AS result; -- Throws an error
SELECT INT64_ARRAY(JSON '[null]') AS result; -- Throws an error
SELECT INT64_ARRAY(JSON 'null') AS result; -- Throws an error

`JSON_ARRAY`

JSON_ARRAY([value][, ...])

Description

Creates a JSON array from zero or more SQL values.

Arguments:

value: A JSON encoding-supported value to add to a JSON array.

Return type

JSON

Examples

The following query creates a JSON array with one value in it:

SELECT JSON_ARRAY(10) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | [10]      |
 *-----------*/

You can create a JSON array with an empty JSON array in it. For example:

SELECT JSON_ARRAY([]) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | [[]]      |
 *-----------*/

SELECT JSON_ARRAY(10, 'foo', NULL) AS json_data

/*-----------------*
 | json_data       |
 +-----------------+
 | [10,"foo",null] |
 *-----------------*/

SELECT JSON_ARRAY(STRUCT(10 AS a, 'foo' AS b)) AS json_data

/*----------------------*
 | json_data            |
 +----------------------+
 | [{"a":10,"b":"foo"}] |
 *----------------------*/

SELECT JSON_ARRAY(10, ['foo', 'bar'], [20, 30]) AS json_data

/*----------------------------*
 | json_data                  |
 +----------------------------+
 | [10,["foo","bar"],[20,30]] |
 *----------------------------*/

SELECT JSON_ARRAY(10, [JSON '20', JSON '"foo"']) AS json_data

/*-----------------*
 | json_data       |
 +-----------------+
 | [10,[20,"foo"]] |
 *-----------------*/

You can create an empty JSON array. For example:

SELECT JSON_ARRAY() AS json_data

/*-----------*
 | json_data |
 +-----------+
 | []        |
 *-----------*/

`JSON_OBJECT`

Signature 1: JSON_OBJECT([json_key, json_value][, ...])
Signature 2: JSON_OBJECT(json_key_array, json_value_array)

Signature 1

JSON_OBJECT([json_key, json_value][, ...])

Description

Creates a JSON object, using key-value pairs.

Arguments:

json_key: A STRING value that represents a key.
json_value: A JSON encoding-supported value.

Details:

If two keys are passed in with the same name, only the first key-value pair is preserved.
The order of key-value pairs is not preserved.
If json_key is NULL, an error is produced.

Return type

JSON

Examples

You can create an empty JSON object by passing in no JSON keys and values. For example:

SELECT JSON_OBJECT() AS json_data

/*-----------*
 | json_data |
 +-----------+
 | {}        |
 *-----------*/

You can create a JSON object by passing in key-value pairs. For example:

SELECT JSON_OBJECT('foo', 10, 'bar', TRUE) AS json_data

/*-----------------------*
 | json_data             |
 +-----------------------+
 | {"bar":true,"foo":10} |
 *-----------------------*/

SELECT JSON_OBJECT('foo', 10, 'bar', ['a', 'b']) AS json_data

/*----------------------------*
 | json_data                  |
 +----------------------------+
 | {"bar":["a","b"],"foo":10} |
 *----------------------------*/

SELECT JSON_OBJECT('a', NULL, 'b', JSON 'null') AS json_data

/*---------------------*
 | json_data           |
 +---------------------+
 | {"a":null,"b":null} |
 *---------------------*/

SELECT JSON_OBJECT('a', 10, 'a', 'foo') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | {"a":10}  |
 *-----------*/

WITH Items AS (SELECT 'hello' AS key, 'world' AS value)
SELECT JSON_OBJECT(key, value) AS json_data FROM Items

/*-------------------*
 | json_data         |
 +-------------------+
 | {"hello":"world"} |
 *-------------------*/

An error is produced if a SQL NULL is passed in for a JSON key.

-- Error: A key cannot be NULL.
SELECT JSON_OBJECT(NULL, 1) AS json_data

An error is produced if the number of JSON keys and JSON values don't match:

-- Error: No matching signature for function JSON_OBJECT for argument types:
-- STRING, INT64, STRING
SELECT JSON_OBJECT('a', 1, 'b') AS json_data

Signature 2

JSON_OBJECT(json_key_array, json_value_array)

Creates a JSON object, using an array of keys and values.

Arguments:

json_key_array: An array of zero or more STRING keys.
json_value_array: An array of zero or more JSON encoding-supported values.

Details:

If two keys are passed in with the same name, only the first key-value pair is preserved.
The order of key-value pairs is not preserved.
The number of keys must match the number of values, otherwise an error is produced.
If any argument is NULL, an error is produced.
If a key in json_key_array is NULL, an error is produced.

Return type

JSON

Examples

You can create an empty JSON object by passing in an empty array of keys and values. For example:

SELECT JSON_OBJECT(CAST([] AS ARRAY<STRING>), []) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | {}        |
 *-----------*/

You can create a JSON object by passing in an array of keys and an array of values. For example:

SELECT JSON_OBJECT(['a', 'b'], [10, NULL]) AS json_data

/*-------------------*
 | json_data         |
 +-------------------+
 | {"a":10,"b":null} |
 *-------------------*/

SELECT JSON_OBJECT(['a', 'b'], [JSON '10', JSON '"foo"']) AS json_data

/*--------------------*
 | json_data          |
 +--------------------+
 | {"a":10,"b":"foo"} |
 *--------------------*/

SELECT
  JSON_OBJECT(
    ['a', 'b'],
    [STRUCT(10 AS id, 'Red' AS color), STRUCT(20 AS id, 'Blue' AS color)])
    AS json_data

/*------------------------------------------------------------*
 | json_data                                                  |
 +------------------------------------------------------------+
 | {"a":{"color":"Red","id":10},"b":{"color":"Blue","id":20}} |
 *------------------------------------------------------------*/

SELECT
  JSON_OBJECT(
    ['a', 'b'],
    [TO_JSON(10), TO_JSON(['foo', 'bar'])])
    AS json_data

/*----------------------------*
 | json_data                  |
 +----------------------------+
 | {"a":10,"b":["foo","bar"]} |
 *----------------------------*/

The following query groups by id and then creates an array of keys and values from the rows with the same id:

WITH
  Fruits AS (
    SELECT 0 AS id, 'color' AS json_key, 'red' AS json_value UNION ALL
    SELECT 0, 'fruit', 'apple' UNION ALL
    SELECT 1, 'fruit', 'banana' UNION ALL
    SELECT 1, 'ripe', 'true'
  )
SELECT JSON_OBJECT(ARRAY_AGG(json_key), ARRAY_AGG(json_value)) AS json_data
FROM Fruits
GROUP BY id

/*----------------------------------*
 | json_data                        |
 +----------------------------------+
 | {"color":"red","fruit":"apple"}  |
 | {"fruit":"banana","ripe":"true"} |
 *----------------------------------*/

An error is produced if the size of the JSON keys and values arrays don't match:

-- Error: The number of keys and values must match.
SELECT JSON_OBJECT(['a', 'b'], [10]) AS json_data

An error is produced if the array of JSON keys or JSON values is a SQL NULL.

-- Error: The keys array cannot be NULL.
SELECT JSON_OBJECT(CAST(NULL AS ARRAY<STRING>), [10, 20]) AS json_data

-- Error: The values array cannot be NULL.
SELECT JSON_OBJECT(['a', 'b'], CAST(NULL AS ARRAY<INT64>)) AS json_data

`JSON_QUERY`

JSON_QUERY(json_string_expr, json_path)

JSON_QUERY(json_expr, json_path)

Description

Extracts a JSON value and converts it to a SQL JSON-formatted STRING or JSON 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_expr: JSON. For example:

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

Extracts a JSON null when a JSON null is encountered.

SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'

json_path: The JSONPath. This identifies the data that you want to obtain from the input.

There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.

Return type

json_string_expr: A JSON-formatted STRING
json_expr: JSON

Examples

In the following example, JSON data is extracted and returned as JSON.

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": [{"id": 5}, {"id": 12}]}}',
    '$.class') AS json_data;

/*-----------------------------------*
 | json_data                         |
 +-----------------------------------+
 | {"students":[{"id":5},{"id":12}]} |
 *-----------------------------------*/

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

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

`JSON_QUERY_ARRAY`

JSON_QUERY_ARRAY(json_string_expr[, json_path])

JSON_QUERY_ARRAY(json_expr[, json_path])

Description

Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> or ARRAY<JSON> 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_expr: JSON. For example:
```
JSON '["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.

There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.

Return type

json_string_expr: ARRAY<JSON-formatted STRING>
json_expr: ARRAY<JSON>

Examples

This extracts items in JSON to an array of JSON values:

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

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

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_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 is not 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`

JSON_VALUE(json_string_expr[, json_path])

JSON_VALUE(json_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_expr: JSON. For example:
```
JSON '{"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.

There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.

Return type

STRING

Examples

In the following example, JSON data is extracted and returned as a scalar value.

SELECT JSON_VALUE(JSON '{"name": "Jakob", "age": "6" }', '$.age') AS scalar_age;

/*------------*
 | scalar_age |
 +------------+
 | 6          |
 *------------*/

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 |
 *-------*/

`JSON_VALUE_ARRAY`

JSON_VALUE_ARRAY(json_string_expr[, json_path])

JSON_VALUE_ARRAY(json_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 is not 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_expr: JSON. For example:
```
JSON '["apples", "oranges", "grapes"]'
```
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.

There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.

Caveats:

A JSON null in the input array produces a SQL NULL as the output for that JSON null.
If a JSONPath matches an array that contains scalar objects and a JSON null, then the output is an array of the scalar objects and a SQL NULL.

Return type

ARRAY<STRING>

Examples

This extracts items in JSON to a string array:

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

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

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 does not 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 is not 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 |
 +--------+
 | []     |
 *--------*/

-- In the following query, the JSON null input is returned as a
-- SQL NULL in the output.
SELECT JSON_VALUE_ARRAY('["world", null, 1]') AS result;

/*------------------*
 | result           |
 +------------------+
 | [world, NULL, 1] |
 *------------------*/

`LAX_BOOL`

LAX_BOOL(json_expr)

Description

Attempts to convert a JSON value to a SQL BOOL value.

Arguments:

json_expr: JSON. For example:
```
JSON 'true'
```

Details:

If json_expr is SQL NULL, the function returns SQL NULL.
See the conversion rules in the next section for additional NULL handling.

Conversion rules

From JSON type	To SQL `BOOL`
boolean	If the JSON boolean is `true`, returns `TRUE`. Otherwise, returns `FALSE`.
string	If the JSON string is `'true'`, returns `TRUE`. If the JSON string is `'false'`, returns `FALSE`. If the JSON string is any other value or has whitespace in it, returns `NULL`. This conversion is case-insensitive.
number	If the JSON number is a representation of `0`, returns `FALSE`. Otherwise, returns `TRUE`.
other type or null	`NULL`

Return type

BOOL

Examples

Example with input that is a JSON boolean:

SELECT LAX_BOOL(JSON 'true') AS result;

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

Examples with inputs that are JSON strings:

SELECT LAX_BOOL(JSON '"true"') AS result;

/*--------*
 | result |
 +--------+
 | TRUE   |
 *--------*/

SELECT LAX_BOOL(JSON '"true "') AS result;

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

SELECT LAX_BOOL(JSON '"foo"') AS result;

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

Examples with inputs that are JSON numbers:

SELECT LAX_BOOL(JSON '10') AS result;

/*--------*
 | result |
 +--------+
 | TRUE   |
 *--------*/

SELECT LAX_BOOL(JSON '0') AS result;

/*--------*
 | result |
 +--------+
 | FALSE  |
 *--------*/

SELECT LAX_BOOL(JSON '0.0') AS result;

/*--------*
 | result |
 +--------+
 | FALSE  |
 *--------*/

SELECT LAX_BOOL(JSON '-1.1') AS result;

/*--------*
 | result |
 +--------+
 | TRUE   |
 *--------*/

`LAX_FLOAT64`

LAX_FLOAT64(json_expr)

Description

Attempts to convert a JSON value to a SQL FLOAT64 value.

Arguments:

json_expr: JSON. For example:
```
JSON '9.8'
```

Details:

If json_expr is SQL NULL, the function returns SQL NULL.
See the conversion rules in the next section for additional NULL handling.

Conversion rules

From JSON type	To SQL `FLOAT64`
boolean	`NULL`
string	If the JSON string represents a JSON number, parses it as a JSON number, and then safe casts the result as a `FLOAT64` value. If the JSON string can't be converted, returns `NULL`.
number	Casts the JSON number as a `FLOAT64` value. Large JSON numbers are rounded.
other type or null	`NULL`

Return type

FLOAT64

Examples

Examples with inputs that are JSON numbers:

SELECT LAX_FLOAT64(JSON '9.8') AS result;

/*--------*
 | result |
 +--------+
 | 9.8    |
 *--------*/

SELECT LAX_FLOAT64(JSON '9') AS result;

/*--------*
 | result |
 +--------+
 | 9.0    |
 *--------*/

SELECT LAX_FLOAT64(JSON '9007199254740993') AS result;

/*--------------------*
 | result             |
 +--------------------+
 | 9007199254740992.0 |
 *--------------------*/

SELECT LAX_FLOAT64(JSON '1e100') AS result;

/*--------*
 | result |
 +--------+
 | 1e+100 |
 *--------*/

Examples with inputs that are JSON booleans:

SELECT LAX_FLOAT64(JSON 'true') AS result;

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

SELECT LAX_FLOAT64(JSON 'false') AS result;

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

Examples with inputs that are JSON strings:

SELECT LAX_FLOAT64(JSON '"10"') AS result;

/*--------*
 | result |
 +--------+
 | 10.0   |
 *--------*/

SELECT LAX_FLOAT64(JSON '"1.1"') AS result;

/*--------*
 | result |
 +--------+
 | 1.1    |
 *--------*/

SELECT LAX_FLOAT64(JSON '"1.1e2"') AS result;

/*--------*
 | result |
 +--------+
 | 110.0  |
 *--------*/

SELECT LAX_FLOAT64(JSON '"9007199254740993"') AS result;

/*--------------------*
 | result             |
 +--------------------+
 | 9007199254740992.0 |
 *--------------------*/

SELECT LAX_FLOAT64(JSON '"+1.5"') AS result;

/*--------*
 | result |
 +--------+
 | 1.5    |
 *--------*/

SELECT LAX_FLOAT64(JSON '"NaN"') AS result;

/*--------*
 | result |
 +--------+
 | NaN    |
 *--------*/

SELECT LAX_FLOAT64(JSON '"Inf"') AS result;

/*----------*
 | result   |
 +----------+
 | Infinity |
 *----------*/

SELECT LAX_FLOAT64(JSON '"-InfiNiTY"') AS result;

/*-----------*
 | result    |
 +-----------+
 | -Infinity |
 *-----------*/

SELECT LAX_FLOAT64(JSON '"foo"') AS result;

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

`LAX_INT64`

LAX_INT64(json_expr)

Description

Attempts to convert a JSON value to a SQL INT64 value.

Arguments:

json_expr: JSON. For example:
```
JSON '999'
```

Details:

If json_expr is SQL NULL, the function returns SQL NULL.
See the conversion rules in the next section for additional NULL handling.

Conversion rules

From JSON type	To SQL `INT64`
boolean	If the JSON boolean is `true`, returns `1`. If `false`, returns `0`.
string	If the JSON string represents a JSON number, parses it as a JSON number, and then safe casts the results as an `INT64` value. If the JSON string can't be converted, returns `NULL`.
number	Casts the JSON number as an `INT64` value. If the JSON number can't be converted, returns `NULL`.
other type or null	`NULL`

Return type

INT64

Examples

Examples with inputs that are JSON numbers:

SELECT LAX_INT64(JSON '10') AS result;

/*--------*
 | result |
 +--------+
 | 10     |
 *--------*/

SELECT LAX_INT64(JSON '10.0') AS result;

/*--------*
 | result |
 +--------+
 | 10     |
 *--------*/

SELECT LAX_INT64(JSON '1.1') AS result;

/*--------*
 | result |
 +--------+
 | 1      |
 *--------*/

SELECT LAX_INT64(JSON '3.5') AS result;

/*--------*
 | result |
 +--------+
 | 4      |
 *--------*/

SELECT LAX_INT64(JSON '1.1e2') AS result;

/*--------*
 | result |
 +--------+
 | 110    |
 *--------*/

SELECT LAX_INT64(JSON '1e100') AS result;

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

Examples with inputs that are JSON booleans:

SELECT LAX_INT64(JSON 'true') AS result;

/*--------*
 | result |
 +--------+
 | 1      |
 *--------*/

SELECT LAX_INT64(JSON 'false') AS result;

/*--------*
 | result |
 +--------+
 | 0      |
 *--------*/

Examples with inputs that are JSON strings:

SELECT LAX_INT64(JSON '"10"') AS result;

/*--------*
 | result |
 +--------+
 | 10     |
 *--------*/

SELECT LAX_INT64(JSON '"1.1"') AS result;

/*--------*
 | result |
 +--------+
 | 1      |
 *--------*/

SELECT LAX_INT64(JSON '"1.1e2"') AS result;

/*--------*
 | result |
 +--------+
 | 110    |
 *--------*/

SELECT LAX_INT64(JSON '"+1.5"') AS result;

/*--------*
 | result |
 +--------+
 | 2      |
 *--------*/

SELECT LAX_INT64(JSON '"1e100"') AS result;

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

SELECT LAX_INT64(JSON '"foo"') AS result;

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

`LAX_STRING`

LAX_STRING(json_expr)

Description

Attempts to convert a JSON value to a SQL STRING value.

Arguments:

json_expr: JSON. For example:
```
JSON '"name"'
```

Details:

If json_expr is SQL NULL, the function returns SQL NULL.
See the conversion rules in the next section for additional NULL handling.

Conversion rules

From JSON type	To SQL `STRING`
boolean	If the JSON boolean is `true`, returns `'true'`. If `false`, returns `'false'`.
string	Returns the JSON string as a `STRING` value.
number	Returns the JSON number as a `STRING` value.
other type or null	`NULL`

Return type

STRING

Examples

Examples with inputs that are JSON strings:

SELECT LAX_STRING(JSON '"purple"') AS result;

/*--------*
 | result |
 +--------+
 | purple |
 *--------*/

SELECT LAX_STRING(JSON '"10"') AS result;

/*--------*
 | result |
 +--------+
 | 10     |
 *--------*/

Examples with inputs that are JSON booleans:

SELECT LAX_STRING(JSON 'true') AS result;

/*--------*
 | result |
 +--------+
 | true   |
 *--------*/

SELECT LAX_STRING(JSON 'false') AS result;

/*--------*
 | result |
 +--------+
 | false  |
 *--------*/

Examples with inputs that are JSON numbers:

SELECT LAX_STRING(JSON '10.0') AS result;

/*--------*
 | result |
 +--------+
 | 10     |
 *--------*/

SELECT LAX_STRING(JSON '10') AS result;

/*--------*
 | result |
 +--------+
 | 10     |
 *--------*/

SELECT LAX_STRING(JSON '1e100') AS result;

/*--------*
 | result |
 +--------+
 | 1e+100 |
 *--------*/

`PARSE_JSON`

PARSE_JSON(
  json_string_expr
  [, wide_number_mode => { 'exact' | 'round' } ]
)

Description

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

Arguments:

json_string_expr: A JSON-formatted string. For example:
```
'{"class": {"students": [{"name": "Jane"}]}}'
```
wide_number_mode: A named argument with a STRING value. Determines how to handle numbers that can't be stored in a JSON value without the loss of precision. If used, wide_number_mode must include one of the following values:
- exact (default): Only accept numbers that can be stored without loss of precision. If a number that cannot be stored without loss of precision is encountered, the function throws an error.
- round: If a number that cannot be stored without loss of precision is encountered, attempt to round it to a number that can be stored without loss of precision. If the number cannot be rounded, the function throws an error.
If a number appears in a JSON object or array, the wide_number_mode argument is applied to the number in the object or array.

Numbers from the following domains can be stored in JSON without loss of precision:

64-bit signed/unsigned integers, such as INT64
FLOAT64

Return type

JSON

Examples

In the following example, a JSON-formatted string is converted to JSON.

SELECT PARSE_JSON('{"coordinates": [10, 20], "id": 1}') AS json_data;

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

The following queries fail because:

The number that was passed in cannot be stored without loss of precision.
wide_number_mode=>'exact' is used implicitly in the first query and explicitly in the second query.

SELECT PARSE_JSON('{"id": 922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id": 922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

The following query rounds the number to a number that can be stored in JSON.

SELECT PARSE_JSON('{"id": 922337203685477580701}', wide_number_mode=>'round') AS json_data;

/*------------------------------*
 | json_data                    |
 +------------------------------+
 | {"id":9.223372036854776e+20} |
 *------------------------------*/

You can also use valid JSON-formatted strings that don't represent name/value pairs. For example:

SELECT PARSE_JSON('6') AS json_data;

/*------------------------------*
 | json_data                    |
 +------------------------------+
 | 6                            |
 *------------------------------*/

SELECT PARSE_JSON('"red"') AS json_data;

/*------------------------------*
 | json_data                    |
 +------------------------------+
 | "red"                        |
 *------------------------------*/

`SAFE_TO_JSON`

SAFE_TO_JSON(sql_value)

Description

Similar to the TO_JSON function, but for each unsupported field in the input argument, produces a JSON null instead of an error.

Arguments:

sql_value: The SQL value to convert to a JSON value. You can review the GoogleSQL data types that this function supports and their JSON encodings.

Return type

JSON

Example

The following queries are functionally the same, except that SAFE_TO_JSON produces a JSON null instead of an error when a hypothetical unsupported data type is encountered:

-- Produces a JSON null.
SELECT SAFE_TO_JSON(CAST(b'' AS UNSUPPORTED_TYPE)) as result;

-- Produces an error.
SELECT TO_JSON(CAST(b'' AS UNSUPPORTED_TYPE), stringify_wide_numbers=>TRUE) as result;

In the following query, the value for ut is ignored because the value is an unsupported type:

SELECT SAFE_TO_JSON(STRUCT(CAST(b'' AS UNSUPPORTED_TYPE) AS ut) AS result;

/*--------------*
 | result       |
 +--------------+
 | {"ut": null} |
 *--------------*/

The following array produces a JSON null instead of an error because the data type for the array is not supported.

SELECT SAFE_TO_JSON([
        CAST(b'' AS UNSUPPORTED_TYPE),
        CAST(b'' AS UNSUPPORTED_TYPE),
        CAST(b'' AS UNSUPPORTED_TYPE),
    ]) AS result;

/*------------*
 | result     |
 +------------+
 | null       |
 *------------*/

`STRING`

STRING(json_expr)

Description

Converts a JSON string to a SQL STRING value.

Arguments:

json_expr: JSON. For example:
```
JSON '"purple"'
```
If the JSON value is not a string, an error is produced. If the expression is SQL NULL, the function returns SQL NULL.

Return type

STRING

Examples

SELECT STRING(JSON '"purple"') AS color;

/*--------*
 | color  |
 +--------+
 | purple |
 *--------*/

SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

/*-------*
 | color |
 +-------+
 | blue  |
 *-------*/

The following examples show how invalid requests are handled:

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

`STRING_ARRAY`

STRING_ARRAY(json_expr)

Description

Converts a JSON array of strings to a SQL ARRAY<STRING> value.

Arguments:

json_expr: JSON. For example:
```
JSON '["purple", "blue"]'
```
If the JSON value is not an array of strings, an error is produced. If the expression is SQL NULL, the function returns SQL NULL.

Return type

ARRAY<STRING>

Examples

SELECT STRING_ARRAY(JSON '["purple", "blue"]') AS colors;

/*----------------*
 | colors         |
 +----------------+
 | [purple, blue] |
 *----------------*/

The following examples show how invalid requests are handled:

-- An error is thrown if the JSON is not an array of strings.
SELECT STRING_ARRAY(JSON '[123]') AS result; -- Throws an error
SELECT STRING_ARRAY(JSON '[null]') AS result; -- Throws an error
SELECT STRING_ARRAY(JSON 'null') AS result; -- Throws an error

`TO_JSON`

TO_JSON(
  sql_value
  [, stringify_wide_numbers => { TRUE | FALSE } ]
)

Description

Converts a SQL value to a JSON value.

Arguments:

sql_value: The SQL value to convert to a JSON value. You can review the GoogleSQL data types that this function supports and their JSON encodings here.
stringify_wide_numbers: A named argument that's either TRUE or FALSE (default).
- If TRUE, numeric values outside of the FLOAT64 type domain are encoded as strings.
- If FALSE (default), numeric values outside of the FLOAT64 type domain are not encoded as strings, but are stored as JSON numbers. If a numerical value cannot be stored in JSON without loss of precision, an error is thrown.
The following numerical data types are affected by the stringify_wide_numbers argument:
INT64
NUMERIC

If one of these numerical data types appears in a container data type such as an ARRAY or STRUCT, the stringify_wide_numbers argument is applied to the numerical data types in the container data type.

Return type

JSON

Examples

In the following example, the query converts rows in a table to JSON values.

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 TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

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

In the following example, the query returns a large numerical value as a JSON string.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on;

/*--------------------*
 | stringify_on       |
 +--------------------+
 | "9007199254740993" |
 *--------------------*/

In the following example, both queries return a large numerical value as a JSON number.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off;
SELECT TO_JSON(9007199254740993) as stringify_off;

/*------------------*
 | stringify_off    |
 +------------------+
 | 9007199254740993 |
 *------------------*/

In the following example, only large numeric values are converted to JSON strings.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

/*---------------------------*
 | json_objects              |
 +---------------------------+
 | {"id":"9007199254740993"} |
 | {"id":2}                  |
 *---------------------------*/

In this example, the values 9007199254740993 (INT64) and 2.1 (FLOAT64) are converted to the common supertype FLOAT64, which is not affected by the stringify_wide_numbers argument.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

/*------------------------------*
 | json_objects                 |
 +------------------------------+
 | {"id":9.007199254740992e+15} |
 | {"id":2.1}                   |
 *------------------------------*/

In the following example, a graph path is converted into a JSON array.

GRAPH FinGraph
MATCH p=(src:Account)-[t1:Transfers]->(dst:Account)
RETURN TO_JSON(p) AS json_array

/*--------------------------------------------------------------------*
 | json_array                                                         |
 +--------------------------------------------------------------------+
 | [{                                                                 |
 |    "identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEg",                    |
 |    "kind":"node",                                                  |
 |    "labels":["Account"],                                           |
 |    "properties":{                                                  |
 |      "create_time":"2020-01-28T01:55:09.206Z",                     |
 |      "id":16,                                                      |
 |      "is_blocked":true,                                            |
 |      "nick_name":"Vacation Fund"                                   |
 |    }                                                               |
 |  },                                                                |
 |  {                                                                 |
 |    "destination_node_identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEo",   |
 |    "identifier":"mUZpbkdyYXBoLkFjY291...",                         |
 |    "kind":"edge",                                                  |
 |    "labels":["Transfers"],                                         |
 |    "properties":{                                                  |
 |      "amount":300.0,                                               |
 |      "create_time":"2020-09-25T09:36:14.926Z",                     |
 |      "id":16,                                                      |
 |      "order_number":"103650009791820",                             |
 |      "to_id":20                                                    |
 |    },                                                              |
 |    "source_node_identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEg"         |
 |  },                                                                |
 |  {                                                                 |
 |    "identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEo",                    |
 |    "kind":"node",                                                  |
 |    "labels":["Account"],                                           |
 |    "properties":{                                                  |
 |      "create_time":"2020-02-18T13:44:20.655Z",                     |
 |      "id":20,                                                      |
 |      "is_blocked":false,                                           |
 |      "nick_name":"Vacation Fund"                                   |
 |    }                                                               |
 |  }                                                                 |
 |  ...                                                               |
 | ]                                                                  |
 *--------------------------------------------------------------------/*

`TO_JSON_STRING`

TO_JSON_STRING(json_expr)

Description

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

Arguments:

json_expr: JSON. For example:

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

Return type

A JSON-formatted STRING

Example

Convert a JSON value to a JSON-formatted STRING value.

SELECT TO_JSON_STRING(JSON '{"id": 1, "coordinates": [10, 20]}') AS json_string

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

Supplemental materials

Differences between the JSON and JSON-formatted STRING types

Many JSON functions accept two input types:

JSON type
STRING type

The STRING version of the extraction functions behaves differently than the JSON version, mainly because JSON type values are always validated whereas JSON-formatted STRING type values are not.

Non-validation of `STRING` inputs

The following STRING is invalid JSON because it is missing a trailing }:

{"hello": "world"

The JSON function reads the input from the beginning and stops as soon as the field to extract is found, without reading the remainder of the input. A parsing error is not produced.

With the JSON type, however, JSON '{"hello": "world"' returns a parsing error.

For example:

SELECT JSON_VALUE('{"hello": "world"', "$.hello") AS hello;

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

SELECT JSON_VALUE(JSON '{"hello": "world"', "$.hello") AS hello;
-- An error is returned: Invalid JSON literal: syntax error while parsing
-- object - unexpected end of input; expected '}'

No strict validation of extracted values

In the following examples, duplicated keys are not removed when using a JSON-formatted string. Similarly, keys order is preserved. For the JSON type, JSON '{"key": 1, "key": 2}' will result in JSON '{"key":1}' during parsing.

SELECT JSON_QUERY('{"key": 1, "key": 2}', "$") AS string;

/*-------------------*
 | string            |
 +-------------------+
 | {"key":1,"key":2} |
 *-------------------*/

SELECT JSON_QUERY(JSON '{"key": 1, "key": 2}', "$") AS json;

/*-----------*
 | json      |
 +-----------+
 | {"key":1} |
 *-----------*/

JSON `null`

When using a JSON-formatted STRING type in a JSON function, a JSON null value is extracted as a SQL NULL value.

When using a JSON type in a JSON function, a JSON null value returns a JSON null value.

WITH t AS (
  SELECT '{"name": null}' AS json_string, JSON '{"name": null}' AS json)
SELECT JSON_QUERY(json_string, "$.name") AS name_string,
  JSON_QUERY(json_string, "$.name") IS NULL AS name_string_is_null,
  JSON_QUERY(json, "$.name") AS name_json,
  JSON_QUERY(json, "$.name") IS NULL AS name_json_is_null
FROM t;

/*-------------+---------------------+-----------+-------------------*
 | name_string | name_string_is_null | name_json | name_json_is_null |
 +-------------+---------------------+-----------+-------------------+
 | NULL        | true                | null      | false             |
 *-------------+---------------------+-----------+-------------------*/

JSON encodings

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

TO_JSON
JSON_ARRAY (uses TO_JSON encoding)
JSON_OBJECT (uses TO_JSON encoding)

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 If the `stringify_wide_numbers` argument is `TRUE` and the value is outside of the FLOAT64 type domain, the value is encoded as a string. If the value cannot be stored in JSON without loss of precision, the function fails. Otherwise, the value is encoded as a number. If the `stringify_wide_numbers` is not used or is `FALSE`, numeric values outside of the `FLOAT64` type domain are not encoded as strings, but are stored as JSON numbers. If a numerical value cannot be stored in JSON without loss of precision, an error is thrown.	SQL input: `9007199254740992` JSON output: `9007199254740992` SQL input: `9007199254740993` JSON output: `9007199254740993` SQL input with stringify_wide_numbers=>TRUE: `9007199254740992` JSON output: `9007199254740992` SQL input with stringify_wide_numbers=>TRUE: `9007199254740993` JSON output: `"9007199254740993"`
NUMERIC	number or string If the `stringify_wide_numbers` argument is `TRUE` and the value is outside of the FLOAT64 type domain, it is encoded as a string. Otherwise, it's encoded as a number.	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` SQL input with stringify_wide_numbers=>TRUE: `9007199254740993` JSON output: `"9007199254740993"` SQL input with stringify_wide_numbers=>TRUE: `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"`
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"`
JSON	data of the input JSON	SQL input: `JSON '{"item": "pen", "price": 10}'` JSON output: `{"item":"pen", "price":10}` SQL input:`[1, 2, 3]` JSON output:`[1, 2, 3]`
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`, a field is included in the output string and any duplicates of this field are omitted. 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}`
PROTO	object The object can contain zero or more key-value pairs. Each value is formatted according to its type. Field names with underscores are converted to camel case in accordance with protobuf json conversion. Field values are formatted according to protobuf json conversion. If a `field_value` is a non-empty repeated field or submessage, the elements and fields are indented to the appropriate level. Field names that aren't valid UTF-8 might result in unparseable JSON. Field annotations are ignored. Repeated fields are represented as arrays. Submessages are formatted as values of PROTO type. Extension fields are included in the output, where the extension field name is enclosed in brackets and prefixed with the full name of the extension type.	SQL input: `NEW Item(12 AS purchases,TRUE AS in_Stock)` JSON output: `{"purchases":12,"inStock": true}`
GRAPH_ELEMENT	object The object can contain zero or more key-value pairs. Each value is formatted according to its type. For `TO_JSON`, graph element (node or edge) objects are supported. The graph element identifier is only valid within the scope of the same query response and cannot be used to correlate entities across different queries. Field names that aren't valid UTF-8 might result in unparseable JSON. The result may include internal key-value pairs that are not defined by the users. The conversion can fail if the object contains values of unsupported types.	SQL: GRAPH FinGraph MATCH (p:Person WHERE p.name = 'Dana') RETURN TO_JSON(p) AS dana_json; JSON output (truncated): {"identifier":"ZGFuYQ==","kind":"node","labels":["Person"],"properties":{"id":2,"name":"Dana"}}
GRAPH_PATH	array The array can contain one or more objects that represent graph elements in a graph path.	SQL: GRAPH FinGraph MATCH account_ownership = (p:Person)-[o:Owns]->(a:Account) RETURN TO_JSON(account_ownership) AS results JSON output for `account_ownership` (truncated): [ {"identifier":"ZGFuYQ==","kind":"node","labels":["Person"], ...}, {"identifier":"TPZuYM==","kind":"edge","labels":["Owns"], ...}, {"identifier":"PRTuMI==","kind":"node","labels":["Account"], ...} ]

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

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 in GoogleSQL

Categories

Function list

BOOL

BOOL_ARRAY

FLOAT64

FLOAT64_ARRAY

FLOAT32

FLOAT32_ARRAY

INT64

INT64_ARRAY

JSON_ARRAY

JSON_OBJECT

Signature 1

Signature 2

JSON_QUERY

JSON_QUERY_ARRAY

JSON_TYPE

JSON_VALUE

JSON_VALUE_ARRAY

LAX_BOOL

LAX_FLOAT64

LAX_INT64

LAX_STRING

PARSE_JSON

SAFE_TO_JSON

STRING

STRING_ARRAY

TO_JSON

TO_JSON_STRING

Supplemental materials

Differences between the JSON and JSON-formatted STRING types

Non-validation of STRING inputs

No strict validation of extracted values

JSON null

JSON encodings

JSONPath format

Operators for JSONPath

`BOOL`

`BOOL_ARRAY`

`FLOAT64`

`FLOAT64_ARRAY`

`FLOAT32`

`FLOAT32_ARRAY`

`INT64`

`INT64_ARRAY`

`JSON_ARRAY`

`JSON_OBJECT`

`JSON_QUERY`

`JSON_QUERY_ARRAY`

`JSON_TYPE`

`JSON_VALUE`

`JSON_VALUE_ARRAY`

`LAX_BOOL`

`LAX_FLOAT64`

`LAX_INT64`

`LAX_STRING`

`PARSE_JSON`

`SAFE_TO_JSON`

`STRING`

`STRING_ARRAY`

`TO_JSON`

`TO_JSON_STRING`

Non-validation of `STRING` inputs

JSON `null`