JSON 函数

Cloud Spanner SQL 支持可检索 JSON 格式字符串内存储的数据的函数,以及可将数据转化为 JSON 格式字符串的函数。

JSON_QUERY 或 JSON_VALUE

JSON_QUERY(json_string_expr, json_path_string_literal),返回 STRING 格式的 JSON 值。

JSON_VALUE(json_string_expr, json_path_string_literal),返回 STRING 格式的标量 JSON 值。

说明

提取 STRING 格式的 JSON 值或 JSON 标量值。

  • json_string_expr:JSON 格式的字符串。例如:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literalJSONpath 格式。 这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果 json_path_string_literal 返回 JSON null,则将其转换为 SQL NULL

如果 JSON 密钥使用无效的 JSONPath 字符,则可以使用双引号转义这些字符。

示例

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      |
+-----------+-------------+----------+--------+

如果 JSON 密钥使用无效的 JSONPath 字符,则可以使用双引号转义这些字符。例如:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSONPath 格式

大多数 JSON 函数都会传入 json_string_exprjson_path_string_literal 参数。json_string_expr 参数传入 JSON 格式的字符串,而 json_path_string_literal 参数用于标识要从 JSON 格式的字符串中获取的一个或多个值。

json_string_expr 参数必须是格式如下的 JSON 字符串:

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

您可以使用 JSONPath 格式构建 json_path_string_literal 参数。作为此格式的一部分,此参数必须以 $ 符号开头,这个符号标识 JSON 格式字符串的最外层。您可以使用英文句点来标识子值。如果 JSON 对象是数组,则可以使用英文括号指定数组索引。如果键包含 $、英文句点或英文方括号,请参阅各 JSON 函数以了解如何对其进行转义。

JSONPath 说明 示例 使用上述 json_string_expr 的结果
$ 根对象或元素 "$" {"class":{"students":[{"name":"Jane"}]}}
. 子运算符 "$.class.students" [{"name":"Jane"}]
[] 下标运算符 "$.class.students[0]" {"name":"Jane"}

如果 json_path_string_literal 参数与 json_string_expr 中的值不匹配,则 JSON 函数会返回 NULL。如果标量函数的选定值不是标量,例如一个对象或一个数组,该函数会返回 NULL

如果 JSONPath 无效,则函数会引发错误。