GoogleSQL for Spanner supports the following functions, which can retrieve and transform JSON data.
Categories
The JSON functions are grouped into the following categories based on their behavior:
Category | Functions | Description |
Extractors |
JSON_QUERY JSON_VALUE JSON_QUERY_ARRAY JSON_VALUE_ARRAY |
Functions that extract JSON data. |
Lax converters |
LAX_BOOL LAX_FLOAT64 LAX_INT64 LAX_STRING |
Functions that flexibly convert a JSON value to an SQL value without returning errors. |
Converters |
BOOL FLOAT64 INT64 STRING |
Functions that convert a JSON value to a SQL value. |
Other converters |
PARSE_JSON TO_JSON TO_JSON_STRING |
Other conversion functions from or to JSON. |
Accessors |
JSON_TYPE |
Functions that provide access to JSON properties. |
Function list
Name | Summary |
---|---|
BOOL
|
Converts a JSON boolean to a SQL BOOL value.
|
FLOAT64
|
Converts a JSON number to a SQL
FLOAT64 value.
|
INT64
|
Converts a JSON number to a SQL INT64 value.
|
JSON_QUERY
|
Extracts a JSON value and converts it to a SQL
JSON-formatted STRING
or
JSON
value.
|
JSON_QUERY_ARRAY
|
Extracts a JSON array and converts it to
a SQL ARRAY<JSON-formatted STRING>
or
ARRAY<JSON>
value.
|
JSON_TYPE
|
Gets the JSON type of the outermost JSON value and converts the name of
this type to a SQL STRING value.
|
JSON_VALUE
|
Extracts a JSON scalar value and converts it to a SQL
STRING value.
|
JSON_VALUE_ARRAY
|
Extracts a JSON array of scalar values and converts it to a SQL
ARRAY<STRING> value.
|
LAX_BOOL
|
Attempts to convert a JSON value to a SQL BOOL value.
|
LAX_FLOAT64
|
Attempts to convert a JSON value to a
SQL FLOAT64 value.
|
LAX_INT64
|
Attempts to convert a JSON value to a SQL INT64 value.
|
LAX_STRING
|
Attempts to convert a JSON value to a SQL STRING value.
|
PARSE_JSON
|
Converts a JSON-formatted STRING value to a
JSON value.
|
STRING
|
Converts a JSON string to a SQL STRING value.
|
TO_JSON
|
Converts a SQL value to a JSON value. |
TO_JSON_STRING
|
Converts a JSON value to a SQL JSON-formatted STRING value.
|
BOOL
BOOL(json_expr)
Description
Converts a JSON boolean to a SQL BOOL
value.
Arguments:
json_expr
: JSON. For example:JSON 'true'
If the JSON value is not a boolean, an error is produced. If the expression is SQL
NULL
, the function returns SQLNULL
.
Return type
BOOL
Examples
SELECT BOOL(JSON 'true') AS vacancy;
/*---------*
| vacancy |
+---------+
| true |
*---------*/
SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;
/*---------*
| vacancy |
+---------+
| true |
*---------*/
The following examples show how invalid requests are handled:
-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throws an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL
FLOAT64
FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])
Description
Converts a JSON number to a SQL FLOAT64
value.
Arguments:
json_expr
: JSON. For example:JSON '9.8'
If the JSON value is not a number, an error is produced. If the expression is a SQL
NULL
, the function returns SQLNULL
.wide_number_mode
: Optional mandatory-named argument, which defines what happens with a number that cannot be represented as aFLOAT64
without loss of precision. This argument accepts one of the two case-sensitive values:exact
: The function fails if the result cannot be represented as aFLOAT64
without loss of precision.round
(default): The numeric value stored in JSON will be rounded toFLOAT64
. If such rounding is not possible, the function fails.
Return type
FLOAT64
Examples
SELECT FLOAT64(JSON '9.8') AS velocity;
/*----------*
| velocity |
+----------+
| 9.8 |
*----------*/
SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;
/*---------*
| vo2_max |
+---------+
| 39.1 |
*---------*/
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;
/*------------------------*
| result |
+------------------------+
| 1.8446744073709552e+19 |
*------------------------*/
SELECT FLOAT64(JSON '18446744073709551615') as result;
/*------------------------*
| result |
+------------------------+
| 1.8446744073709552e+19 |
*------------------------*/
The following examples show how invalid requests are handled:
-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;
-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;
-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;
-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;
INT64
INT64(json_expr)
Description
Converts a JSON number to a SQL INT64
value.
Arguments:
json_expr
: JSON. For example:JSON '999'
If the JSON value is not a number, or the JSON number is not in the SQL
INT64
domain, an error is produced. If the expression is SQLNULL
, the function returns SQLNULL
.
Return type
INT64
Examples
SELECT INT64(JSON '2005') AS flight_number;
/*---------------*
| flight_number |
+---------------+
| 2005 |
*---------------*/
SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;
/*---------------*
| flight_number |
+---------------+
| 2005 |
*---------------*/
SELECT INT64(JSON '10.0') AS score;
/*-------*
| score |
+-------+
| 10 |
*-------*/
The following examples show how invalid requests are handled:
-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result; -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result; -- Returns a SQL NULL
JSON_QUERY
JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)
Description
Extracts a JSON value and converts it to a SQL
JSON-formatted STRING
or
JSON
value.
This function uses double quotes to escape invalid
JSONPath characters in JSON keys. For example: "a.b"
.
Arguments:
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.
There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.
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_QUERY_ARRAY
JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])
Description
Extracts a JSON array and converts it to
a SQL ARRAY<JSON-formatted STRING>
or
ARRAY<JSON>
value.
In addition, this function uses double quotes to escape invalid
JSONPath characters in JSON keys. For example: "a.b"
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'["a", "b", {"key": "c"}]'
json_expr
: JSON. For example:JSON '["a", "b", {"key": "c"}]'
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.
There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.
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_TYPE
JSON_TYPE(json_expr)
Description
Gets the JSON type of the outermost JSON value and converts the name of
this type to a SQL STRING
value. The names of these JSON types can be
returned: object
, array
, string
, number
, boolean
, null
Arguments:
json_expr
: JSON. For example:JSON '{"name": "sky", "color": "blue"}'
If this expression is SQL
NULL
, the function returns SQLNULL
. If the extracted JSON value is not a valid JSON type, an error is produced.
Return type
STRING
Examples
SELECT json_val, JSON_TYPE(json_val) AS type
FROM
UNNEST(
[
JSON '"apple"',
JSON '10',
JSON '3.14',
JSON 'null',
JSON '{"city": "New York", "State": "NY"}',
JSON '["apple", "banana"]',
JSON 'false'
]
) AS json_val;
/*----------------------------------+---------*
| json_val | type |
+----------------------------------+---------+
| "apple" | string |
| 10 | number |
| 3.14 | number |
| null | null |
| {"State":"NY","city":"New York"} | object |
| ["apple","banana"] | array |
| false | boolean |
*----------------------------------+---------*/
JSON_VALUE
JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])
Description
Extracts a JSON scalar value and converts it to a SQL STRING
value.
In addition, this function:
- Removes the outermost quotes and unescapes the values.
- Returns a SQL
NULL
if a non-scalar value is selected. - Uses double quotes to escape invalid JSONPath characters
in JSON keys. For example:
"a.b"
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'{"name": "Jakob", "age": "6"}'
json_expr
: JSON. For example:JSON '{"name": "Jane", "age": "6"}'
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.
There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.
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_VALUE_ARRAY
JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])
Description
Extracts a JSON array of scalar values and converts it to a SQL
ARRAY<STRING>
value.
In addition, this function:
- 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. - Uses double quotes to escape invalid JSONPath characters
in JSON keys. For example:
"a.b"
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'["apples", "oranges", "grapes"]'
json_expr
: JSON. For example:JSON '["apples", "oranges", "grapes"]'
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.
There are differences between the JSON-formatted string and JSON input types. For details, see Differences between the JSON and JSON-formatted STRING types.
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] |
*------------------*/
LAX_BOOL
LAX_BOOL(json_expr)
Description
Attempts to convert a JSON value to a SQL BOOL
value.
Arguments:
json_expr
: JSON. For example:JSON 'true'
Details:
- If
json_expr
is SQLNULL
, the function returns SQLNULL
. - See the conversion rules in the next section for additional
NULL
handling.
Conversion rules
From JSON type | To SQL BOOL |
---|---|
boolean |
If the JSON boolean is true , returns TRUE .
Otherwise, returns FALSE .
|
string |
If the JSON string is 'true' , returns TRUE .
If the JSON string is 'false' , returns FALSE .
If the JSON string is any other value or has whitespace in it,
returns NULL .
This conversion is case-insensitive.
|
number |
If the JSON number is a representation of 0 ,
returns FALSE . Otherwise, returns TRUE .
|
other type or null | NULL |
Return type
BOOL
Examples
Example with input that is a JSON boolean:
SELECT LAX_BOOL(JSON 'true') AS result;
/*--------*
| result |
+--------+
| true |
*--------*/
Examples with inputs that are JSON strings:
SELECT LAX_BOOL(JSON '"true"') AS result;
/*--------*
| result |
+--------+
| TRUE |
*--------*/
SELECT LAX_BOOL(JSON '"true "') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
SELECT LAX_BOOL(JSON '"foo"') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
Examples with inputs that are JSON numbers:
SELECT LAX_BOOL(JSON '10') AS result;
/*--------*
| result |
+--------+
| TRUE |
*--------*/
SELECT LAX_BOOL(JSON '0') AS result;
/*--------*
| result |
+--------+
| FALSE |
*--------*/
SELECT LAX_BOOL(JSON '0.0') AS result;
/*--------*
| result |
+--------+
| FALSE |
*--------*/
SELECT LAX_BOOL(JSON '-1.1') AS result;
/*--------*
| result |
+--------+
| TRUE |
*--------*/
LAX_FLOAT64
LAX_FLOAT64(json_expr)
Description
Attempts to convert a JSON value to a
SQL FLOAT64
value.
Arguments:
json_expr
: JSON. For example:JSON '9.8'
Details:
- If
json_expr
is SQLNULL
, the function returns SQLNULL
. - See the conversion rules in the next section for additional
NULL
handling.
Conversion rules
From JSON type | To SQL FLOAT64 |
---|---|
boolean |
NULL
|
string |
If the JSON string represents a JSON number, parses it as
a
JSON number, and then safe casts the result as a
FLOAT64 value.
If the JSON string can't be converted, returns NULL .
|
number |
Casts the JSON number as a
FLOAT64 value.
Large JSON numbers are rounded.
|
other type or null | NULL |
Return type
FLOAT64
Examples
Examples with inputs that are JSON numbers:
SELECT LAX_FLOAT64(JSON '9.8') AS result;
/*--------*
| result |
+--------+
| 9.8 |
*--------*/
SELECT LAX_FLOAT64(JSON '9') AS result;
/*--------*
| result |
+--------+
| 9.0 |
*--------*/
SELECT LAX_FLOAT64(JSON '9007199254740993') AS result;
/*--------------------*
| result |
+--------------------+
| 9007199254740992.0 |
*--------------------*/
SELECT LAX_FLOAT64(JSON '1e100') AS result;
/*--------*
| result |
+--------+
| 1e+100 |
*--------*/
Examples with inputs that are JSON booleans:
SELECT LAX_FLOAT64(JSON 'true') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
SELECT LAX_FLOAT64(JSON 'false') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
Examples with inputs that are JSON strings:
SELECT LAX_FLOAT64(JSON '"10"') AS result;
/*--------*
| result |
+--------+
| 10.0 |
*--------*/
SELECT LAX_FLOAT64(JSON '"1.1"') AS result;
/*--------*
| result |
+--------+
| 1.1 |
*--------*/
SELECT LAX_FLOAT64(JSON '"1.1e2"') AS result;
/*--------*
| result |
+--------+
| 110.0 |
*--------*/
SELECT LAX_FLOAT64(JSON '"9007199254740993"') AS result;
/*--------------------*
| result |
+--------------------+
| 9007199254740992.0 |
*--------------------*/
SELECT LAX_FLOAT64(JSON '"+1.5"') AS result;
/*--------*
| result |
+--------+
| 1.5 |
*--------*/
SELECT LAX_FLOAT64(JSON '"NaN"') AS result;
/*--------*
| result |
+--------+
| NaN |
*--------*/
SELECT LAX_FLOAT64(JSON '"Inf"') AS result;
/*----------*
| result |
+----------+
| Infinity |
*----------*/
SELECT LAX_FLOAT64(JSON '"-InfiNiTY"') AS result;
/*-----------*
| result |
+-----------+
| -Infinity |
*-----------*/
SELECT LAX_FLOAT64(JSON '"foo"') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
LAX_INT64
LAX_INT64(json_expr)
Description
Attempts to convert a JSON value to a SQL INT64
value.
Arguments:
json_expr
: JSON. For example:JSON '999'
Details:
- If
json_expr
is SQLNULL
, the function returns SQLNULL
. - See the conversion rules in the next section for additional
NULL
handling.
Conversion rules
From JSON type | To SQL INT64 |
---|---|
boolean |
If the JSON boolean is true , returns 1 .
If false , returns 0 .
|
string |
If the JSON string represents a JSON number, parses it as
a
JSON number, and then safe casts the results as an
INT64 value.
If the JSON string can't be converted, returns NULL .
|
number |
Casts the JSON number as an INT64 value.
If the JSON number can't be converted, returns NULL .
|
other type or null | NULL |
Return type
INT64
Examples
Examples with inputs that are JSON numbers:
SELECT LAX_INT64(JSON '10') AS result;
/*--------*
| result |
+--------+
| 10 |
*--------*/
SELECT LAX_INT64(JSON '10.0') AS result;
/*--------*
| result |
+--------+
| 10 |
*--------*/
SELECT LAX_INT64(JSON '1.1') AS result;
/*--------*
| result |
+--------+
| 1 |
*--------*/
SELECT LAX_INT64(JSON '3.5') AS result;
/*--------*
| result |
+--------+
| 4 |
*--------*/
SELECT LAX_INT64(JSON '1.1e2') AS result;
/*--------*
| result |
+--------+
| 110 |
*--------*/
SELECT LAX_INT64(JSON '1e100') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
Examples with inputs that are JSON booleans:
SELECT LAX_INT64(JSON 'true') AS result;
/*--------*
| result |
+--------+
| 1 |
*--------*/
SELECT LAX_INT64(JSON 'false') AS result;
/*--------*
| result |
+--------+
| 0 |
*--------*/
Examples with inputs that are JSON strings:
SELECT LAX_INT64(JSON '"10"') AS result;
/*--------*
| result |
+--------+
| 10 |
*--------*/
SELECT LAX_INT64(JSON '"1.1"') AS result;
/*--------*
| result |
+--------+
| 1 |
*--------*/
SELECT LAX_INT64(JSON '"1.1e2"') AS result;
/*--------*
| result |
+--------+
| 110 |
*--------*/
SELECT LAX_INT64(JSON '"+1.5"') AS result;
/*--------*
| result |
+--------+
| 2 |
*--------*/
SELECT LAX_INT64(JSON '"1e100"') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
SELECT LAX_INT64(JSON '"foo"') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
LAX_STRING
LAX_STRING(json_expr)
Description
Attempts to convert a JSON value to a SQL STRING
value.
Arguments:
json_expr
: JSON. For example:JSON '"name"'
Details:
- If
json_expr
is SQLNULL
, the function returns SQLNULL
. - See the conversion rules in the next section for additional
NULL
handling.
Conversion rules
From JSON type | To SQL STRING |
---|---|
boolean |
If the JSON boolean is true , returns 'true' .
If false , returns 'false' .
|
string |
Returns the JSON string as a STRING value.
|
number |
Returns the JSON number as a STRING value.
|
other type or null | NULL |
Return type
STRING
Examples
Examples with inputs that are JSON strings:
SELECT LAX_STRING(JSON '"purple"') AS result;
/*--------*
| result |
+--------+
| purple |
*--------*/
SELECT LAX_STRING(JSON '"10"') AS result;
/*--------*
| result |
+--------+
| 10 |
*--------*/
Examples with inputs that are JSON booleans:
SELECT LAX_STRING(JSON 'true') AS result;
/*--------*
| result |
+--------+
| true |
*--------*/
SELECT LAX_STRING(JSON 'false') AS result;
/*--------*
| result |
+--------+
| false |
*--------*/
Examples with inputs that are JSON numbers:
SELECT LAX_STRING(JSON '10.0') AS result;
/*--------*
| result |
+--------+
| 10 |
*--------*/
SELECT LAX_STRING(JSON '10') AS result;
/*--------*
| result |
+--------+
| 10 |
*--------*/
SELECT LAX_STRING(JSON '1e100') AS result;
/*--------*
| result |
+--------+
| 1e+100 |
*--------*/
PARSE_JSON
PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])
Description
Converts a JSON-formatted STRING
value to a JSON
value.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'{"class": {"students": [{"name": "Jane"}]}}'
wide_number_mode
: Optional mandatory-named argument that determines how to handle numbers that cannot be stored in aJSON
value without the loss of precision. If used,wide_number_mode
must include one of these values:exact
(default): 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 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} |
*------------------------------*/
STRING
STRING(json_expr)
Description
Converts a JSON string to a SQL STRING
value.
Arguments:
json_expr
: JSON. For example:JSON '"purple"'
If the JSON value is not a string, an error is produced. If the expression is SQL
NULL
, the function returns SQLNULL
.
Return type
STRING
Examples
SELECT STRING(JSON '"purple"') AS color;
/*--------*
| color |
+--------+
| purple |
*--------*/
SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;
/*-------*
| color |
+-------+
| blue |
*-------*/
The following examples show how invalid requests are handled:
-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL
TO_JSON
TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])
Description
Converts a SQL value to a JSON value.
Arguments:
sql_value
: The SQL value to convert to a JSON value. You can review the GoogleSQL data types that this function supports and their JSON encodings here.stringify_wide_numbers
: Optional mandatory-named argument that is eitherTRUE
orFALSE
(default).- If
TRUE
, numeric values outside of theFLOAT64
type domain are encoded as strings. - If
FALSE
(default), 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:- If
INT64
NUMERIC
If one of these numerical data types appears in a container data type such as an
ARRAY
orSTRUCT
, thestringify_wide_numbers
argument is applied to the numerical data types in the container data type.
Return type
JSON
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
Converts a JSON value to a SQL JSON-formatted STRING
value.
Arguments:
json_expr
: JSON. For example:JSON '{"class": {"students": [{"name": "Jane"}]}}'
Return type
A JSON-formatted STRING
Example
Convert a JSON value to a JSON-formatted STRING
value.
SELECT TO_JSON_STRING(JSON '{"id": 1, "coordinates": [10, 20]}') AS json_string
/*--------------------------------*
| json_string |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
*--------------------------------*/
JSON encodings
You can encode a SQL value as a JSON value with the following functions:
TO_JSON
The following SQL to JSON encodings are supported:
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 object is a JSON 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.
Differences between the JSON and JSON-formatted STRING types
Many JSON functions accept two input types:
JSON
typeSTRING
type
The STRING
version of the extraction functions behaves differently than the
JSON
version, mainly because JSON
type values are always validated whereas
JSON-formatted STRING
type values are not.
Non-validation of STRING
inputs
The following STRING
is invalid JSON because it is missing a trailing }
:
{"hello": "world"
The JSON function reads the input from the beginning and stops as soon as the field to extract is found, without reading the remainder of the input. A parsing error is not produced.
With the JSON
type, however, JSON '{"hello": "world"'
returns a parsing
error.
For example:
SELECT JSON_VALUE('{"hello": "world"', "$.hello") AS hello;
/*-------*
| hello |
+-------+
| world |
*-------*/
SELECT JSON_VALUE(JSON '{"hello": "world"', "$.hello") AS hello;
-- An error is returned: Invalid JSON literal: syntax error while parsing
-- object - unexpected end of input; expected '}'
No strict validation of extracted values
In the following examples, duplicated keys are not removed when using a
JSON-formatted string. Similarly, keys order is preserved. For the JSON
type, JSON '{"key": 1, "key": 2}'
will result in JSON '{"key":1}'
during
parsing.
SELECT JSON_QUERY('{"key": 1, "key": 2}', "$") AS string;
/*-------------------*
| string |
+-------------------+
| {"key":1,"key":2} |
*-------------------*/
SELECT JSON_QUERY(JSON '{"key": 1, "key": 2}', "$") AS json;
/*-----------*
| json |
+-----------+
| {"key":1} |
*-----------*/
JSON null
When using a JSON-formatted STRING
type in a JSON function, a JSON null
value is extracted as a SQL NULL
value.
When using a JSON type in a JSON function, a JSON null
value returns a JSON
null
value.
WITH t AS (
SELECT '{"name": null}' AS json_string, JSON '{"name": null}' AS json)
SELECT JSON_QUERY(json_string, "$.name") AS name_string,
JSON_QUERY(json_string, "$.name") IS NULL AS name_string_is_null,
JSON_QUERY(json, "$.name") AS name_json,
JSON_QUERY(json, "$.name") IS NULL AS name_json_is_null
FROM t;
/*-------------+---------------------+-----------+-------------------*
| name_string | name_string_is_null | name_json | name_json_is_null |
+-------------+---------------------+-----------+-------------------+
| NULL | true | null | false |
*-------------+---------------------+-----------+-------------------*/