JSON functions in Standard SQL

BigQuery supports functions that help you retrieve data stored in JSON-formatted strings and functions that help you transform data into JSON-formatted strings.

Function overview

JSON function Description
JSON_EXTRACT Extracts a JSON value, such as an array or object, or a JSON-formatted scalar value, such as a string, integer, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
JSON_QUERY Extracts a JSON value, such as an array or object, or a JSON-formatted scalar value, such as a string, integer, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.
JSON_EXTRACT_SCALAR Extracts a scalar value such as a string, integer, or boolean. Removes the outermost quotes and unescapes the values. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
JSON_VALUE Extracts a scalar value such as a string, integer, or boolean. Removes the outermost quotes and unescapes the values. If a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.
JSON_EXTRACT_ARRAY Extracts an array of JSON-formatted strings. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
TO_JSON_STRING Returns a JSON-formatted string representation of a value.

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

Description

Extracts a JSON value, such as an array or object, or a JSON-formatted scalar value, such as a string, integer, 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 value or values you want to obtain from the JSON-formatted string. If json_path returns a JSON null, this is converted into a SQL NULL.

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

Return type

A JSON-formatted STRING

Examples

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-formatted scalar value, such as a string, integer, 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 value or values you want to obtain from the JSON-formatted string. If json_path returns a JSON null, this is converted into a SQL NULL.

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

Return type

A JSON-formatted STRING

Examples

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 such as a string, integer, or boolean. Removes the outermost quotes and unescapes the return values. 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 value or values you want to obtain from the JSON-formatted string. If json_path returns a JSON null, this is converted into a SQL NULL.

If you only want to extract scalar values such strings, integers, and booleans, use JSON_EXTRACT_SCALAR. If you want to include non-scalar values such as arrays in the extraction, 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          |
+-----------+-------------+----------+------------+

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 such as a string, integer, or boolean. Removes the outermost quotes and unescapes the return values. 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 value or values you want to obtain from the JSON-formatted string. If json_path returns a JSON null, this is converted into a SQL NULL.

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

Return type

STRING

Examples

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;

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

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-formatted strings. 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 value or values you want to obtain from the JSON-formatted string. If this optional parameter is not provided, the JSONPath $ symbol is applied, which means that the entire JSON-formatted string is analyzed.

Return type

ARRAY<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 fruit 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, NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') as result;

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

-- If a key that does not exist is specified, 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 |
+--------+
| []     |
+--------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

Returns a JSON-formatted string representation of value. This function supports an optional boolean parameter called pretty_print. If pretty_print is true, the returned value is formatted for easy readability.

Input data type Returned value
NULL of any type null
BOOL true or false.
INT64

Same as CAST(value AS STRING) when 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. Values outside of this range are represented as quoted strings. For example:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 is greater than 253, so it is represented as a quoted string.
NUMERIC, BIGNUMERIC

Same as CAST(value AS STRING) when value is in the range of [-253, 253] and has no fractional part. Values outside of this range are represented as quoted strings. For example:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-inf and NaN are represented as Infinity, -Infinity, and NaN, respectively.

Otherwise, the same as CAST(value AS STRING).
STRING Quoted string value, escaped according to the JSON standard. Specifically, ", \, and the control characters from U+0000 to U+001F are escaped.
BYTES

Quoted RFC 4648 base64-escaped value. For example:

"R29vZ2xl" is the base64 representation of bytes b"Google"
DATE

Quoted date. For example:

"2017-03-06"
TIMESTAMP

Quoted ISO 8601 date-time, where T separates the date and time and Zulu/UTC represents the time zone. For example:

"2017-03-06T12:34:56.789012Z"
DATETIME

Quoted ISO 8601 date-time, where T separates the date and time. For example:

"2017-03-06T12:34:56.789012"
TIME

Quoted ISO 8601 time. For example:

"12:34:56.789012"
ARRAY

Array of zero or more elements. Each element is formatted according to its type.

Example without formatting: 

["red", "blue", "green"]

Example with formatting: 

[
  "red",
  "blue",
  "green"
]
STRUCT

Object that contains zero or more key/value pairs. Each value is formatted according to its type.

Example without formatting: 

{"colors":["red","blue"],"purchases":12,"inStock": true}

Example with formatting: 

{
  "color":[
    "red",
    "blue"
   ]
  "purchases":12,
  "inStock": true
}

Fields with duplicate names might result in unparseable JSON. Anonymous fields are represented with "". If a field is a non-empty array or object, elements/fields are indented to the appropriate level.

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.

Return type

JSON string representation of the value.

Examples

Convert rows in a table to JSON.

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 with formatting.

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             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

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.