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. Ifjson_path
returns a JSONnull
, this is converted into a SQLNULL
.
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. Ifjson_path
returns a JSONnull
, this is converted into a SQLNULL
.
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. Ifjson_path
returns a JSONnull
, this is converted into a SQLNULL
.
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. Ifjson_path
returns a JSONnull
, this is converted into a SQLNULL
.
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 -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
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.