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.

JSON_QUERY or JSON_VALUE

JSON_QUERY(json_string_expr, json_path_format), which returns JSON values as STRINGs.

JSON_VALUE(json_string_expr, json_path_format), which returns scalar JSON values as STRINGs.

Description

Extracts JSON values or JSON scalar values as strings.

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

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

• json_path_format: The JSONpath format. This identifies the value or values you want to obtain from the JSON-formatted string. If json_path_format returns a JSON null, this is converted into a SQL NULL.

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.

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"}] |
+------------------------------------+

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;

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

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 format

Most JSON functions pass in a json_string_expr and json_path_format parameter. The json_string_expr parameter passes in a JSON-formatted string, and the json_path_format 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_format 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_format 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.