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;
The above query produces the following result:
+-----------------------------------------------------------+
| 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;
The above query produces the following result:
+-----------------+
| 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;
The above query produces the following result:
+-------------------+
| second_student |
+-------------------+
| 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;
The above query produces the following result:
+------------------------------------+
| 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;
The above query produces the following result:
+-----------+-------------+----------+--------+
| 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 -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993"
|
NUMERIC | Same as -1 0 "9007199254740993" "123.56"
|
FLOAT64 | +/-inf and NaN are represented as
Infinity , -Infinity , and NaN ,
respectively.
Otherwise, the same as |
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:
|
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. The empty
array is represented as |
STRUCT | {"field_name1":field_value1,"field_name2":field_value2,...}
Where each { "field_name1": field_value1, "field_name2": field_value2, ... } Where each 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, |
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" |
| } |
|} |
+-----------------------+