GoogleSQL for Bigtable 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 |
Functions that extract JSON data. |
Legacy extractors |
JSON_EXTRACT JSON_EXTRACT_SCALAR |
Functions that extract JSON data. While these functions are supported by GoogleSQL, we recommend using the standard extractor functions. |
Other converters |
TO_JSON_STRING |
Other conversion functions from or to JSON. |
Function list
Name | Summary |
---|---|
JSON_EXTRACT
|
(Deprecated)
Extracts a JSON value and converts it to a SQL
JSON-formatted STRING
value.
|
JSON_EXTRACT_SCALAR
|
(Deprecated)
Extracts a JSON scalar value and converts it to a SQL
STRING value.
|
JSON_QUERY
|
Extracts a JSON value and converts it to a SQL
JSON-formatted STRING
value.
|
JSON_QUERY_ARRAY
|
Extracts a JSON array and converts it to
a SQL ARRAY<JSON-formatted STRING>
value.
|
JSON_VALUE
|
Extracts a JSON scalar value and converts it to a SQL
STRING value.
|
TO_JSON_STRING
|
Converts a SQL value to a JSON-formatted STRING value.
|
JSON_EXTRACT
JSON_EXTRACT(json_string_expr, json_path)
Description
Extracts a JSON value and converts it to a
SQL JSON-formatted STRING
value.
This function uses single quotes and brackets to escape invalid
JSONPath characters in JSON keys. For example: ['a.b']
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'{"class": {"students": [{"name": "Jane"}]}}'
Extracts a SQL
NULL
when a JSON-formatted stringnull
is encountered. For example:SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
json_path
: The JSONPath. This identifies the data that you want to obtain from the input.
Return type
A JSON-formatted STRING
Examples
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "Jane"}]}}',
'$') AS json_text_string;
/*-----------------------------------------------------------*
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
*-----------------------------------------------------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": []}}',
'$') AS json_text_string;
/*-----------------------------------------------------------*
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[]}} |
*-----------------------------------------------------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
'$') AS json_text_string;
/*-----------------------------------------------------------*
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
*-----------------------------------------------------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "Jane"}]}}',
'$.class.students[0]') AS first_student;
/*-----------------*
| first_student |
+-----------------+
| {"name":"Jane"} |
*-----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": []}}',
'$.class.students[0]') AS first_student;
/*-----------------*
| first_student |
+-----------------+
| NULL |
*-----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
'$.class.students[0]') AS first_student;
/*-----------------*
| first_student |
+-----------------+
| {"name":"John"} |
*-----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "Jane"}]}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| NULL |
*----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": []}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| NULL |
*----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "John"}, {"name": null}]}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| NULL |
*----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| "Jamie" |
*----------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "Jane"}]}}',
"$.class['students']") AS student_names;
/*------------------------------------*
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
*------------------------------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": []}}',
"$.class['students']") AS student_names;
/*------------------------------------*
| student_names |
+------------------------------------+
| [] |
*------------------------------------*/
SELECT JSON_EXTRACT(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
"$.class['students']") AS student_names;
/*------------------------------------*
| student_names |
+------------------------------------+
| [{"name":"John"},{"name":"Jamie"}] |
*------------------------------------*/
SELECT JSON_EXTRACT('{"a": null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a": null}', "$.b"); -- Returns a SQL NULL
JSON_EXTRACT_SCALAR
JSON_EXTRACT_SCALAR(json_string_expr[, json_path])
Description
Extracts a JSON scalar value and converts it to a SQL STRING
value.
In addition, this function:
- Removes the outermost quotes and unescapes the return values.
- Returns a SQL
NULL
if a non-scalar value is selected. - Uses single quotes and brackets to escape invalid JSONPath
characters in JSON keys. For example:
['a.b']
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'{"name": "Jane", "age": "6"}'
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.If
json_path
returns a JSONnull
or a non-scalar value (in other words, ifjson_path
refers to an object or an array), then a SQLNULL
is returned.
Return type
STRING
Examples
The following example compares how results are returned for the JSON_EXTRACT
and JSON_EXTRACT_SCALAR
functions.
SELECT JSON_EXTRACT('{"name": "Jakob", "age": "6" }', '$.name') AS json_name,
JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }', '$.name') AS scalar_name,
JSON_EXTRACT('{"name": "Jakob", "age": "6" }', '$.age') AS json_age,
JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }', '$.age') AS scalar_age;
/*-----------+-------------+----------+------------*
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob" | Jakob | "6" | 6 |
*-----------+-------------+----------+------------*/
SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;
/*--------------------+---------------------*
| json_extract | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL |
*--------------------+---------------------*/
In cases where a JSON key uses invalid JSONPath characters, you can escape those
characters using single quotes and brackets, [' ']
. For example:
SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello;
/*-------*
| hello |
+-------+
| world |
*-------*/
JSON_QUERY
JSON_QUERY(json_string_expr, json_path)
Description
Extracts a JSON value and converts it to a SQL
JSON-formatted STRING
value.
This function uses double quotes to escape invalid
JSONPath characters in JSON keys. For example: "a.b"
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'{"class": {"students": [{"name": "Jane"}]}}'
Extracts a SQL
NULL
when a JSON-formatted stringnull
is encountered. For example:SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
json_path
: The JSONPath. This identifies the data that you want to obtain from the input.
Return type
A JSON-formatted STRING
Examples
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
SELECT
JSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}', '$') AS json_text_string;
/*-----------------------------------------------------------*
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
*-----------------------------------------------------------*/
SELECT JSON_QUERY('{"class": {"students": []}}', '$') AS json_text_string;
/*-----------------------------------------------------------*
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[]}} |
*-----------------------------------------------------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "John"},{"name": "Jamie"}]}}',
'$') AS json_text_string;
/*-----------------------------------------------------------*
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
*-----------------------------------------------------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "Jane"}]}}',
'$.class.students[0]') AS first_student;
/*-----------------*
| first_student |
+-----------------+
| {"name":"Jane"} |
*-----------------*/
SELECT
JSON_QUERY('{"class": {"students": []}}', '$.class.students[0]') AS first_student;
/*-----------------*
| first_student |
+-----------------+
| NULL |
*-----------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
'$.class.students[0]') AS first_student;
/*-----------------*
| first_student |
+-----------------+
| {"name":"John"} |
*-----------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "Jane"}]}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| NULL |
*----------------*/
SELECT
JSON_QUERY(
'{"class": {"students": []}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| NULL |
*----------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "John"}, {"name": null}]}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| NULL |
*----------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
'$.class.students[1].name') AS second_student;
/*----------------*
| second_student |
+----------------+
| "Jamie" |
*----------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "Jane"}]}}',
'$.class."students"') AS student_names;
/*------------------------------------*
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
*------------------------------------*/
SELECT
JSON_QUERY(
'{"class": {"students": []}}',
'$.class."students"') AS student_names;
/*------------------------------------*
| student_names |
+------------------------------------+
| [] |
*------------------------------------*/
SELECT
JSON_QUERY(
'{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',
'$.class."students"') AS student_names;
/*------------------------------------*
| student_names |
+------------------------------------+
| [{"name":"John"},{"name":"Jamie"}] |
*------------------------------------*/
SELECT JSON_QUERY('{"a": null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a": null}', "$.b"); -- Returns a SQL NULL
JSON_QUERY_ARRAY
JSON_QUERY_ARRAY(json_string_expr[, json_path])
Description
Extracts a JSON array and converts it to
a SQL ARRAY<JSON-formatted STRING>
value.
In addition, this function uses double quotes to escape invalid
JSONPath characters in JSON keys. For example: "a.b"
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'["a", "b", {"key": "c"}]'
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.
Return type
ARRAY<JSON-formatted STRING>
Examples
This extracts the items in a JSON-formatted string to a string array:
SELECT JSON_QUERY_ARRAY('[1, 2, 3]') AS string_array;
/*--------------*
| string_array |
+--------------+
| [1, 2, 3] |
*--------------*/
This extracts string values in a JSON-formatted string to an array:
-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples", "oranges", "grapes"]', '$') AS string_array;
/*---------------------------------*
| string_array |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
*---------------------------------*/
This extracts only the items in the fruit
property to an array:
SELECT JSON_QUERY_ARRAY(
'{"fruit": [{"apples": 5, "oranges": 10}, {"apples": 2, "oranges": 4}], "vegetables": [{"lettuce": 7, "kale": 8}]}',
'$.fruit'
) AS string_array;
/*-------------------------------------------------------*
| string_array |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
*-------------------------------------------------------*/
These are equivalent:
SELECT JSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$.fruits') AS string_array;
SELECT JSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}', '$."fruits"') AS string_array;
-- The queries above produce the following result:
/*---------------------------------*
| string_array |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
*---------------------------------*/
In cases where a JSON key uses invalid JSONPath characters, you can escape those
characters using double quotes: " "
. For example:
SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;
/*-----------*
| hello |
+-----------+
| ["world"] |
*-----------*/
The following examples show how invalid requests and empty arrays are handled:
-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo", "bar", "baz"]', 'INVALID_JSONPath') AS result;
-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a": "foo"}', '$.a') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a": "foo"}', '$.b') AS result;
/*--------*
| result |
+--------+
| NULL |
*--------*/
-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a": "foo", "b": []}', '$.b') AS result;
/*--------*
| result |
+--------+
| [] |
*--------*/
JSON_VALUE
JSON_VALUE(json_string_expr[, json_path])
Description
Extracts a JSON scalar value and converts it to a SQL STRING
value.
In addition, this function:
- Removes the outermost quotes and unescapes the values.
- Returns a SQL
NULL
if a non-scalar value is selected. - Uses double quotes to escape invalid JSONPath characters
in JSON keys. For example:
"a.b"
.
Arguments:
json_string_expr
: A JSON-formatted string. For example:'{"name": "Jakob", "age": "6"}'
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.If
json_path
returns a JSONnull
or a non-scalar value (in other words, ifjson_path
refers to an object or an array), then a SQLNULL
is returned.
Return type
STRING
Examples
The following example compares how results are returned for the JSON_QUERY
and JSON_VALUE
functions.
SELECT JSON_QUERY('{"name": "Jakob", "age": "6"}', '$.name') AS json_name,
JSON_VALUE('{"name": "Jakob", "age": "6"}', '$.name') AS scalar_name,
JSON_QUERY('{"name": "Jakob", "age": "6"}', '$.age') AS json_age,
JSON_VALUE('{"name": "Jakob", "age": "6"}', '$.age') AS scalar_age;
/*-----------+-------------+----------+------------*
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob" | Jakob | "6" | 6 |
*-----------+-------------+----------+------------*/
SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;
/*--------------------+------------*
| json_query | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL |
*--------------------+------------*/
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes. For example:
SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;
/*-------*
| hello |
+-------+
| world |
*-------*/
TO_JSON_STRING
TO_JSON_STRING(value[, pretty_print])
Description
Converts a SQL value to a JSON-formatted STRING
value.
Arguments:
value
: A SQL value. You can review the GoogleSQL data types that this function supports and their JSON encodings here.pretty_print
: Optional boolean parameter. Ifpretty_print
istrue
, the `returned value is formatted for easy readability.
Return type
A JSON-formatted STRING
Examples
The following query converts a STRUCT
value to a JSON-formatted string:
SELECT TO_JSON_STRING(STRUCT(1 AS id, [10,20] AS coordinates)) AS json_data
/*--------------------------------*
| json_data |
+--------------------------------+
| {"id":1,"coordinates":[10,20]} |
*--------------------------------*/
The following query converts a STRUCT
value to a JSON-formatted string that is
easy to read:
SELECT TO_JSON_STRING(STRUCT(1 AS id, [10,20] AS coordinates), true) AS json_data
/*--------------------*
| json_data |
+--------------------+
| { |
| "id": 1, |
| "coordinates": [ |
| 10, |
| 20 |
| ] |
| } |
*--------------------*/
Supplemental materials
JSON encodings
You can encode a SQL value as a JSON value with the following functions:
TO_JSON_STRING
The following SQL to JSON encodings are supported:
From SQL | To JSON | Examples |
---|---|---|
NULL |
null |
SQL input: NULL JSON output: null
|
BOOL | boolean |
SQL input: TRUE JSON output: true SQL input: FALSE JSON output: false |
INT64 |
number or string Encoded as a number when the value is in the range of [-253, 253], which is the range of integers that can be represented losslessly as IEEE 754 double-precision floating point numbers. A value outside of this range is encoded as a string. |
SQL input: 9007199254740992 JSON output: 9007199254740992 SQL input: 9007199254740993 JSON output: "9007199254740993" |
FLOAT32 FLOAT64 |
number or string
|
SQL input: 1.0 JSON output: 1 SQL input: 9007199254740993 JSON output: 9007199254740993 SQL input: "+inf" JSON output: "Infinity" SQL input: "-inf" JSON output: "-Infinity" SQL input: "NaN" JSON output: "NaN" |
STRING |
string
Encoded as a string, escaped according to the JSON standard.
Specifically, |
SQL input: "abc" JSON output: "abc" SQL input: "\"abc\"" JSON output: "\"abc\"" |
BYTES |
string Uses RFC 4648 Base64 data encoding. |
SQL input: b"Google" JSON output: "R29vZ2xl" |
ENUM |
string Invalid enum values are encoded as their number, such as 0 or 42. |
SQL input: Color.Red JSON output: "Red" |
DATE | string |
SQL input: DATE '2017-03-06' JSON output: "2017-03-06" |
TIMESTAMP |
string Encoded as ISO 8601 date and time, where T separates the date and time and Z (Zulu/UTC) represents the time zone. |
SQL input: TIMESTAMP '2017-03-06 12:34:56.789012' JSON output: "2017-03-06T12:34:56.789012Z" |
ARRAY |
array Can contain zero or more elements. |
SQL input: ["red", "blue", "green"] JSON output: ["red","blue","green"] SQL input: [1, 2, 3] JSON output: [1,2,3] |
STRUCT |
object The object can contain zero or more key-value pairs. Each value is formatted according to its type.
For
Anonymous fields are represented with
Invalid UTF-8 field names might result in unparseable JSON. String
values are escaped according to the JSON standard. Specifically,
|
SQL input: STRUCT(12 AS purchases, TRUE AS inStock) JSON output: {"inStock": true,"purchases":12} |
JSONPath format
With the JSONPath format, you can identify the values you want to obtain from a JSON-formatted string.
If a key in a JSON functions contains a JSON format operator, refer to each JSON function for how to escape them.
A JSON function returns NULL
if the JSONPath format does not match a value in
a JSON-formatted string. If the selected value for a scalar function is not
scalar, such as an object or an array, the function returns NULL
. If the
JSONPath format is invalid, an error is produced.
Operators for JSONPath
The JSONPath format supports these operators:
Operator | Description | Examples |
---|---|---|
$ |
Root object or element. The JSONPath format must start with this operator, which refers to the outermost level of the JSON-formatted string. |
JSON-formatted string:
JSON path:
JSON result: |
. |
Child operator. You can identify child values using dot-notation. |
JSON-formatted string:
JSON path:
JSON result: |
[] |
Subscript operator. If the object is a JSON array, you can use brackets to specify the array index. |
JSON-formatted string:
JSON path:
JSON result: |
[][] [][][]...
|
Child subscript operator. If the object is a JSON array within an array, you can use as many additional brackets as you need to specify the child array index. |
JSON-formatted string:
JSON path:
JSON result: |