JSON functions

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: