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_format)
, which returns JSON values as STRINGs.
JSON_EXTRACT_SCALAR(json_string_expr,
json_path_format)
, 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_format
: The JSONpath format. This identifies the value or values you want to obtain from the JSON-formatted string. Ifjson_path_format
returns a JSONnull
, this is converted into a SQLNULL
.
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
Return type
STRING
s
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_format])
Description
Extracts an array from a JSON-formatted string.
json_string_expr
: A JSON-formatted string. For example:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: 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 |
+--------+
| [] |
+--------+
JSON_QUERY or JSON_VALUE
JSON_QUERY(json_string_expr, json_path_format)
,
which returns JSON values as STRINGs.
JSON_VALUE(json_string_expr, json_path_format)
,
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_format
: The JSONpath format. This identifies the value or values you want to obtain from the JSON-formatted string. Ifjson_path_format
returns a JSONnull
, this is converted into a SQLNULL
.
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.
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"}] |
+------------------------------------+
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 |
+-------+
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 -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993"
|
NUMERIC, BIGNUMERIC | 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 |
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
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 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 format
Most JSON functions pass in a json_string_expr
and json_path_format
parameter. The json_string_expr
parameter passes in a JSON-formatted
string, and the json_path_format
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_format
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_format
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.