JSON functions

Cloud Spanner SQL 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

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

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. If json_path returns a JSON null, then this is converted into 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, 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_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. 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 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 |
+-------+

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.