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

The json_string_expr parameter must be a JSON-formatted string. For example:

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

The json_path_string_literal parameter identifies the value or values you want to obtain from the JSON-formatted string. You construct this 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 dot or bracket notation. If the JSON object is an array, you can use brackets to specify the array index.

JSONPath	Description
$	Root object or element
. or []	Child operator
[]	Subscript operator

Both functions return NULL if the json_path_string_literal parameter does not match a value in json_string_expr. If the selected value for JSON_EXTRACT_SCALAR is not scalar, such as an object or an array, the function returns NULL.

If the JSONPath is invalid, these functions raise an error.

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

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": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"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      |
+-----------+-------------+----------+--------+

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

Was this page helpful? Let us know how we did:

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

Last updated August 1, 2018.

JSON Functions in Standard SQL

JSON_EXTRACT or JSON_EXTRACT_SCALAR

TO_JSON_STRING

Send feedback about...