JSON 函数

Cloud Spanner SQL 支持以下函数,这些函数可以检索和转换 JSON 数据。

函数概览

以下函数使用英文双引号来转义无效的 JSONPath 字符:"a.b"

此行为与 ANSI 标准一致。

JSON 函数 说明 返回类型
JSON_QUERY 提取 JSON 值(如数组或对象)或 JSON 标量值(如字符串、整数或布尔值)。 JSON 格式的 STRING
JSON_VALUE 提取标量值。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义值。如果选择了非标量值,则返回 SQL NULL STRING

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

说明

提取 JSON 值(如数组或对象)或 JSON 标量值(如字符串、整数或布尔值)。如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文双引号对这些字符进行转义。

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

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

    当遇到 JSON 格式的字符串“null”时,提取 SQL NULL。例如:

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。

    SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
    SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
    

如果您要在提取中包含非标量值(如数组),请使用 JSON_QUERY。如果您只想提取标量值(如字符串、整数和布尔值),请使用 JSON_VALUE

返回类型

JSON 格式的 STRING

示例

在以下示例中,系统会提取 JSON 数据并以 JSON 格式字符串形式返回。

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])

说明

提取标量值,然后将其作为字符串返回。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义返回值。如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文双引号对这些字符进行转义。

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

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果 json_path 返回 JSON null 或非标量值(换句话说,如果 json_path 表示对象或数组),则返回 SQL NULL。 如果未提供此可选参数,则应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

如果您只想提取标量值(如字符串、整数和布尔值),那么使用 JSON_VALUE。如果您要在提取中包含非标量值(如数组),那么使用 JSON_QUERY

返回类型

STRING

示例

以下示例将如何返回 JSON_QUERYJSON_VALUE 函数的结果进行比较。

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

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

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

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

JSONPath

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

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

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

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

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

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

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