JSON functions (original) (raw)

Skip to main content

JSON functions

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

GoogleSQL for BigQuery 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 JSON_VALUE_ARRAY Functions that extract JSON data.
Legacy extractors JSON_EXTRACT JSON_EXTRACT_SCALAR JSON_EXTRACT_ARRAY JSON_EXTRACT_STRING_ARRAY Functions that extract JSON data. While these functions are supported by GoogleSQL, we recommend using the standard extractor functions.
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 FLOAT64 INT64 STRING Functions that convert a JSON value to a SQL value.
Other converters PARSE_JSON TO_JSON TO_JSON_STRING Other conversion functions from or to JSON.
Constructors JSON_ARRAY JSON_OBJECT Functions that create JSON.
Mutators JSON_ARRAY_APPEND JSON_ARRAY_INSERT JSON_REMOVE JSON_SET JSON_STRIP_NULLS Functions that mutate existing JSON.
Accessors JSON_KEYS JSON_TYPE Functions that provide access to JSON properties.

Function list

Name Summary
BOOL Converts a JSON boolean to a SQL BOOL value.
FLOAT64 Converts a JSON number to a SQLFLOAT64 value.
INT64 Converts a JSON number to a SQL INT64 value.
JSON_ARRAY Creates a JSON array.
JSON_ARRAY_APPEND Appends JSON data to the end of a JSON array.
JSON_ARRAY_INSERT Inserts JSON data into a JSON array.
JSON_EXTRACT (Deprecated) Extracts a JSON value and converts it to a SQL JSON-formatted STRING orJSON value.
JSON_EXTRACT_ARRAY (Deprecated) Extracts a JSON array and converts it to a SQL ARRAY orARRAY value.
JSON_EXTRACT_SCALAR (Deprecated) Extracts a JSON scalar value and converts it to a SQLSTRING value.
JSON_EXTRACT_STRING_ARRAY (Deprecated) Extracts a JSON array of scalar values and converts it to a SQLARRAY value.
JSON_KEYS Extracts unique JSON keys from a JSON expression.
JSON_OBJECT Creates a JSON object.
JSON_QUERY Extracts a JSON value and converts it to a SQL JSON-formatted STRING orJSON value.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQL ARRAY orARRAY value.
JSON_REMOVE Produces JSON with the specified JSON data removed.
JSON_SET Inserts or replaces JSON data.
JSON_STRIP_NULLS Removes JSON nulls from JSON objects and JSON arrays.
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 SQLSTRING value.
JSON_VALUE_ARRAY Extracts a JSON array of scalar values and converts it to a SQLARRAY 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 aJSON value.
STRING (JSON) Converts a JSON string to a SQL STRING value.
TO_JSON Converts a SQL value to a JSON value.
TO_JSON_STRING Converts a SQL value to a JSON-formatted STRING value.

BOOL

BOOL(json_expr)

Description

Converts a JSON boolean to a SQL BOOL value.

Arguments:

JSON 'true'

If the JSON value isn't 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 isn't 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

FLOAT64

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

Description

Converts a JSON number to a SQL FLOAT64 value.

Arguments:

JSON '9.8'

If the JSON value isn't a number, an error is produced. If the expression is a SQL NULL, the function returns SQL NULL.

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 isn't 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 can't 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;

INT64

INT64(json_expr)

Description

Converts a JSON number to a SQL INT64 value.

Arguments:

JSON '999'

If the JSON value isn't a number, or the JSON number isn't in the SQLINT64 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 isn't a number or can't 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

JSON_ARRAY

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

Description

Creates a JSON array from zero or more SQL values.

Arguments:

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_ARRAY_APPEND

JSON_ARRAY_APPEND(
  json_expr,
  json_path_value_pair[, ...]
  [, append_each_element => { TRUE | FALSE } ]
)

json_path_value_pair:
  json_path, value

Appends JSON data to the end of a JSON array.

Arguments:

JSON '["a", "b", "c"]'

Details:

Return type

JSON

Examples

In the following example, path $ is matched and appends 1.

SELECT JSON_ARRAY_APPEND(JSON '["a", "b", "c"]', '$', 1) AS json_data

/*-----------------*
 | json_data       |
 +-----------------+
 | ["a","b","c",1] |
 *-----------------*/

In the following example, append_each_element defaults to TRUE, so[1, 2] is appended as individual elements.

SELECT JSON_ARRAY_APPEND(JSON '["a", "b", "c"]', '$', [1, 2]) AS json_data

/*-------------------*
 | json_data         |
 +-------------------+
 | ["a","b","c",1,2] |
 *-------------------*/

In the following example, append_each_element is FALSE, so[1, 2] is appended as one element.

SELECT JSON_ARRAY_APPEND(
  JSON '["a", "b", "c"]',
  '$', [1, 2],
  append_each_element=>FALSE) AS json_data

/*---------------------*
 | json_data           |
 +---------------------+
 | ["a","b","c",[1,2]] |
 *---------------------*/

In the following example, append_each_element is FALSE, so[1, 2] and [3, 4] are each appended as one element.

SELECT JSON_ARRAY_APPEND(
  JSON '["a", ["b"], "c"]',
  '$[1]', [1, 2],
  '$[1][1]', [3, 4],
  append_each_element=>FALSE) AS json_data

/*-----------------------------*
 | json_data                   |
 +-----------------------------+
 | ["a",["b",[1,2,[3,4]]],"c"] |
 *-----------------------------*/

In the following example, the first path $[1] appends [1, 2] as single elements, and then the second path $[1][1] isn't a valid path to an array, so the second operation is ignored.

SELECT JSON_ARRAY_APPEND(
  JSON '["a", ["b"], "c"]',
  '$[1]', [1, 2],
  '$[1][1]', [3, 4]) AS json_data

/*---------------------*
 | json_data           |
 +---------------------+
 | ["a",["b",1,2],"c"] |
 *---------------------*/

In the following example, path $.a is matched and appends 2.

SELECT JSON_ARRAY_APPEND(JSON '{"a": [1]}', '$.a', 2) AS json_data

/*-------------*
 | json_data   |
 +-------------+
 | {"a":[1,2]} |
 *-------------*/

In the following example, a value is appended into a JSON null.

SELECT JSON_ARRAY_APPEND(JSON '{"a": null}', '$.a', 10)

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

In the following example, path $.a isn't an array, so the operation is ignored.

SELECT JSON_ARRAY_APPEND(JSON '{"a": 1}', '$.a', 2) AS json_data

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

In the following example, path $.b doesn't exist, so the operation is ignored.

SELECT JSON_ARRAY_APPEND(JSON '{"a": 1}', '$.b', 2) AS json_data

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

JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(
  json_expr,
  json_path_value_pair[, ...]
  [, insert_each_element => { TRUE | FALSE } ]
)

json_path_value_pair:
  json_path, value

Produces a new JSON value that's created by inserting JSON data into a JSON array.

Arguments:

JSON '["a", "b", "c"]'

Details:

Return type

JSON

Examples

In the following example, path $[1] is matched and inserts 1.

SELECT JSON_ARRAY_INSERT(JSON '["a", ["b", "c"], "d"]', '$[1]', 1) AS json_data

/*-----------------------*
 | json_data             |
 +-----------------------+
 | ["a",1,["b","c"],"d"] |
 *-----------------------*/

In the following example, path $[1][0] is matched and inserts 1.

SELECT JSON_ARRAY_INSERT(JSON '["a", ["b", "c"], "d"]', '$[1][0]', 1) AS json_data

/*-----------------------*
 | json_data             |
 +-----------------------+
 | ["a",[1,"b","c"],"d"] |
 *-----------------------*/

In the following example, insert_each_element defaults to TRUE, so[1, 2] is inserted as individual elements.

SELECT JSON_ARRAY_INSERT(JSON '["a", "b", "c"]', '$[1]', [1, 2]) AS json_data

/*-------------------*
 | json_data         |
 +-------------------+
 | ["a",1,2,"b","c"] |
 *-------------------*/

In the following example, insert_each_element is FALSE, so [1, 2] is inserted as one element.

SELECT JSON_ARRAY_INSERT(
  JSON '["a", "b", "c"]',
  '$[1]', [1, 2],
  insert_each_element=>FALSE) AS json_data

/*---------------------*
 | json_data           |
 +---------------------+
 | ["a",[1,2],"b","c"] |
 *---------------------*/

In the following example, path $[7] is larger than the length of the matched array, so the array is extended with JSON nulls and "e" is inserted at the end of the array.

SELECT JSON_ARRAY_INSERT(JSON '["a", "b", "c", "d"]', '$[7]', "e") AS json_data

/*--------------------------------------*
 | json_data                            |
 +--------------------------------------+
 | ["a","b","c","d",null,null,null,"e"] |
 *--------------------------------------*/

In the following example, path $.a is an object, so the operation is ignored.

SELECT JSON_ARRAY_INSERT(JSON '{"a": {}}', '$.a[0]', 2) AS json_data

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

In the following example, path $ doesn't specify a valid array position, so the operation is ignored.

SELECT JSON_ARRAY_INSERT(JSON '[1, 2]', '$', 3) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | [1,2]     |
 *-----------*/

In the following example, a value is inserted into a JSON null.

SELECT JSON_ARRAY_INSERT(JSON '{"a": null}', '$.a[2]', 10) AS json_data

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

In the following example, the operation is ignored because you can't insert data into a JSON number.

SELECT JSON_ARRAY_INSERT(JSON '1', '$[0]', 'r1') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | 1         |
 *-----------*/

JSON_EXTRACT(json_string_expr, json_path)

JSON_EXTRACT(json_expr, json_path)

Description

Extracts a JSON value and converts it to a SQL JSON-formatted STRING or JSON value. This function uses single quotes and brackets to escape invalidJSONPath characters in JSON keys. For example: ['a.b'].

Arguments:

'{"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 '{"class": {"students": [{"name": "Jane"}]}}'

Extracts a JSON null when a JSON null is encountered. 

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

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

Examples

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

SELECT
  JSON_EXTRACT(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_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

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

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

JSON_EXTRACT_ARRAY(json_expr[, json_path])

Description

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

Arguments:

'["a", "b", {"key": "c"}]'  
JSON '["a", "b", {"key": "c"}]'

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

Examples

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

SELECT JSON_EXTRACT_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_EXTRACT_ARRAY('[1,2,3]') AS string_array;

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

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

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

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

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

JSON_EXTRACT_SCALAR(json_expr[, json_path])

Description

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

Arguments:

'{"name": "Jane", "age": "6"}'  
JSON '{"name": "Jane", "age": "6"}'

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, age is extracted.

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

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

The following example compares how results are returned for the JSON_EXTRACTand 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_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

JSON_EXTRACT_STRING_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:

Arguments:

'["apples", "oranges", "grapes"]'  
JSON '["apples", "oranges", "grapes"]'

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:

Return type

ARRAY<STRING>

Examples

This extracts items in JSON to a string array:

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

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

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

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

JSON_KEYS

JSON_KEYS(
  json_expr
  [, max_depth ]
  [, mode => { 'strict' | 'lax' | 'lax recursive' } ]
)

Description

Extracts unique JSON keys from a JSON expression.

Arguments:

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

Details:

Return type

ARRAY<STRING>

Examples

In the following example, there are no arrays, so all keys are returned.

SELECT JSON_KEYS(JSON '{"a": {"b":1}}') AS json_keys

/*-----------*
 | json_keys |
 *-----------*
 | [a, a.b]  |
 *-----------*/

In the following example, max_depth is set to 1 so "a.b" isn't included.

SELECT JSON_KEYS(JSON '{"a": {"b":1}}', 1) AS json_keys

/*-----------*
 | json_keys |
 *-----------*
 | [a]       |
 *-----------*/

In the following example, json_expr contains an array. Because the mode isstrict, keys inside the array are excluded.

SELECT JSON_KEYS(JSON '{"a":[{"b":1}, {"c":2}], "d":3}') AS json_keys

/*-----------*
 | json_keys |
 *-----------*
 | [a, d]    |
 *-----------*/

In the following example, json_expr contains an array. Because the mode islax, keys inside the array are included.

SELECT JSON_KEYS(
  JSON '{"a":[{"b":1}, {"c":2}], "d":3}',
  mode => "lax") as json_keys

/*------------------*
 | json_keys        |
 *------------------*
 | [a, a.b, a.c, d] |
 *------------------*/

In the following example, json_expr contains consecutively nested arrays. Because the mode is lax, keys inside the consecutively nested arrays aren't included.

SELECT JSON_KEYS(JSON '{"a":[[{"b":1}]]}', mode => "lax") as json_keys

/*-----------*
 | json_keys |
 *-----------*
 | [a]       |
 *-----------*/

In the following example, json_expr contains consecutively nested arrays. Because the mode is lax recursive, every key is returned.

SELECT JSON_KEYS(JSON '{"a":[[{"b":1}]]}', mode => "lax recursive") as json_keys

/*-----------*
 | json_keys |
 *-----------*
 | [a, a.b]  |
 *-----------*/

In the following example, json_expr contains multiple arrays. Because the arrays aren't consecutively nested and the mode is lax, keys inside the arrays are included.

SELECT JSON_KEYS(JSON '{"a":[{"b":[{"c":1}]}]}', mode => "lax") as json_keys

/*-----------------*
 | json_keys       |
 *-----------------*
 | [a, a.b, a.b.c] |
 *-----------------*/

In the following example, json_expr contains both consecutively nested and single arrays. Because the mode is lax, keys inside the consecutively nested arrays are excluded.

SELECT JSON_KEYS(JSON '{"a":[{"b":[[{"c":1}]]}]}', mode => "lax") as json_keys

/*-----------*
 | json_keys |
 *-----------*
 | [a, a.b]  |
 *-----------*/

In the following example, json_expr contains both consecutively nested and single arrays. Because the mode is lax recursive, all keys are included.

SELECT JSON_KEYS(
  JSON '{"a":[{"b":[[{"c":1}]]}]}', mode => "lax recursive") as json_keys

/*-----------------*
 | json_keys       |
 *-----------------*
 | [a, a.b, a.b.c] |
 *-----------------*/

JSON_OBJECT

Signature 1

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

Description

Creates a JSON object, using key-value pairs.

Arguments:

Details:

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 can't 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:

Details:

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 can't be NULL.
SELECT JSON_OBJECT(CAST(NULL AS ARRAY<STRING>), [10, 20]) AS json_data

-- Error: The values array can't 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 orJSON value. This function uses double quotes to escape invalidJSONPath characters in JSON keys. For example: "a.b".

Arguments:

'{"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 '{"class": {"students": [{"name": "Jane"}]}}'

Extracts a JSON null when a JSON null is encountered. 

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

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

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"}] |
 *------------------------------------*/

In the following examples, the JSON data is extracted in lax mode. Because the keyword lax is included in the JSONPath, JSON arrays are automatically unwrapped.

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": [{"name": "Jane"}]}}',
    'lax $.class.students.name') AS student_names_lax;

/*-------------------*
 | student_names_lax |
 *-------------------*
 | ["Jane"]          |
 *-------------------*/

SELECT
  JSON_QUERY(
    JSON '[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]',
    'lax $.class.students.name') AS student_names_lax;

/*-------------------*
 | student_names_lax |
 *-------------------*
 | ["Joe","Jamie"]   |
 *-------------------*/

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}',
    'lax $.class.students.name') AS student_names_lax;

/*-------------------*
 | student_names_lax |
 *-------------------*
 | ["Jamie"]         |
 *-------------------*/

In the following examples, the JSON data is extracted in lax recursive mode. Because the keyword lax recursive is included in the JSONPath, JSON arrays are unwrapped until a non-array type is found.

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": [{"name": "Jane"}]}}',
    'lax recursive $.class.students.name') AS student_names_lax_recursive;

/*-----------------------------*
 | student_names_lax_recursive |
 *-----------------------------*
 | ["Jane"]                    |
 *-----------------------------*/

SELECT
  JSON_QUERY(
    JSON '[[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]]',
    'lax recursive $.class.students.name') AS student_names_lax_recursive;

/*-----------------------------*
 | student_names_lax_recursive |
 *-----------------------------*
 | ["Joe","Jamie"]             |
 *-----------------------------*/

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}',
    'lax recursive $.class.students.name') AS student_names_lax_recursive;

/*-----------------------------*
 | student_names_lax_recursive |
 *-----------------------------*
 | ["John","Jamie"]            |
 *-----------------------------*/

In the following examples, the keywords lax and lax recursive indicate that non-array types should be wrapped into arrays of size 1 before matching. The modes lax and lax recursive behave identically for wrapping arrays.

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": {"name": "Jane"}}}',
    'lax $.class[0].students[0].name') AS student_names_lax,
  JSON_QUERY(
    JSON '{"class": {"students": {"name": "Jane"}}}',
    'lax recursive $.class[0].students[0].name') AS student_names_lax_recursive;

/*-------------------*-----------------------------*
 | student_names_lax | student_names_lax_recursive |
 *-------------------*-----------------------------*
 | ["Jane"]          | ["Jane"]                    |
 *-------------------*-----------------------------*/

SELECT
  JSON_QUERY(
    JSON '[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]',
    'lax $.class[0].students[0].name') AS student_names_lax,
  JSON_QUERY(
    JSON '[{"class": {"students": [{"name": "Joe"}, {"name": "Jamie"}]}}]',
    'lax recursive $.class[0].students[0].name') AS student_names_lax_recursive;

/*-------------------*-----------------------------*
 | student_names_lax | student_names_lax_recursive |
 *-------------------*-----------------------------*
 | ["Joe"]           | ["Joe"]                     |
 *-------------------*-----------------------------*/

SELECT
  JSON_QUERY(
    JSON '{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}',
    'lax $.class[0].students[0].name') AS student_names_lax,
  JSON_QUERY(
    JSON '{"class": {"students": [[{"name": "John"}], {"name": "Jamie"}]}}',
    'lax recursive $.class[0].students[0].name') AS student_names_lax_recursive;

/*-------------------*-----------------------------*
 | student_names_lax | student_names_lax_recursive |
 *-------------------*-----------------------------*
 | ["John"]          | ["John"]                    |
 *-------------------*-----------------------------*/

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> orARRAY<JSON> value. In addition, this function uses double quotes to escape invalidJSONPath characters in JSON keys. For example: "a.b".

Arguments:

'["a", "b", {"key": "c"}]'  
JSON '["a", "b", {"key": "c"}]'

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

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

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

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

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

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

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

JSON_REMOVE

JSON_REMOVE(json_expr, json_path[, ...])

Produces a new SQL JSON value with the specified JSON data removed.

Arguments:

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

Details:

Return type

JSON

Examples

In the following example, the path $[1] is matched and removes["b", "c"].

SELECT JSON_REMOVE(JSON '["a", ["b", "c"], "d"]', '$[1]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | ["a","d"] |
 *-----------*/

You can use the field access operator to pass JSON data into this function. For example:

WITH T AS (SELECT JSON '{"a": {"b": 10, "c": 20}}' AS data)
SELECT JSON_REMOVE(data.a, '$.b') AS json_data FROM T

/*-----------*
 | json_data |
 +-----------+
 | {"c":20}  |
 *-----------*/

In the following example, the first path $[1] is matched and removes["b", "c"]. Then, the second path $[1] is matched and removes "d".

SELECT JSON_REMOVE(JSON '["a", ["b", "c"], "d"]', '$[1]', '$[1]') AS json_data

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

The structure of an empty array is preserved when all elements are deleted from it. For example:

SELECT JSON_REMOVE(JSON '["a", ["b", "c"], "d"]', '$[1]', '$[1]', '$[0]') AS json_data

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

In the following example, the path $.a.b.c is matched and removes the"c":"d" key-value pair from the JSON object.

SELECT JSON_REMOVE(JSON '{"a": {"b": {"c": "d"}}}', '$.a.b.c') AS json_data

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

In the following example, the path $.a.b is matched and removes the"b": {"c":"d"} key-value pair from the JSON object.

SELECT JSON_REMOVE(JSON '{"a": {"b": {"c": "d"}}}', '$.a.b') AS json_data

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

In the following example, the path $.b isn't valid, so the operation makes no changes.

SELECT JSON_REMOVE(JSON '{"a": 1}', '$.b') AS json_data

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

In the following example, path $.a.b and $.b don't exist, so those operations are ignored, but the others are processed.

SELECT JSON_REMOVE(JSON '{"a": [1, 2, 3]}', '$.a[0]', '$.a.b', '$.b', '$.a[0]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | {"a":[3]} |
 *-----------*/

If you pass in $ as the path, an error is produced. For example:

-- Error: The JSONPath can't be '$'
SELECT JSON_REMOVE(JSON '{}', '$') AS json_data

In the following example, the operation is ignored because you can't remove data from a JSON null.

SELECT JSON_REMOVE(JSON 'null', '$.a.b') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | null      |
 *-----------*/

JSON_SET

JSON_SET(
  json_expr,
  json_path_value_pair[, ...]
  [, create_if_missing => { TRUE | FALSE } ]
)

json_path_value_pair:
  json_path, value

Produces a new SQL JSON value with the specified JSON data inserted or replaced.

Arguments:

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

Details:

Return type

JSON

Examples

In the following example, the path $ matches the entire JSON value and replaces it with {"b": 2, "c": 3}.

SELECT JSON_SET(JSON '{"a": 1}', '$', JSON '{"b": 2, "c": 3}') AS json_data

/*---------------*
 | json_data     |
 +---------------+
 | {"b":2,"c":3} |
 *---------------*/

In the following example, create_if_missing is FALSE and the path $.bdoesn't exist, so the set operation is ignored.

SELECT JSON_SET(
  JSON '{"a": 1}',
  "$.b", 999,
  create_if_missing => false) AS json_data

/*------------*
 | json_data  |
 +------------+
 | '{"a": 1}' |
 *------------*/

In the following example, create_if_missing is TRUE and the path $.aexists, so the value is replaced.

SELECT JSON_SET(
  JSON '{"a": 1}',
  "$.a", 999,
  create_if_missing => false) AS json_data

/*--------------*
 | json_data    |
 +--------------+
 | '{"a": 999}' |
 *--------------*/

In the following example, the path $.a is matched, but $.a.b doesn't exist, so the new path and the value are inserted.

SELECT JSON_SET(JSON '{"a": {}}', '$.a.b', 100) AS json_data

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

In the following example, the path prefix $ points to a JSON null, so the remainder of the path is created for the value 100.

SELECT JSON_SET(JSON 'null', '$.a.b', 100) AS json_data

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

In the following example, the path $.a.c implies that the value at $.a is a JSON object but it's not. This part of the operation is ignored, but the other parts of the operation are completed successfully.

SELECT JSON_SET(
  JSON '{"a": 1}',
  '$.b', 2,
  '$.a.c', 100,
  '$.d', 3) AS json_data

/*---------------------*
 | json_data           |
 +---------------------+
 | {"a":1,"b":2,"d":3} |
 *---------------------*/

In the following example, the path $.a[2] implies that the value for $.a is an array, but it's not, so the operation is ignored for that value.

SELECT JSON_SET(
  JSON '{"a": 1}',
  '$.a[2]', 100,
  '$.b', 2) AS json_data

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

In the following example, the path $[1] is matched and replaces the array element value with foo.

SELECT JSON_SET(JSON '["a", ["b", "c"], "d"]', '$[1]', "foo") AS json_data

/*-----------------*
 | json_data       |
 +-----------------+
 | ["a","foo","d"] |
 *-----------------*/

In the following example, the path $[1][0] is matched and replaces the array element value with foo.

SELECT JSON_SET(JSON '["a", ["b", "c"], "d"]', '$[1][0]', "foo") AS json_data

/*-----------------------*
 | json_data             |
 +-----------------------+
 | ["a",["foo","c"],"d"] |
 *-----------------------*/

In the following example, the path prefix $ points to a JSON null, so the remainder of the path is created. The resulting array is padded with JSON nulls and appended with foo.

SELECT JSON_SET(JSON 'null', '$[0][3]', "foo")

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

In the following example, the path $[1] is matched, the matched array is extended since $[1][4] is larger than the existing array, and then foo is inserted in the array.

SELECT JSON_SET(JSON '["a", ["b", "c"], "d"]', '$[1][4]', "foo") AS json_data

/*-------------------------------------*
 | json_data                           |
 +-------------------------------------+
 | ["a",["b","c",null,null,"foo"],"d"] |
 *-------------------------------------*/

In the following example, the path $[1][0][0] implies that the value of$[1][0] is an array, but it isn't, so the operation is ignored.

SELECT JSON_SET(JSON '["a", ["b", "c"], "d"]', '$[1][0][0]', "foo") AS json_data

/*---------------------*
 | json_data           |
 +---------------------+
 | ["a",["b","c"],"d"] |
 *---------------------*/

In the following example, the path $[1][2] is larger than the length of the matched array. The array length is extended and the remainder of the path is recursively created. The operation continues to the path $[1][2][1]and inserts foo.

SELECT JSON_SET(JSON '["a", ["b", "c"], "d"]', '$[1][2][1]', "foo") AS json_data

/*----------------------------------*
 | json_data                        |
 +----------------------------------+
 | ["a",["b","c",[null,"foo"]],"d"] |
 *----------------------------------*/

In the following example, because the JSON object is empty, key b is inserted, and the remainder of the path is recursively created.

SELECT JSON_SET(JSON '{}', '$.b[2].d', 100) AS json_data

/*-----------------------------*
 | json_data                   |
 +-----------------------------+
 | {"b":[null,null,{"d":100}]} |
 *-----------------------------*/

In the following example, multiple values are set.

SELECT JSON_SET(
  JSON '{"a": 1, "b": {"c":3}, "d": [4]}',
  '$.a', 'v1',
  '$.b.e', 'v2',
  '$.d[2]', 'v3') AS json_data

/*---------------------------------------------------*
 | json_data                                         |
 +---------------------------------------------------+
 | {"a":"v1","b":{"c":3,"e":"v2"},"d":[4,null,"v3"]} |
 *---------------------------------------------------*/

JSON_STRIP_NULLS

JSON_STRIP_NULLS(
  json_expr
  [, json_path ]
  [, include_arrays => { TRUE | FALSE } ]
  [, remove_empty => { TRUE | FALSE } ]
)

Recursively removes JSON nulls from JSON objects and JSON arrays.

Arguments:

JSON '{"a": null, "b": "c"}'

Details:

Return type

JSON

Examples

In the following example, all JSON nulls are removed.

SELECT JSON_STRIP_NULLS(JSON '{"a": null, "b": "c"}') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | {"b":"c"} |
 *-----------*/

In the following example, all JSON nulls are removed from a JSON array.

SELECT JSON_STRIP_NULLS(JSON '[1, null, 2, null]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | [1,2]     |
 *-----------*/

In the following example, include_arrays is set as FALSE so that JSON nulls aren't removed from JSON arrays.

SELECT JSON_STRIP_NULLS(JSON '[1, null, 2, null]', include_arrays=>FALSE) AS json_data

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

In the following example, remove_empty is omitted and defaults toFALSE, and the empty structures are retained.

SELECT JSON_STRIP_NULLS(JSON '[1, null, 2, null, [null]]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | [1,2,[]]  |
 *-----------*/

In the following example, remove_empty is set as TRUE, and the empty structures are removed.

SELECT JSON_STRIP_NULLS(
  JSON '[1, null, 2, null, [null]]',
  remove_empty=>TRUE) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | [1,2]     |
 *-----------*/

In the following examples, remove_empty is set as TRUE, and the empty structures are removed. Because no JSON data is left the function returns JSON null.

SELECT JSON_STRIP_NULLS(JSON '{"a": null}', remove_empty=>TRUE) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | null      |
 *-----------*/

SELECT JSON_STRIP_NULLS(JSON '{"a": [null]}', remove_empty=>TRUE) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | null      |
 *-----------*/

In the following example, empty structures are removed for JSON objects, but not JSON arrays.

SELECT JSON_STRIP_NULLS(
  JSON '{"a": {"b": {"c": null}}, "d": [null], "e": [], "f": 1}',
  include_arrays=>FALSE,
  remove_empty=>TRUE) AS json_data

/*---------------------------*
 | json_data                 |
 +---------------------------+
 | {"d":[null],"e":[],"f":1} |
 *---------------------------*/

In the following example, empty structures are removed for both JSON objects, and JSON arrays.

SELECT JSON_STRIP_NULLS(
  JSON '{"a": {"b": {"c": null}}, "d": [null], "e": [], "f": 1}',
  remove_empty=>TRUE) AS json_data

/*-----------*
 | json_data |
 +-----------+
 | {"f":1}   |
 *-----------*/

In the following example, because no JSON data is left, the function returns a JSON null.

SELECT JSON_STRIP_NULLS(JSON 'null') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | null      |
 *-----------*/

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 '{"name": "sky", "color": "blue"}'

If this expression is SQL NULL, the function returns SQL NULL. If the extracted JSON value isn't a valid JSON type, an error is produced.

Return type

STRING

Examples

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

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

JSON_VALUE

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:

Arguments:

'{"name": "Jakob", "age": "6"}'  
JSON '{"name": "Jane", "age": "6"}'

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_QUERYand 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 SQLARRAY<STRING> value. In addition, this function:

Arguments:

'["apples", "oranges", "grapes"]'  
JSON '["apples", "oranges", "grapes"]'

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:

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 theJSON_QUERY_ARRAY and JSON_VALUE_ARRAY functions.

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

LAX_BOOL

LAX_BOOL(json_expr)

Description

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

Arguments:

JSON 'true'

Details:

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's 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 '9.8'

Details:

Conversion rules

From JSON type To SQL FLOAT64
boolean NULL
string If the JSON string represents a JSON number, parses it as a BIGNUMERIC value, and then safe casts the result as aFLOAT64 value. If the JSON string can't be converted, returns NULL.
number Casts the JSON number as aFLOAT64 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 '999'

Details:

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 BIGNUMERIC value, and then safe casts the results as anINT64 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 '"name"'

Details:

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:

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

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

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:

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

STRING

STRING(json_expr)

Description

Converts a JSON string to a SQL STRING value.

Arguments:

JSON '"purple"'

If the JSON value isn't 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 isn't 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

TO_JSON

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

Description

Converts a SQL value to a JSON value.

Arguments:

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 isn't 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}                   |
 *------------------------------*/

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

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

Arguments:

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

Differences between the JSON and JSON-formatted STRING types

Many JSON functions accept two input types:

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

Non-validation of STRING inputs

The following STRING is invalid JSON because it's 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 isn't 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 '}'

In the following examples, duplicated keys aren't removed when using a JSON-formatted string. Similarly, keys order is preserved. For the JSONtype, 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 nullvalue is extracted as a SQL NULL value.

When using a JSON type in a JSON function, a JSON null value returns a JSONnull 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:

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 (TO_JSON_STRING only) 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"
INT64 (TO_JSON only) 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 can't be stored in JSON without loss of precision, the function fails. Otherwise, the value is encoded as a number. If the stringify_wide_numbers isn't used or isFALSE, numeric values outside of the `FLOAT64` type domain aren't encoded as strings, but are stored as JSON numbers. If a numerical value can't 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"
INTERVAL string SQL input: INTERVAL '10:20:30.52' HOUR TO SECOND JSON output: "PT10H20M30.52S" SQL input: INTERVAL 1 SECOND JSON output: "PT1S" INTERVAL -25 MONTH JSON output: "P-2Y-1M" INTERVAL '1 5:30' DAY TO MINUTE JSON output: "P1DT5H30M"
NUMERICBIGNUMERIC (TO_JSON_STRING only) number or string Encoded as a number when the value is in the range of [-253, 253] and has no fractional part. A value outside of this range is encoded as a string. SQL input: -1 JSON output: -1 SQL input: 0 JSON output: 0 SQL input: 9007199254740993 JSON output: "9007199254740993" SQL input: 123.56 JSON output: "123.56"
NUMERICBIGNUMERIC (TO_JSON only) number or string If the stringify_wide_numbers argument is TRUE and the value is outside of the FLOAT64 type domain, it's 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 asInfinity, -Infinity, and NaN. Otherwise, this value is encoded as a number. SQL input: 1.0 JSON output: 1 SQL input: 9007199254740993 JSON output: 9007199254740993 SQL input: "+inf" JSON output: "Infinity" SQL input: "-inf" JSON output: "-Infinity" SQL input: "NaN" JSON output: "NaN"
STRING string Encoded as a string, escaped according to the JSON standard. Specifically, ", \, and the control characters from U+0000 to U+001F are escaped. SQL input: "abc" JSON output: "abc" SQL input: "\"abc\"" JSON output: "\"abc\""
BYTES string Uses RFC 4648 Base64 data encoding. SQL input: b"Google" JSON output: "R29vZ2xl"
DATE string SQL input: DATE '2017-03-06' JSON output: "2017-03-06"
TIMESTAMP string Encoded as ISO 8601 date and time, where T separates the date and time and Z (Zulu/UTC) represents the time zone. SQL input: TIMESTAMP '2017-03-06 12:34:56.789012' JSON output: "2017-03-06T12:34:56.789012Z"
DATETIME string Encoded as ISO 8601 date and time, where T separates the date and time. SQL input: DATETIME '2017-03-06 12:34:56.789012' JSON output: "2017-03-06T12:34:56.789012"
TIME string Encoded as ISO 8601 time. SQL input: TIME '12:34:56.789012' JSON output: "12:34:56.789012"
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. 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 fromU+0000 to U+001F are escaped. SQL input: STRUCT(12 AS purchases, TRUE AS inStock) JSON output: {"inStock": true,"purchases":12}
RANGE range Encoded as an object with a start and end value. Any unbounded part of the range is represented asnull. SQL input: RANGE '[2024-07-24, 2024-07-25)' JSON output: {"start":"2024-07-24","end":"2024-07-25"} SQL input: RANGE '[2024-07-24 10:00:00, UNBOUNDED)' JSON output: {"start":"2024-07-24T10:00:00","end":null}

JSONPath format

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

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

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

Operators for JSONPath

The JSONPath format supports these operators:

Operator Description Examples
$ 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"

Modes for JSONPath

Some JSON functions that take a JSONPath let you specify a mode that indicates how the JSONPath matches the JSON data structure. For example, the JSONPath could be lax $.class.students. The following modes are supported:

Mode Description Example JSONPath
strict (default) The JSONPath must structurally match the JSON data "$.class.students"
lax Implicitly adapts the path to the structure of the JSON data. If the JSONPath doesn't exactly match the JSON data, then the following rules apply: If the JSONPath operator is an object, then it unwraps a single level of array. If the JSONPath operator is an array and the JSON value isn't, then it wraps JSON values in an array of size 1 . A single value is equivalent to an array of size 1 containing the value. "lax $.class.students"
lax recursive In addition to lax behavior, JSONPath unwraps consecutive arrays until a non-array type is encountered. "lax recursive $.class.students"

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-05-05 UTC.