BigQuery supports functions that help you retrieve data stored in JSON-formatted strings and functions that help you transform data into JSON-formatted strings.
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-formatted scalar value, such as a string, integer, or boolean. | JSON-formatted STRING |
JSON_VALUE |
Extracts a scalar value.
A scalar value can represent a string, integer, 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-formatted scalar values, such as strings, integers, and booleans. | ARRAY<JSON-formatted STRING> |
JSON_VALUE_ARRAY |
Extracts an array of scalar values. A scalar value can represent a
string, integer, 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 BigQuery, 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-formatted scalar value, such as a string, integer, or boolean. | JSON-formatted STRING |
JSON_EXTRACT_SCALAR |
Extracts a scalar value.
A scalar value can represent a string, integer, 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-formatted scalar values, such as strings, integers, and booleans. | ARRAY<JSON-formatted STRING> |
JSON_EXTRACT_STRING_ARRAY |
Extracts an array of scalar values. A scalar value can represent a
string, integer, 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 |
---|---|---|
TO_JSON_STRING |
Returns a JSON-formatted string representation of a value. | JSON-formatted STRING |
JSON_EXTRACT
JSON_EXTRACT(json_string_expr, json_path)
Description
Extracts a JSON value, such as an array or object, or a JSON-formatted scalar value, such as a string, integer, or boolean. 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_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. Ifjson_path
returns a JSONnull
, then this is converted into a SQLNULL
.
If you want to include non-scalar values such as arrays in the extraction, then
use JSON_EXTRACT
. If you only want to extract scalar values such strings,
integers, and booleans, then use JSON_EXTRACT_SCALAR
.
Return type
A JSON-formatted STRING
Examples
SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
JSON_QUERY
JSON_QUERY(json_string_expr, json_path)
Description
Extracts a JSON value, such as an array or object, or a JSON-formatted scalar value, such as a string, integer, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. Ifjson_path
returns a JSONnull
, then this is converted into a SQLNULL
.
If you want to include non-scalar values such as arrays in the extraction, then
use JSON_QUERY
. If you only want to extract scalar values such strings,
integers, and booleans, then use JSON_VALUE
.
Return type
A JSON-formatted STRING
Examples
SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
JSON_EXTRACT_SCALAR
JSON_EXTRACT_SCALAR(json_string_expr, json_path)
Description
Extracts a scalar value and then returns it as a string. A scalar value can represent a string, integer, or boolean. Removes the outermost quotes and unescapes the return values. 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_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. Ifjson_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.
If you only want to extract scalar values such strings, integers, and booleans,
then use JSON_EXTRACT_SCALAR
. If you want to include non-scalar values such as
arrays in the extraction, then use JSON_EXTRACT
.
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_VALUE
JSON_VALUE(json_string_expr, json_path)
Description
Extracts a scalar value and then returns it as a string. A scalar value can represent a string, integer, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. Ifjson_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.
If you only want to extract scalar values such strings, integers, and booleans,
then use JSON_VALUE
. If you want to include non-scalar values such as arrays
in the extraction, then use JSON_QUERY
.
Return type
STRING
Examples
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 |
+-------+
JSON_EXTRACT_ARRAY
JSON_EXTRACT_ARRAY(json_string_expr[, json_path])
Description
Extracts an array of JSON values, such as arrays or objects, and JSON-formatted scalar values, such as strings, integers, 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_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that the entire JSON-formatted string is analyzed.
Return type
ARRAY<JSON-FORMATTED STRING>
Examples
This extracts the items in a JSON-formatted string to a string array:
SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array;
+--------------+
| string_array |
+--------------+
| [1, 2, 3] |
+--------------+
This extracts a string array and converts it to an integer array:
SELECT ARRAY(
SELECT CAST(integer_element AS INT64)
FROM UNNEST(
JSON_EXTRACT_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array;
+---------------+
| integer_array |
+---------------+
| [1, 2, 3] |
+---------------+
This extracts string values in a JSON-formatted string to an array:
-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;
+---------------------------------+
| string_array |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+
-- Strips the double quotes
SELECT ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;
+---------------------------+
| string_array |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+
This extracts only the items in the fruit
property to an array:
SELECT JSON_EXTRACT_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_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;
SELECT JSON_EXTRACT_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 single quotes and brackets, [' ']
. For example:
SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;
+-----------+
| hello |
+-----------+
| ["world"] |
+-----------+
The following examples explore how invalid requests and empty arrays are handled:
- If a JSONPath is invalid, an error is thrown.
- If a JSON-formatted string is invalid, the output is NULL.
- It is okay to have empty arrays in the JSON-formatted string.
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;
-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') AS result;
+--------+
| result |
+--------+
| [] |
+--------+
JSON_QUERY_ARRAY
JSON_QUERY_ARRAY(json_string_expr[, json_path])
Description
Extracts an array of JSON values, such as arrays or objects, and JSON-formatted scalar values, such as strings, integers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that the entire JSON-formatted string 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 a string array and converts it to an integer array:
SELECT ARRAY(
SELECT CAST(integer_element AS INT64)
FROM UNNEST(
JSON_QUERY_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array;
+---------------+
| integer_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"] |
+---------------------------------+
-- Strips the double quotes
SELECT ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) 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_EXTRACT_STRING_ARRAY
JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])
Description
Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, integer, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
json_string_expr
: A JSON-formatted string. For example:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that the entire JSON-formatted string is analyzed.
Return type
ARRAY<STRING>
Examples
The following example compares how results are returned for the
JSON_EXTRACT_ARRAY
and JSON_EXTRACT_STRING_ARRAY
functions.
SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;
+-----------------------+-------------------+
| json_array | string_array |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+
This extracts the items in a JSON-formatted string to a string array:
-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;
+-------------------+
| string_array |
+-------------------+
| [foo, bar, baz] |
+-------------------+
This extracts a string array and converts it to an integer array:
SELECT ARRAY(
SELECT CAST(integer_element AS INT64)
FROM UNNEST(
JSON_EXTRACT_STRING_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array;
+---------------+
| integer_array |
+---------------+
| [1, 2, 3] |
+---------------+
These are equivalent:
SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;
SELECT JSON_EXTRACT_STRING_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 single quotes and brackets: [' ']
. For example:
SELECT JSON_EXTRACT_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
The following examples explore how invalid requests and empty arrays are handled:
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;
-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;
+--------+
| result |
+--------+
| [] |
+--------+
-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;
+--------------+
| string_value |
+--------------+
| world |
| 1 |
| NULL |
+--------------+
JSON_VALUE_ARRAY
JSON_VALUE_ARRAY(json_string_expr[, json_path])
Description
Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, integer, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.
json_string_expr
: A JSON-formatted string. For example:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path
: The JSONPath. This identifies the value or values that you want to obtain from the JSON-formatted string. If this optional parameter is not provided, then the JSONPath$
symbol is applied, which means that the entire JSON-formatted string is analyzed.
Return type
ARRAY<STRING>
Examples
The following example compares how results are returned for the
JSON_QUERY_ARRAY
and JSON_VALUE_ARRAY
functions.
SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;
+-----------------------+-------------------+
| json_array | string_array |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+
This extracts the items in a JSON-formatted string to a string array:
-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;
+-----------------+
| string_array |
+-----------------+
| [foo, bar, baz] |
+-----------------+
This extracts a string array and converts it to an integer array:
SELECT ARRAY(
SELECT CAST(integer_element AS INT64)
FROM UNNEST(
JSON_VALUE_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array;
+---------------+
| integer_array |
+---------------+
| [1, 2, 3] |
+---------------+
These are equivalent:
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_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_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
The following examples explore how invalid requests and empty arrays are handled:
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;
-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;
+--------+
| result |
+--------+
| [] |
+--------+
-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;
+--------------+
| string_value |
+--------------+
| world |
| 1 |
| NULL |
+--------------+
TO_JSON_STRING
TO_JSON_STRING(value[, pretty_print])
Description
Returns a JSON-formatted string representation of value
. This function
supports an optional boolean parameter called pretty_print
. If pretty_print
is true
, the returned value is formatted for easy readability.
Input data type | Returned value |
---|---|
NULL of any type | null |
BOOL | true or false . |
INT64 | Same as -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993"
|
NUMERIC, BIGNUMERIC | Same as -1 0 "9007199254740993" "123.56"
|
FLOAT64 | +/-inf and NaN are represented as
Infinity , -Infinity , and NaN ,
respectively.
Otherwise, the same as |
STRING | Quoted string value, escaped according to the JSON standard.
Specifically, " , \ , and the control characters
from U+0000 to U+001F are escaped. |
BYTES | Quoted RFC 4648 base64-escaped value. For example:
|
DATE | Quoted date. For example: "2017-03-06"
|
TIMESTAMP | Quoted ISO 8601 date-time, where T separates the date and time and Zulu/UTC represents the time zone. For example: "2017-03-06T12:34:56.789012Z"
|
DATETIME | Quoted ISO 8601 date-time, where T separates the date and time. For example: "2017-03-06T12:34:56.789012"
|
TIME | Quoted ISO 8601 time. For example: "12:34:56.789012" |
ARRAY |
Array of zero or more elements. Each element is formatted according to its type. Example without formatting: ["red", "blue", "green"] Example with formatting: [ "red", "blue", "green" ] |
STRUCT |
Object that contains zero or more key/value pairs. Each value is formatted according to its type. Example without formatting: {"colors":["red","blue"],"purchases":12,"inStock": true} Example with formatting: { "color":[ "red", "blue" ] "purchases":12, "inStock": true }
Fields with duplicate names might result in unparseable JSON. 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,
|
Return type
A JSON-formatted STRING
Examples
Convert rows in a table to JSON.
With CoordinatesTable AS (
(SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
(SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
(SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable AS t;
+----+-------------+--------------------------------+
| id | coordinates | json_data |
+----+-------------+--------------------------------+
| 1 | [10, 20] | {"id":1,"coordinates":[10,20]} |
| 2 | [30, 40] | {"id":2,"coordinates":[30,40]} |
| 3 | [50, 60] | {"id":3,"coordinates":[50,60]} |
+----+-------------+--------------------------------+
Convert rows in a table to JSON with formatting.
With CoordinatesTable AS (
(SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
(SELECT 2 AS id, [30,40] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable AS t;
+----+-------------+--------------------+
| id | coordinates | json_data |
+----+-------------+--------------------+
| 1 | [10, 20] | { |
| | | "id": 1, |
| | | "coordinates": [ |
| | | 10, |
| | | 20 |
| | | ] |
| | | } |
+----+-------------+--------------------+
| 2 | [30, 40] | { |
| | | "id": 2, |
| | | "coordinates": [ |
| | | 30, |
| | | 40 |
| | | ] |
| | | } |
+----+-------------+--------------------+
JSONPath
Most JSON functions pass in a json_string_expr
and json_path
parameter. The json_string_expr
parameter passes in a JSON-formatted
string, and the json_path
parameter identifies the value or
values you want to obtain from the JSON-formatted string.
The json_string_expr
parameter must be a JSON string that is
formatted like this:
{"class" : {"students" : [{"name" : "Jane"}]}}
You construct the json_path
parameter using the
JSONPath format. As part of this format, this parameter must start
with a $
symbol, which refers to the outermost level of the JSON-formatted
string. You can identify child values using dots. If the JSON object is an
array, you can use brackets to specify the array index. If the keys contain
$
, dots, or brackets, refer to each JSON function for how to escape
them.
JSONPath | Description | Example | Result using the above json_string_expr |
---|---|---|---|
$ | Root object or element | "$" | {"class":{"students":[{"name":"Jane"}]}} |
. | Child operator | "$.class.students" | [{"name":"Jane"}] |
[] | Subscript operator | "$.class.students[0]" | {"name":"Jane"} |
A JSON functions returns NULL
if the json_path
parameter does
not match a value in json_string_expr
. 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 is invalid, the function raises an error.