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