JSON functions

GoogleSQL for BigQuery supports the following functions, which can retrieve and transform JSON data.

Function overview

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:

SELECT JSON_EXTRACT_ARRAY(
  JSON '