JSON functions

GoogleSQL for Bigtable supports the following functions, which can retrieve and transform JSON data.

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 string null 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 isn't 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.

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 string null 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 isn't 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 doesn't refer to an array, then NULL is returned. 
 SELECT 
  
 JSON_QUERY_ARRAY 
 ( 
 '{"a": "foo"}' 
 , 
  
 '$.a' 
 ) 
  
 AS 
  
 result 
 ; 
 /*--------* 
 | result | 
 +--------+ 
 | NULL   | 
 *--------*/ 
 -- If a key that doesn't 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 isn't 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.

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. If pretty_print is true , 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 [-2 ⁵³ , 2 ⁵³ ], 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 `+/-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"`
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 `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 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 doesn't match a value in a JSON-formatted string. If the selected value for a scalar function isn't 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: `'{"class" : {"students" : [{"name" : "Jane"}]}}'` JSON path: `"$"` JSON result: `{"class":{"students":[{"name":"Jane"}]}}`
`.`	Child operator. You can identify child values using dot-notation.	JSON-formatted string: `'{"class" : {"students" : [{"name" : "Jane"}]}}'` JSON path: `"$.class.students"` JSON result: `[{"name":"Jane"}]`
`[]`	Subscript operator. If the object is a JSON array, you can use brackets to specify the array index.	JSON-formatted string: `'{"class" : {"students" : [{"name" : "Jane"}]}}'` JSON path: `"$.class.students[0]"` JSON result: `{"name":"Jane"}`
`[][]` `[][][]...`	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: `'{"a": [["b", "c"], "d"], "e":"f"}'` JSON path: `"$.a[0][1]"` JSON result: `"c"`

JSON functions Stay organized with collections Save and categorize content based on your preferences.

Categories

Function list

JSON_EXTRACT

JSON_EXTRACT_SCALAR

JSON_QUERY

JSON_QUERY_ARRAY

JSON_VALUE

TO_JSON_STRING

Supplemental materials

JSON encodings

JSONPath format

Operators for JSONPath

JSON functions

`JSON_EXTRACT`

`JSON_EXTRACT_SCALAR`

`JSON_QUERY`

`JSON_QUERY_ARRAY`

`JSON_VALUE`

`TO_JSON_STRING`