JSON functions

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 SQL NULL.

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 SQL NULL.

  • wide_number_mode: Optional mandatory-named argument, 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 (default): The numeric value stored in JSON will be rounded to FLOAT64. 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 SQL NULL, the function returns SQL NULL.

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:

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:

  • append_each_element: An optional, mandatory named argument.

    • If TRUE (default), and value is a SQL array, appends each element individually.

    • If FALSE, and value 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 contains value.
  • 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 SQL NULL, the function returns SQL NULL.
  • If append_each_element is SQL NULL, the function returns json_expr.
  • If json_path is SQL NULL, the json_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.