- Home
- Docs
- BigQuery
- Documentation
- Reference
JSON functions
BigQuery supports the following functions, which can retrieve and transform JSON data.
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_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 |
| 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_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 |
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_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 |
| 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_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 |
Other JSON functions
| TO_JSON_STRING | Takes a SQL value and returns a JSON-formatted string representation of the 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 scalar value, such as a string, number, 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"}]}}'Extracts a SQL NULL when a JSON-formatted string "null" is encountered. For example:
SELECT JSON_EXTRACT["null", "$"] -- Returns a SQL NULLjson_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.
SELECT JSON_EXTRACT['{"a":null}', "$.a"]; -- Returns a SQL NULL SELECT JSON_EXTRACT['{"a":null}', "$.b"]; -- Returns a SQL NULL
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, numbers, and booleans, then use JSON_EXTRACT_SCALAR.
Return type
A JSON-formatted STRING
Examples
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
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 scalar value, such as a string, number, 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"}]}}'Extracts a SQL NULL when a JSON-formatted string "null" is encountered. For example:
SELECT JSON_QUERY["null", "$"] -- Returns a SQL NULLjson_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.
SELECT JSON_QUERY['{"a":null}', "$.a"]; -- Returns a SQL NULL SELECT JSON_QUERY['{"a":null}', "$.b"]; -- Returns a SQL NULL
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, numbers, and booleans, then use JSON_VALUE.
Return type
A JSON-formatted STRING
Examples
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
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, number, 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 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 JSON null or a non-scalar value [in other words, if json_path refers to an object or an array], then a SQL NULL is returned. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that the entire JSON-formatted string is analyzed.
If you only want to extract scalar values such strings, numbers, 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.
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, number, 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 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 JSON null or a non-scalar value [in other words, if json_path refers to an object or an array], then a SQL NULL is returned. If this optional parameter is not provided, then the JSONPath $ symbol is applied, which means that the entire JSON-formatted string is analyzed.
If you only want to extract scalar values such strings, numbers, 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
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 | +-------+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 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_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 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
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.
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 scalar values, such as strings, numbers, 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 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
Examples
This extracts the items in a JSON-formatted string to a string array:
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_VALUE[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, number, 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 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
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, number, 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 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
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
Takes a SQL value and returns a JSON-formatted string representation of the value. The value must be a supported BigQuery data type. You can review the BigQuery data types that this function supports and their JSON encodings here.
This function supports an optional boolean parameter called pretty_print. If pretty_print is true, the returned value is formatted for easy readability.
Return type
A JSON-formatted STRING
Examples
Convert rows in a table to JSON-formatted strings.
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-formatted strings that are easy to read.
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 | | | | ] | | | | } | +----+-------------+--------------------+JSON encodings
The following table includes common encodings that are used when a SQL value is encoded as JSON value with the TO_JSON_STRING function.
| 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" |
| NUMERIC BIGNUMERIC | number or string Encoded as a number when the value is in the range of [-253, 253] and has no fractional part. A value outside of this range is encoded as a string. | SQL input: -1 JSON output: -1 SQL input: 0 JSON output: 0 SQL input: 9007199254740993 JSON output: "9007199254740993" SQL input: 123.56 JSON output: "123.56" |
| FLOAT64 | number or string +/-inf and NaN are encoded as Infinity, -Infinity, and NaN. Otherwise, this value is encoded as a number. | 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, ", \, and the control characters from U+0000 to U+001F are escaped. | 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" |
| 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" |
| DATETIME | string Encoded as ISO 8601 date and time, where T separates the date and time. | SQL input: DATETIME '2017-03-06 12:34:56.789012' JSON output: "2017-03-06T12:34:56.789012" |
| TIME | string Encoded as ISO 8601 time. | SQL input: TIME '12:34:56.789012' JSON output: "12:34:56.789012" |
| 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 TO_JSON_STRING, a field and any duplicates of this field are included in the output string. 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, ", \, and the control characters from U+0000 to U+001F are escaped. | SQL input: STRUCT[12 AS purchases, TRUE AS inStock] JSON output: {"inStock": true,"purchases":12} |
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.
| $ | 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.