JSON functions in Standard SQL

BigQuery supports the following functions, which can retrieve and transform JSON data.

Function overview

The following functions use double quotes to escape invalid JSONPath characters: "a.b".

This behavior is consistent with the ANSI standard.

JSON function Description Return type
JSON_QUERY Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. JSON-formatted STRING
JSON_VALUE Extracts a scalar value. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the values. Returns a SQL NULL if a non-scalar value is selected. STRING
JSON_QUERY_ARRAY Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. ARRAY<JSON-formatted STRING>
JSON_VALUE_ARRAY Extracts an array of scalar values. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the values. Returns a SQL NULL if the selected value is not an array or not an array containing only scalar values. ARRAY<STRING>

Legacy JSON extraction functions

The following functions use single quotes and brackets to escape invalid JSONPath characters: ['a.b'].

While these functions are supported by BigQuery, we recommend using the functions in the previous table.

JSON function Description Return type
JSON_EXTRACT Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. JSON-formatted STRING
JSON_EXTRACT_SCALAR Extracts a scalar value. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the values. Returns a SQL NULL if a non-scalar value is selected. STRING
JSON_EXTRACT_ARRAY Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. ARRAY<JSON-formatted STRING>
JSON_EXTRACT_STRING_ARRAY Extracts an array of scalar values. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the values. Returns a SQL NULL if the selected value is not an array or not an array containing only scalar values. ARRAY<STRING>

Other JSON functions

JSON function Description Return type
TO_JSON_STRING Takes a SQL value and returns a JSON-formatted string representation of the value. JSON-formatted STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

Description

Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.

  • json_string_expr: A JSON-formatted string. For example:

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

    Extracts a SQL NULL when a JSON-formatted string "null" is encountered. For example:

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

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

If you want to include non-scalar values such as arrays in the extraction, then use JSON_EXTRACT. If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_EXTRACT_SCALAR.

Return type

A JSON-formatted STRING

Examples

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

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

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

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

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

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

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+

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

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

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

Description

Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.

  • json_string_expr: A JSON-formatted string. For example:

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

    Extracts a SQL NULL when a JSON-formatted string "null" is encountered. For example:

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

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

If you want to include non-scalar values such as arrays in the extraction, then use JSON_QUERY. If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_VALUE.

Return type

A JSON-formatted STRING

Examples

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

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

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

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

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

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

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+

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

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

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

Description

Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.

  • json_string_expr: A JSON-formatted string. For example:

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

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

    If json_path returns a JSON null or a non-scalar value (in other words, if json_path refers to an object or an array), then a SQL NULL is returned. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that the entire JSON-formatted string is analyzed.

If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_EXTRACT_SCALAR. If you want to include non-scalar values such as arrays in the extraction, then use JSON_EXTRACT.

Return type

STRING

Examples

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

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

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

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

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

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

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

+-------+
| hello |
+-------+
| world |
+-------+

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])

Description

Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.

  • json_string_expr: A JSON-formatted string. For example:

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

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

    If json_path returns a JSON null or a non-scalar value (in other words, if json_path refers to an object or an array), then a SQL NULL is returned. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that the entire JSON-formatted string is analyzed.

If you only want to extract scalar values such strings, numbers, and booleans, then use JSON_VALUE. If you want to include non-scalar values such as arrays in the extraction, then use JSON_QUERY.

Return type

STRING

Examples

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

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

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

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

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

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

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

+-------+
| hello |
+-------+
| world |
+-------+

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

Description

Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.

  • json_string_expr: A JSON-formatted string. For example:

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

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

    If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that the entire JSON-formatted string is analyzed.

Return type

ARRAY<JSON-formatted STRING>

Examples

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

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

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

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

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

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

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

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

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

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

  • If a JSONPath is invalid, an error is thrown.
  • If a JSON-formatted string is invalid, the output is NULL.
  • It is okay to have empty arrays in the JSON-formatted string.
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

JSON_QUERY_ARRAY(json_string_expr[, json_path])

Description

Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.

  • json_string_expr: A JSON-formatted string. For example:

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

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Return type

ARRAY<JSON-formatted STRING>

Examples

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

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

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

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

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

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

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

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

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

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

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

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| []     |
+--------+

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

Description

Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.

  • json_string_expr: A JSON-formatted string. For example:

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

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Return type

ARRAY<STRING>

Examples

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

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

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

+---------+
| hello   |
+---------+
| [world] |
+---------+

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

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

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not 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 is not 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 |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

Description

Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.

  • json_string_expr: A JSON-formatted string. For example:

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

  • json_path: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that all of the data is analyzed.

Return type

ARRAY<STRING>

Examples

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

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

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

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

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

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

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

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

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

These are equivalent:

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

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

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

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

+---------+
| hello   |
+---------+
| [world] |
+---------+

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

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

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| NULL   |
+--------+

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

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

Takes a SQL value and returns a JSON-formatted string representation of the value. The value must be a supported BigQuery data type. You can review the BigQuery data types that this function supports and their JSON encodings here.

This function supports an optional boolean parameter called pretty_print. If pretty_print is true, the returned value is formatted for easy readability.

Return type

A JSON-formatted STRING

Examples

Convert rows in a table to JSON-formatted strings.

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

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

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

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

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

JSON encodings

The following table includes common encodings that are used when a SQL value is encoded as JSON value with the TO_JSON_STRING function.

From SQL To JSON Examples
NULL

null

 SQL input: NULL
JSON output: null
BOOL boolean SQL input: TRUE
JSON output: true
SQL input: FALSE
JSON output: false
INT64

number or string

Encoded as a number when the value is in the range of [-253, 253], which is the range of integers that can be represented losslessly as IEEE 754 double-precision floating point numbers. A value outside of this range is encoded as a string.

SQL input: 9007199254740992
JSON output: 9007199254740992
SQL input: 9007199254740993
JSON output: "9007199254740993"
NUMERIC
BIGNUMERIC

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"
FLOAT64

number or string

+/-inf and NaN are encoded as Infinity, -Infinity, and NaN. Otherwise, this value is encoded as a number.

SQL input: 1.0
JSON output: 1
SQL input: 9007199254740993
JSON output: 9007199254740993
SQL input: "+inf"
JSON output: "Infinity"
SQL input: "-inf"
JSON output: "-Infinity"
SQL input: "NaN"
JSON output: "NaN"
STRING

string

Encoded as a string, escaped according to the JSON standard. Specifically, ", \, and the control characters from U+0000 to U+001F are escaped.

SQL input: "abc"
JSON output: "abc"
SQL input: "\"abc\""
JSON output: "\"abc\""
BYTES

string

Uses RFC 4648 Base64 data encoding.

 SQL input: b"Google"
JSON output: "R29vZ2xl"
DATE string SQL input: DATE '2017-03-06'
JSON output: "2017-03-06"
TIMESTAMP

string

Encoded as ISO 8601 date and time, where T separates the date and time and Z (Zulu/UTC) represents the time zone.

SQL input: TIMESTAMP '2017-03-06 12:34:56.789012'
JSON output: "2017-03-06T12:34:56.789012Z"
DATETIME

string

Encoded as ISO 8601 date and time, where T separates the date and time.

SQL input: DATETIME '2017-03-06 12:34:56.789012'
JSON output: "2017-03-06T12:34:56.789012"
TIME

string

Encoded as ISO 8601 time.

 SQL input: TIME '12:34:56.789012'
JSON output: "12:34:56.789012"
ARRAY

array

Can contain zero or more elements.

SQL input: ["red", "blue", "green"]
JSON output: ["red", "blue", "green"]
SQL input:[1, 2, 3]
JSON output:[1, 2, 3]
STRUCT

object

The object can contain zero or more key/value pairs. Each value is formatted according to its type.

For TO_JSON_STRING, a field and any duplicates of this field are included in the output string.

Anonymous fields are represented with "".

Invalid UTF-8 field names might result in unparseable JSON. String values are escaped according to the JSON standard. Specifically, ", \, and the control characters from U+0000 to U+001F are escaped.

SQL input: STRUCT(12 AS purchases, TRUE AS inStock)
JSON output: {"inStock": true,"purchases":12}

JSONPath

Most JSON functions pass in a json_string_expr and json_path parameter. The json_string_expr parameter passes in a JSON-formatted string, and the json_path parameter identifies the value or values you want to obtain from the JSON-formatted string.

The json_string_expr parameter must be a JSON string that is formatted like this:

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

You construct the json_path parameter using the JSONPath format. As part of this format, this parameter must start with a $ symbol, which refers to the outermost level of the JSON-formatted string. You can identify child values using dots. If the JSON object is an array, you can use brackets to specify the array index. If the keys contain $, dots, or brackets, refer to each JSON function for how to escape them.

JSONPath Description Example Result using the above json_string_expr
$ Root object or element "$" {"class":{"students":[{"name":"Jane"}]}}
. Child operator "$.class.students" [{"name":"Jane"}]
[] Subscript operator "$.class.students[0]" {"name":"Jane"}

A JSON functions returns NULL if the json_path parameter does not match a value in json_string_expr. If the selected value for a scalar function is not scalar, such as an object or an array, the function returns NULL.

If the JSONPath is invalid, the function raises an error.