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.

JSON_EXTRACT or JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal), which returns JSON values as STRINGs.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal), which returns scalar JSON values as STRINGs.

Description

Extracts JSON values or JSON scalar values as strings.

json_string_expr: A JSON-formatted string. For example:
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path_string_literal: The JSONpath format. This identifies the value or values you want to obtain from the JSON-formatted string. If json_path_string_literal returns a JSON null, this is converted into a SQL NULL.

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.

Return type

STRINGs

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

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;

+-----------+-------------+----------+--------+
| 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 single quotes and brackets, [' ']. For example:

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

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_string_literal])

Description

Extracts an array from a JSON-formatted string.

json_string_expr: A JSON-formatted string. For example:
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path_string_literal: The JSONpath format. 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.

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.

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:

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

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

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

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

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

-- The queries above produce this 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] |
+---------+

These 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.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result

-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result

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

-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result

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

-- Empty arrays in JSON-formatted strings are supported.
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 pretty_print parameter. If pretty_print is present, 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 [-2⁵³, 2⁵³], 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 2⁵³, so it is represented as a quoted string.
NUMERIC	Same as `CAST(value AS STRING)` when `value` is in the range of [-2⁵³, 2⁵³] 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	`[elem1,elem2,...]`, where each `elem` is formatted according to the element type. Example with formatting: [ elem1, elem2, ... ] Where each elem is formatted according to the element type. The empty array is represented as `[]`.
STRUCT	`{"field_name1":field_value1,"field_name2":field_value2,...}` Where each `field_value` is formatted according to its type. Example with formatting: { "field_name1": field_value1, "field_name2": field_value2, ... } Where each `field_value` is formatted according to its type. If a `field_value` is a non-empty ARRAY or STRUCT, elements are indented to the appropriate level. The empty struct is represented as `{}`. Fields with duplicate names might result in unparseable JSON. 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.

Return type

JSON string representation of the value.

Examples

Convert rows in a table to JSON.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

The above query produces the following result:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Convert rows in a table to JSON with formatting.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

The above query produces the following result:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

JSONPath format

Most JSON functions pass in a json_string_expr and json_path_string_literal parameter. The json_string_expr parameter passes in a JSON-formatted string, and the json_path_string_literal 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_string_literal 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_string_literal 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.