Google Standard SQL supports the following functions, which can retrieve and transform JSON data.
Function overview
JSON extraction functions
The following functions use double quotes to escape invalid
JSONPath characters: "a.b"
.
This behavior is consistent with the ANSI standard.
JSON function | Description | Return type |
---|---|---|
JSON_QUERY |
Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. |
JSON-formatted STRING
or
JSON
|
JSON_VALUE |
Extracts a scalar value.
A scalar value can represent a string, number, or boolean.
Removes the outermost quotes and unescapes the values.
Returns a SQL NULL if a non-scalar value is selected.
|
STRING |
JSON_QUERY_ARRAY |
Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. |
ARRAY<JSON-formatted STRING>
or
ARRAY<JSON>
|
JSON_VALUE_ARRAY |
Extracts an array of scalar values. A scalar value can represent a
string, number, or boolean.
Removes the outermost quotes and unescapes the values.
Returns a SQL NULL if the selected value is not an array or
not an array containing only scalar values.
|
ARRAY<STRING> |
Other JSON functions
JSON function | Description | Return type |
---|---|---|
PARSE_JSON |
Takes a JSON-formatted string and returns a JSON value. | JSON |
TO_JSON |
Takes a SQL value and returns a JSON value. | JSON |
TO_JSON_STRING |
Takes a JSON value and returns a JSON-formatted string representation of the value. | JSON-formatted STRING |
JSON_QUERY
JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)
Description
Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:'{"class" : {"students" : [{"name" : "Jane"}]}}'
Extracts a SQL
NULL
when a JSON-formatted stringnull
is encountered. For example:SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
json_expr
: JSON. For example:JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
Extracts a JSON
null
when a JSONnull
is encountered.SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
json_path
: The JSONPath. This identifies the data that you want to obtain from the input.
Return type
json_string_expr
: A JSON-formattedSTRING
json_expr
:JSON
Examples
In the following example, JSON data is extracted and returned as JSON.
SELECT
JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
AS json_data;
+-----------------------------------+
| json_data |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL
JSON_VALUE
JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])
Description
Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:'{"class" : {"students" : [{"name" : "Jane"}]}}'
json_expr
: JSON. For example:JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
json_path
: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that all of the data is analyzed.If
json_path
returns a JSONnull
or a non-scalar value (in other words, ifjson_path
refers to an object or an array), then a SQLNULL
is returned.
Return type
STRING
Examples
In the following example, JSON data is extracted and returned as a scalar value.
SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;
+------------+
| scalar_age |
+------------+
| 6 |
+------------+
The following example compares how results are returned for the JSON_QUERY
and JSON_VALUE
functions.
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_age;
+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+------------+
SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;
+--------------------+------------+
| json_query | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL |
+--------------------+------------+
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_QUERY_ARRAY
JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])
Description
Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:'{"class" : {"students" : [{"name" : "Jane"}]}}'
json_expr
: JSON. For example:JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
json_path
: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that all of the data is analyzed.
Return type
json_string_expr
:ARRAY<JSON-formatted STRING>
json_expr
:ARRAY<JSON>
Examples
This extracts items in JSON to an array of JSON
values:
SELECT JSON_QUERY_ARRAY(
JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
) AS json_array;
+---------------------------------+
| json_array |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+
This extracts the items in a JSON-formatted string to a string array:
SELECT JSON_QUERY_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_QUERY_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_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;
+---------------------------------+
| string_array |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+
-- Strips the double quotes
SELECT ARRAY(
SELECT JSON_VALUE(string_element, '$')
FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;
+---------------------------+
| string_array |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+
This extracts only the items in the fruit
property to an array:
SELECT JSON_QUERY_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_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_QUERY_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 double quotes: " "
. For example:
SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;
+-----------+
| hello |
+-----------+
| ["world"] |
+-----------+
The following examples show how invalid requests and empty arrays are handled:
-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;
-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;
+--------+
| result |
+--------+
| [] |
+--------+
JSON_VALUE_ARRAY
JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])
Description
Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, 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_expr
: JSON. For example:JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
json_path
: The JSONPath. This identifies the data that you want to obtain from the input. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that all of the data is analyzed.
Caveats:
- A JSON
null
in the input array produces a SQLNULL
as the output for that JSONnull
. - If a JSONPath matches an array that contains scalar objects and a JSON
null
, then the output is an array of the scalar objects and a SQLNULL
.
Return type
ARRAY<STRING>
Examples
This extracts items in JSON to a string array:
SELECT JSON_VALUE_ARRAY(
JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
) AS string_array;
+---------------------------+
| string_array |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+
The following example compares how results are returned for the
JSON_QUERY_ARRAY
and JSON_VALUE_ARRAY
functions.
SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;
+-----------------------+-------------------+
| json_array | string_array |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+
This extracts the items in a JSON-formatted string to a string array:
-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;
+-----------------+
| string_array |
+-----------------+
| [foo, bar, baz] |
+-----------------+
This extracts a string array and converts it to an integer array:
SELECT ARRAY(
SELECT CAST(integer_element AS INT64)
FROM UNNEST(
JSON_VALUE_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array;
+---------------+
| integer_array |
+---------------+
| [1, 2, 3] |
+---------------+
These are equivalent:
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_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 double quotes: " "
. For example:
SELECT JSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
The following examples explore how invalid requests and empty arrays are handled:
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;
-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;
+--------+
| result |
+--------+
| [] |
+--------+
-- In the following query, the JSON null input is returned as a
-- SQL NULL in the output.
SELECT JSON_VALUE_ARRAY('["world", null, 1]') AS result;
+------------------+
| result |
+------------------+
| [world, NULL, 1] |
+------------------+
PARSE_JSON
PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])
Description
Takes a SQL STRING
value and returns a SQL JSON
value.
The STRING
value represents a string-formatted JSON value.
This function supports an optional mandatory-named argument called
wide_number_mode
that determines how to handle numbers that cannot be stored
in a JSON
value without the loss of precision. If used,
wide_number_mode
must include one of these values:
exact
: Only accept numbers that can be stored without loss of precision. If a number that cannot be stored without loss of precision is encountered, the function throws an error.round
: If a number that cannot be stored without loss of precision is encountered, attempt to round it to a number that can be stored without loss of precision. If the number cannot be rounded, the function throws an error.
If wide_number_mode
is not used, the function implicitly includes
wide_number_mode=>'exact'
. If a number appears in a JSON object or array,
the wide_number_mode
argument is applied to the number in the object or array.
Numbers from the following domains can be stored in JSON without loss of precision:
- 64-bit signed/unsigned integers, such as
INT64
FLOAT64
Return type
JSON
Examples
In the following example, a JSON-formatted string is converted to JSON
.
SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;
+--------------------------------+
| json_data |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+
The following queries fail because:
- The number that was passed in cannot be stored without loss of precision.
wide_number_mode=>'exact'
is used implicitly in the first query and explicitly in the second query.
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails
The following query rounds the number to a number that can be stored in JSON.
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;
+--------------------------------+
| json_data |
+--------------------------------+
| {"id":9.223372036854776e+20} |
+--------------------------------+
TO_JSON
TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])
Description
Takes a SQL value and returns a JSON value. The value must be a supported Google Standard SQL data type. You can review the Google Standard SQL data types that this function supports and their JSON encodings here.
This function supports an optional mandatory-named argument called
stringify_wide_numbers
.
- If this argument is
TRUE
, numeric values outside of theFLOAT64
type domain are encoded as strings. - If this argument is not used or is
FALSE
, numeric values outside of theFLOAT64
type domain are not encoded as strings, but are stored as JSON numbers. If a numerical value cannot be stored in JSON without loss of precision, an error is thrown.
The following numerical data types are affected by the
stringify_wide_numbers
argument:
INT64
NUMERIC
If one of these numerical data types appears in a container data type
such as an ARRAY
or STRUCT
, the stringify_wide_numbers
argument is
applied to the numerical data types in the container data type.
Return type
A JSON value
Examples
In the following example, the query converts rows in a table to JSON values.
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 TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;
+--------------------------------+
| json_objects |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
| {"coordinates":[30,40],"id":2} |
| {"coordinates":[50,60],"id":3} |
+--------------------------------+
In the following example, the query returns a large numerical value as a JSON string.
SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on
+--------------------+
| stringify_on |
+--------------------+
| "9007199254740993" |
+--------------------+
In the following example, both queries return a large numerical value as a JSON number.
SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off
+------------------+
| stringify_off |
+------------------+
| 9007199254740993 |
+------------------+
In the following example, only large numeric values are converted to JSON strings.
With T1 AS (
(SELECT 9007199254740993 AS id) UNION ALL
(SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;
+---------------------------+
| json_objects |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2} |
+---------------------------+
In this example, the values 9007199254740993
(INT64
)
and 2.1
(FLOAT64
) are converted
to the common supertype FLOAT64
, which is not
affected by the stringify_wide_numbers
argument.
With T1 AS (
(SELECT 9007199254740993 AS id) UNION ALL
(SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;
+------------------------------+
| json_objects |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1} |
+------------------------------+
TO_JSON_STRING
TO_JSON_STRING(json_expr)
Description
Takes a JSON
expression and returns a JSON-formatted string representation of
the value from the expression.
Return type
A JSON-formatted STRING
Example
Convert JSON to a JSON-formatted string.
SELECT TO_JSON_STRING(JSON '{"id":1, "coordinates":[10,20]}') AS json_string
+--------------------------------+
| json_string |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+
JSON encodings
The following table includes common encodings that are used when a
SQL value is encoded as JSON value with
the TO_JSON
function.
From SQL | To JSON | Examples |
---|---|---|
NULL |
null |
SQL input: NULL JSON output: null
|
BOOL | boolean |
SQL input: TRUE JSON output: true SQL input: FALSE JSON output: false |
INT64 |
number or string
If the
If the |
SQL input: 9007199254740992 JSON output: 9007199254740992 SQL input: 9007199254740993 JSON output: 9007199254740993 SQL input with stringify_wide_numbers=>TRUE: 9007199254740992 JSON output: 9007199254740992 SQL input with stringify_wide_numbers=>TRUE: 9007199254740993 JSON output: "9007199254740993" |
NUMERIC |
number or string
If the |
SQL input: -1 JSON output: -1 SQL input: 0 JSON output: 0 SQL input: 9007199254740993 JSON output: 9007199254740993 SQL input: 123.56 JSON output: 123.56 SQL input with stringify_wide_numbers=>TRUE: 9007199254740993 JSON output: "9007199254740993" SQL input with stringify_wide_numbers=>TRUE: 123.56 JSON output: 123.56 |
FLOAT64 |
number or string
|
SQL input: 1.0 JSON output: 1 SQL input: 9007199254740993 JSON output: 9007199254740993 SQL input: "+inf" JSON output: "Infinity" SQL input: "-inf" JSON output: "-Infinity" SQL input: "NaN" JSON output: "NaN" |
STRING |
string
Encoded as a string, escaped according to the JSON standard.
Specifically, |
SQL input: "abc" JSON output: "abc" SQL input: "\"abc\"" JSON output: "\"abc\"" |
BYTES |
string Uses RFC 4648 Base64 data encoding. |
SQL input: b"Google" JSON output: "R29vZ2xl" |
DATE | string |
SQL input: DATE '2017-03-06' JSON output: "2017-03-06" |
TIMESTAMP |
string Encoded as ISO 8601 date and time, where T separates the date and time and Z (Zulu/UTC) represents the time zone. |
SQL input: TIMESTAMP '2017-03-06 12:34:56.789012' JSON output: "2017-03-06T12:34:56.789012Z" |
JSON |
data of the input JSON |
SQL input: JSON '{"item": "pen", "price": 10}' JSON output: {"item":"pen", "price":10} SQL input: [1, 2, 3] JSON output: [1, 2, 3] |
ARRAY |
array Can contain zero or more elements. |
SQL input: ["red", "blue", "green"] JSON output: ["red","blue","green"] SQL input: [1, 2, 3] JSON output: [1,2,3] |
STRUCT |
object The object can contain zero or more key/value pairs. Each value is formatted according to its type.
For
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,
|
SQL input: STRUCT(12 AS purchases, TRUE AS inStock) JSON output: {"inStock": true,"purchases":12} |
JSONPath format
With the JSONPath format, you can identify the values you want to obtain from a JSON-formatted string. The JSONPath format supports these operators:
Operator | Description | Examples |
---|---|---|
$ |
Root object or element. The JSONPath format must start with this operator, which refers to the outermost level of the JSON-formatted string. |
JSON-formatted string:
JSON path:
JSON result: |
. |
Child operator. You can identify child values using dot-notation. |
JSON-formatted string:
JSON path:
JSON result: |
[] |
Subscript operator. If the JSON object is an array, you can use brackets to specify the array index. |
JSON-formatted string:
JSON path:
JSON result: |
If a key in a JSON functions contains a JSON format operator, refer to each JSON function for how to escape them.
A JSON function returns NULL
if the JSONPath format does not match a value in
a JSON-formatted string. 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 format is invalid, an error is produced.