GoogleSQL for BigQuery supports the following functions, which can retrieve and transform JSON data.
Function overview
Standard JSON extraction functions (recommended)
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> |
Legacy JSON extraction functions
The following functions use single quotes and brackets to escape invalid
JSONPath characters: ['a.b']
.
While these functions are supported by GoogleSQL, we recommend using the functions in the previous table.
JSON function | Description | Return type |
---|---|---|
JSON_EXTRACT |
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_EXTRACT_SCALAR |
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_EXTRACT_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_EXTRACT_STRING_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 SQL value and returns a JSON-formatted string representation of the value. | JSON-formatted STRING |
STRING |
Extracts a string from JSON. | STRING |
BOOL |
Extracts a boolean from JSON. | BOOL |
INT64 |
Extracts a 64-bit integer from JSON. | INT64 |
FLOAT64 |
Extracts a 64-bit floating-point number from JSON. | FLOAT64 |
JSON_TYPE |
Returns the type of the outermost JSON value as a string. | STRING |
BOOL
BOOL(json_expr)
Description
Takes a JSON expression, extracts a JSON boolean, and returns that value as a SQL
BOOL
. If the expression is SQL NULL
, the function returns SQL
NULL
. If the extracted JSON value is not a boolean, an error is produced.
json_expr
: JSON. For example:JSON '{"name": "sky", "color" : "blue"}'
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; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL
FLOAT64
FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])
Description
Takes a JSON expression, extracts a JSON number and returns that value as a SQL
FLOAT64
. If the expression is SQL NULL
, the
function returns SQL NULL
. If the extracted JSON value is not a number, an
error is produced.
json_expr
: JSON. For example:JSON '{"name": "sky", "color" : "blue"}'
This function supports an optional mandatory-named argument called
wide_number_mode
which defines what happens with a number that cannot be
represented as a FLOAT64 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 a
FLOAT64
without loss of precision. - ‘round’: The numeric value stored in JSON will be rounded to
FLOAT64
. If such rounding is not possible, the function fails. This is the default value if the argument is not specified.
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
Takes a JSON expression, extracts a JSON number and returns that value as a SQL
INT64
. If the expression is SQL NULL
, the function returns SQL
NULL
. If the extracted JSON number has a fractional part or is outside of the
INT64 domain, an error is produced.
json_expr
: JSON. For example:JSON '{"name": "sky", "color" : "blue"}'
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_EXTRACT_ARRAY
JSON_EXTRACT_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_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 single quotes and brackets.
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.
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: