GoogleSQL for BigQuery 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 |
Standard extractors |
JSON_QUERY JSON_VALUE JSON_QUERY_ARRAY JSON_VALUE_ARRAY |
Functions that extract JSON data. |
Legacy extractors |
JSON_EXTRACT JSON_EXTRACT_SCALAR JSON_EXTRACT_ARRAY JSON_EXTRACT_STRING_ARRAY |
Functions that extract JSON data. While these functions are supported by GoogleSQL, we recommend using the standard extractor functions. |
Lax converters |
LAX_BOOL LAX_FLOAT64 LAX_INT64 LAX_STRING |
Functions that flexibly convert a JSON value to a scalar SQL value without returning errors. |
Converters |
BOOL FLOAT64 INT64 STRING |
Functions that convert a JSON value to a scalar SQL value. |
Other converters |
PARSE_JSON TO_JSON TO_JSON_STRING |
Other conversion functions from or to JSON. |
Constructors |
JSON_ARRAY JSON_OBJECT |
Functions that create JSON. |
Mutators |
JSON_ARRAY_APPEND JSON_ARRAY_INSERT JSON_REMOVE JSON_SET JSON_STRIP_NULLS |
Functions that mutate existing 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_ARRAY
|
Creates a JSON array. |
JSON_ARRAY_APPEND
|
Appends JSON data to the end of a JSON array. |
JSON_ARRAY_INSERT
|
Inserts JSON data into a JSON array. |
JSON_EXTRACT
|
(Deprecated)
Extracts a JSON value and converts it to a SQL
JSON-formatted STRING
or
JSON
value.
|
JSON_EXTRACT_ARRAY
|
(Deprecated)
Extracts a JSON array and converts it to
a SQL ARRAY<JSON-formatted STRING>
or
ARRAY<JSON>
value.
|
JSON_EXTRACT_SCALAR
|
(Deprecated)
Extracts a JSON scalar value and converts it to a SQL
STRING value.
|
JSON_EXTRACT_STRING_ARRAY
|
(Deprecated)
Extracts a JSON array of scalar values and converts it to a SQL
ARRAY<STRING> value.
|
JSON_OBJECT
|
Creates a JSON object. |
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_REMOVE
|
Produces JSON with the specified JSON data removed. |
JSON_SET
|
Inserts or replaces JSON data. |
JSON_STRIP_NULLS
|
Removes JSON nulls from JSON objects and JSON arrays. |
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 SQL value to a 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_ARRAY
JSON_ARRAY([value][, ...])
Description
Creates a JSON array from zero or more SQL values.
Arguments:
value
: A JSON encoding-supported value to add to a JSON array.
Return type
JSON
Examples
You can create an empty JSON array. For example:
SELECT JSON_ARRAY() AS json_data
/*-----------*
| json_data |
+-----------+
| [] |
*-----------*/
The following query creates a JSON array with one value in it:
SELECT JSON_ARRAY(10) AS json_data
/*-----------*
| json_data |
+-----------+
| [10] |
*-----------*/
You can create a JSON array with an empty JSON array in it. For example:
SELECT JSON_ARRAY([]) AS json_data
/*-----------*
| json_data |
+-----------+
| [[]] |
*-----------*/
SELECT JSON_ARRAY(10, 'foo', NULL) AS json_data
/*-----------------*
| json_data |
+-----------------+
| [10,"foo",null] |
*-----------------*/
SELECT JSON_ARRAY(STRUCT(10 AS a, 'foo' AS b)) AS json_data
/*----------------------*
| json_data |
+----------------------+
| [{"a":10,"b":"foo"}] |
*----------------------*/
SELECT JSON_ARRAY(10, ['foo', 'bar'], [20, 30]) AS json_data
/*----------------------------*
| json_data |
+----------------------------+
| [10,["foo","bar"],[20,30]] |
*----------------------------*/
SELECT JSON_ARRAY(10, [JSON '20', JSON '"foo"']) AS json_data
/*-----------------*
| json_data |
+-----------------+
| [10,[20,"foo"]] |
*-----------------*/
JSON_ARRAY_APPEND
JSON_ARRAY_APPEND(
json_expr,
json_path_value_pair[, ...]
[, append_each_element=>{ TRUE | FALSE }]
)
json_path_value_pair:
json_path, value
Appends JSON data to the end of a JSON array.
Arguments:
json_expr
: JSON. For example:JSON '["a", "b", "c"]'
json_path_value_pair
: A value and the JSONPath for that value. This includes:json_path
: Appendvalue
at this JSONPath injson_expr
.value
: A JSON encoding-supported value to append.
append_each_element
: An optional, mandatory named argument.If
TRUE
(default), andvalue
is a SQL array, appends each element individually.If
FALSE,
andvalue
is a SQL array, appends the array as one element.
Details:
- Path value pairs are evaluated left to right. The JSON produced by evaluating one pair becomes the JSON against which the next pair is evaluated.
- The operation is ignored if the path points to a JSON non-array value that is not a JSON null.
- If
json_path
points to a JSON null, the JSON null is replaced by a JSON array that containsvalue
. - If the path exists but has an incompatible type at any given path token, the path value pair operation is ignored.
- The function applies all path value pair append operations even if an individual path value pair operation is invalid. For invalid operations, the operation is ignored and the function continues to process the rest of the path value pairs.
- If any
json_path
is an invalid JSONPath, an error is produced. - If
json_expr
is SQLNULL
, the function returns SQLNULL
. - If
append_each_element
is SQLNULL
, the function returnsjson_expr
. - If
json_path
is SQLNULL
, thejson_path_value_pair
operation is ignored.
Return type
JSON
Examples
In the following example, path $
is matched and appends 1
.
SELECT JSON_ARRAY_APPEND(JSON '["a", "b", "c"]', '$', 1) AS json_data
/*-----------------*
| json_data |
+-----------------+
| ["a","b","c",1] |
*-----------------*/
In the following example, append_each_element
defaults to TRUE
, so
[1, 2]
is appended as individual elements.
SELECT JSON_ARRAY_APPEND(JSON '["a", "b", "c"]', '$', [1, 2]) AS json_data
/*-------------------*
| json_data |
+-------------------+
| ["a","b","c",1,2] |
*-------------------*/
In the following example, append_each_element
is FALSE
, so
[1, 2]
is appended as one element.
SELECT JSON_ARRAY_APPEND(
JSON '["a", "b", "c"]',
'$', [1, 2],
append_each_element=>FALSE) AS json_data
/*---------------------*
| json_data |
+---------------------+
| ["a","b","c",[1,2]] |
*---------------------*/
In the following example, append_each_element
is FALSE
, so
[1, 2]
and [3, 4]
are each appended as one element.
SELECT JSON_ARRAY_APPEND(
JSON '["a", ["b"], "c"]',
'$[1]', [1, 2],
'$[1][1]', [3, 4],
append_each_element=>FALSE) AS json_data
/*-----------------------------*
| json_data |
+-----------------------------+
| ["a",["b",[1,2,[3,4]]],"c"] |
*-----------------------------*/
In the following example, the first path $[1]
appends [1, 2]
as single
elements, and then the second path $[1][1]
is not a valid path to an array,
so the second operation is ignored.