JSON functions

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

Categories

The JSON functions are grouped into the following categories based on theirbehavior:

CategoryFunctionsDescription
Standard extractorsJSON_QUERY
JSON_VALUE
JSON_QUERY_ARRAY
 Functions that extract JSON data.
Legacy extractorsJSON_EXTRACT
JSON_EXTRACT_SCALAR
 Functions that extract JSON data.
While these functions are supported by GoogleSQL, we recommend using thestandard extractor functions.
Other convertersTO_JSON_STRING
 Other conversion functions from or to JSON.

Function list

NameSummary
JSON_EXTRACT (Deprecated) Extracts a JSON value and converts it to a SQL JSON-formattedSTRING value.
JSON_EXTRACT_SCALAR (Deprecated) Extracts a JSON scalar value and converts it to a SQLSTRING value.
JSON_QUERY Extracts a JSON value and converts it to a SQL JSON-formattedSTRING value.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQLARRAY<JSON-formatted STRING> value.
JSON_VALUE Extracts a JSON scalar value and converts it to a SQLSTRING value.
TO_JSON_STRING Converts a SQL value to a JSON-formattedSTRING value.

JSON_EXTRACT

JSON_EXTRACT(json_string_expr,json_path)

Description

Extracts a JSON value and converts it to aSQL JSON-formattedSTRING value.This function uses single quotes and brackets to escape invalidJSONPath characters in JSON keys. For example:['a.b'].

Arguments:

  • json_string_expr: A JSON-formatted string. For example:

    '{"class": {"students": [{"name": "Jane"}]}}'

    Extracts a SQLNULL when a JSON-formatted stringnull is encountered.For example:

    SELECTJSON_EXTRACT("null","$")-- Returns a SQL NULL

  • json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input.

Return type

A JSON-formattedSTRING

Examples

In the following examples, JSON data is extracted and returned asJSON-formatted strings.

SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string                                          | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"Jane"}]}}                  | +-----------------------------------------------------------*/
SELECTJSON_EXTRACT('{"class": {"students": []}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string                                          | +-----------------------------------------------------------+ | {"class":{"students":[]}}                                 | +-----------------------------------------------------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string                                          | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} | +-----------------------------------------------------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student   | +-----------------+ | {"name":"Jane"} | +-----------------*/
SELECTJSON_EXTRACT('{"class": {"students": []}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student   | +-----------------+ | NULL            | +-----------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student   | +-----------------+ | {"name":"John"} | +-----------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL           | +----------------*/
SELECTJSON_EXTRACT('{"class": {"students": []}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL           | +----------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": null}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL           | +----------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | "Jamie"        | +----------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "Jane"}]}}',"$.class['students']")ASstudent_names;/*------------------------------------+ | student_names                      | +------------------------------------+ | [{"name":"Jane"}]                  | +------------------------------------*/
SELECTJSON_EXTRACT('{"class": {"students": []}}',"$.class['students']")ASstudent_names;/*------------------------------------+ | student_names                      | +------------------------------------+ | []                                 | +------------------------------------*/
SELECTJSON_EXTRACT('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}',"$.class['students']")ASstudent_names;/*------------------------------------+ | student_names                      | +------------------------------------+ | [{"name":"John"},{"name":"Jamie"}] | +------------------------------------*/
SELECTJSON_EXTRACT('{"a": null}',"$.a");-- Returns a SQL NULLSELECTJSON_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 SQLSTRING value.In addition, this function:

  • Removes the outermost quotes and unescapes the return values.
  • Returns a SQLNULL if a non-scalar value is selected.
  • Uses single quotes and brackets to escape invalidJSONPathcharacters in JSON keys. For example:['a.b'].

Arguments:

  • json_string_expr: A JSON-formatted string. For example:

    '{"name": "Jane", "age": "6"}'

  • json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input. If this optional parameter isn'tprovided, then the JSONPath$ symbol is applied, which means that all ofthe data is analyzed.

    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 isreturned.

Return type

STRING

Examples

The following example compares how results are returned for theJSON_EXTRACTandJSON_EXTRACT_SCALAR functions.

SELECTJSON_EXTRACT('{"name": "Jakob", "age": "6" }','$.name')ASjson_name,JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }','$.name')ASscalar_name,JSON_EXTRACT('{"name": "Jakob", "age": "6" }','$.age')ASjson_age,JSON_EXTRACT_SCALAR('{"name": "Jakob", "age": "6" }','$.age')ASscalar_age;/*-----------+-------------+----------+------------+ | json_name | scalar_name | json_age | scalar_age | +-----------+-------------+----------+------------+ | "Jakob"   | Jakob       | "6"      | 6          | +-----------+-------------+----------+------------*/
SELECTJSON_EXTRACT('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_extract,JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_extract_scalar;/*--------------------+---------------------+ | json_extract       | json_extract_scalar | +--------------------+---------------------+ | ["apple","banana"] | NULL                | +--------------------+---------------------*/

In cases where a JSON key uses invalid JSONPath characters, you can escape thosecharacters using single quotes and brackets,[' ']. For example:

SELECTJSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}',"$['a.b'].c")AShello;/*-------+ | hello | +-------+ | world | +-------*/

JSON_QUERY

JSON_QUERY(json_string_expr,json_path)

Description

Extracts a JSON value and converts it to a SQLJSON-formattedSTRING value.This function uses double quotes to escape invalidJSONPath characters in JSON keys. For example:"a.b".

Arguments:

  • json_string_expr: A JSON-formatted string. For example:

    '{"class": {"students": [{"name": "Jane"}]}}'

    Extracts a SQLNULL when a JSON-formatted stringnull is encountered.For example:

    SELECTJSON_QUERY("null","$")-- Returns a SQL NULL

  • json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input.

Return type

A JSON-formattedSTRING

Examples

In the following examples, JSON data is extracted and returned asJSON-formatted strings.

SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string                                          | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"Jane"}]}}                  | +-----------------------------------------------------------*/
SELECTJSON_QUERY('{"class": {"students": []}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string                                          | +-----------------------------------------------------------+ | {"class":{"students":[]}}                                 | +-----------------------------------------------------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "John"},{"name": "Jamie"}]}}','$')ASjson_text_string;/*-----------------------------------------------------------+ | json_text_string                                          | +-----------------------------------------------------------+ | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} | +-----------------------------------------------------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student   | +-----------------+ | {"name":"Jane"} | +-----------------*/
SELECTJSON_QUERY('{"class": {"students": []}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student   | +-----------------+ | NULL            | +-----------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[0]')ASfirst_student;/*-----------------+ | first_student   | +-----------------+ | {"name":"John"} | +-----------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL           | +----------------*/
SELECTJSON_QUERY('{"class": {"students": []}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL           | +----------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": null}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | NULL           | +----------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class.students[1].name')ASsecond_student;/*----------------+ | second_student | +----------------+ | "Jamie"        | +----------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "Jane"}]}}','$.class."students"')ASstudent_names;/*------------------------------------+ | student_names                      | +------------------------------------+ | [{"name":"Jane"}]                  | +------------------------------------*/
SELECTJSON_QUERY('{"class": {"students": []}}','$.class."students"')ASstudent_names;/*------------------------------------+ | student_names                      | +------------------------------------+ | []                                 | +------------------------------------*/
SELECTJSON_QUERY('{"class": {"students": [{"name": "John"}, {"name": "Jamie"}]}}','$.class."students"')ASstudent_names;/*------------------------------------+ | student_names                      | +------------------------------------+ | [{"name":"John"},{"name":"Jamie"}] | +------------------------------------*/
SELECTJSON_QUERY('{"a": null}',"$.a");-- Returns a SQL NULLSELECTJSON_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 toa SQLARRAY<JSON-formatted STRING> value.In addition, this function uses double quotes to escape invalidJSONPath characters in JSON keys. For example:"a.b".

Arguments:

  • json_string_expr: A JSON-formatted string. For example:

    '["a", "b", {"key": "c"}]'

  • json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input. If this optional parameter isn'tprovided, then the JSONPath$ symbol is applied, which means that all ofthe data is analyzed.

Return type

ARRAY<JSON-formatted STRING>

Examples

This extracts the items in a JSON-formatted string to a string array:

SELECTJSON_QUERY_ARRAY('[1, 2, 3]')ASstring_array;/*--------------+ | string_array | +--------------+ | [1, 2, 3]    | +--------------*/

This extracts string values in a JSON-formatted string to an array:

-- Doesn't strip the double quotesSELECTJSON_QUERY_ARRAY('["apples", "oranges", "grapes"]','$')ASstring_array;/*---------------------------------+ | string_array                    | +---------------------------------+ | ["apples", "oranges", "grapes"] | +---------------------------------*/

This extracts only the items in thefruit property to an array:

SELECTJSON_QUERY_ARRAY('{"fruit": [{"apples": 5, "oranges": 10}, {"apples": 2, "oranges": 4}], "vegetables": [{"lettuce": 7, "kale": 8}]}','$.fruit')ASstring_array;/*-------------------------------------------------------+ | string_array                                          | +-------------------------------------------------------+ | [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] | +-------------------------------------------------------*/

These are equivalent:

SELECTJSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$.fruits')ASstring_array;SELECTJSON_QUERY_ARRAY('{"fruits": ["apples", "oranges", "grapes"]}','$."fruits"')ASstring_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 thosecharacters using double quotes:" ". For example:

SELECTJSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}','$."a.b".c')AShello;/*-----------+ | hello     | +-----------+ | ["world"] | +-----------*/

The following examples show how invalid requests and empty arrays are handled:

-- An error is returned if you provide an invalid JSONPath.SELECTJSON_QUERY_ARRAY('["foo", "bar", "baz"]','INVALID_JSONPath')ASresult;-- If the JSONPath doesn't refer to an array, then NULL is returned.SELECTJSON_QUERY_ARRAY('{"a": "foo"}','$.a')ASresult;/*--------+ | result | +--------+ | NULL   | +--------*/-- If a key that doesn't exist is specified, then the result is NULL.SELECTJSON_QUERY_ARRAY('{"a": "foo"}','$.b')ASresult;/*--------+ | result | +--------+ | NULL   | +--------*/-- Empty arrays in JSON-formatted strings are supported.SELECTJSON_QUERY_ARRAY('{"a": "foo", "b": []}','$.b')ASresult;/*--------+ | result | +--------+ | []     | +--------*/

JSON_VALUE

JSON_VALUE(json_string_expr[,json_path])

Description

Extracts a JSON scalar value and converts it to a SQLSTRING value.In addition, this function:

  • Removes the outermost quotes and unescapes the values.
  • Returns a SQLNULL if a non-scalar value is selected.
  • Uses double quotes to escape invalidJSONPath charactersin JSON keys. For example:"a.b".

Arguments:

  • json_string_expr: A JSON-formatted string. For example:

    '{"name": "Jakob", "age": "6"}'

  • json_path: TheJSONPath. This identifies the data thatyou want to obtain from the input. If this optional parameter isn'tprovided, then the JSONPath$ symbol is applied, which means that all ofthe data is analyzed.

    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 isreturned.

Return type

STRING

Examples

The following example compares how results are returned for theJSON_QUERYandJSON_VALUE functions.

SELECTJSON_QUERY('{"name": "Jakob", "age": "6"}','$.name')ASjson_name,JSON_VALUE('{"name": "Jakob", "age": "6"}','$.name')ASscalar_name,JSON_QUERY('{"name": "Jakob", "age": "6"}','$.age')ASjson_age,JSON_VALUE('{"name": "Jakob", "age": "6"}','$.age')ASscalar_age;/*-----------+-------------+----------+------------+ | json_name | scalar_name | json_age | scalar_age | +-----------+-------------+----------+------------+ | "Jakob"   | Jakob       | "6"      | 6          | +-----------+-------------+----------+------------*/
SELECTJSON_QUERY('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_query,JSON_VALUE('{"fruits": ["apple", "banana"]}','$.fruits')ASjson_value;/*--------------------+------------+ | json_query         | json_value | +--------------------+------------+ | ["apple","banana"] | NULL       | +--------------------+------------*/

In cases where a JSON key uses invalid JSONPath characters, you can escape thosecharacters using double quotes. For example:

SELECTJSON_VALUE('{"a.b": {"c": "world"}}','$."a.b".c')AShello;/*-------+ | hello | +-------+ | world | +-------*/

TO_JSON_STRING

TO_JSON_STRING(value[,pretty_print])

Description

Converts a SQL value to a JSON-formattedSTRING value.

Arguments:

  • value: A SQL value. You can review the GoogleSQL data types thatthis function supports and their JSON encodingshere.
  • pretty_print: Optional boolean parameter. Ifpretty_print istrue, thereturned value is formatted for easy readability. Ifpretty_print isNULL, the function returnsNULL, regardless of thevalue argument.

Return type

A JSON-formattedSTRING

Examples

The following query converts aSTRUCT value to a JSON-formatted string:

SELECTTO_JSON_STRING(STRUCT(1ASid,[10,20]AScoordinates))ASjson_data/*--------------------------------+ | json_data                      | +--------------------------------+ | {"id":1,"coordinates":[10,20]} | +--------------------------------*/

The following query converts aSTRUCT value to a JSON-formatted string that iseasy to read:

SELECTTO_JSON_STRING(STRUCT(1ASid,[10,20]AScoordinates),true)ASjson_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 SQLTo JSONExamples
NULL

null

 SQL input:NULL
JSON output:null
BOOLboolean SQL input:TRUE
JSON output:true
SQL input:FALSE
JSON output:false
INT64

number or string

Encoded as a number when the value is in the range of [-253, 253], which is the range of integers that can be represented losslessly as IEEE 754 double-precision floating point numbers. A value outside of this range is encoded as a string.

 SQL input:9007199254740992
JSON output:9007199254740992
SQL input:9007199254740993
JSON output:"9007199254740993"
FLOAT32
FLOAT64

number or string

+/-inf andNaN are encoded asInfinity,-Infinity, andNaN. 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 fromU+0000 toU+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"
DATEstring 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.

ForTO_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 fromU+0000 toU+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 toobtain from a JSON-formatted string.

If a key in a JSON functions contains a JSON format operator, refer to eachJSON function for how to escape them.

A JSON function returnsNULL if the JSONPath format doesn't match a value ina JSON-formatted string. If the selected value for a scalar function isn'tscalar, such as an object or an array, the function returnsNULL. If theJSONPath format is invalid, an error is produced.

Operators for JSONPath

The JSONPath format supports these operators:

OperatorDescriptionExamples
$ 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"

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-17 UTC.