标准 SQL 中的 JSON 函数

BigQuery 支持帮助您检索 JSON 格式字符串内存储的数据的函数,以及帮助您将数据转化为 JSON 格式字符串的函数。

JSON_EXTRACT 或 JSON_EXTRACT_SCALAR

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

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

说明

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

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

json_path_string_literal 参数标识您希望从 JSON 格式的字符串中获取的一个或多个值。您应使用 JSONPath 格式构造此参数。作为此格式的一部分,此参数必须以 $ 符号开头,这个符号标识的就是 JSON 格式字符串的最外层。您可以使用圆点或括号表示法来标识子值。如果 JSON 对象是数组,则可以使用括号指定数组索引。

JSONPath 说明
$ 根对象或元素
. 或 [] 子运算符
[] 下标运算符

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

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

如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用单引号和括号 [' '] 对这些字符进行转义。例如:

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

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

示例

SELECT JSON_EXTRACT(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_EXTRACT(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_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

以上查询会生成以下结果:

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"Jamie"} |
+-------------------+
SELECT JSON_EXTRACT(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_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

以上查询会生成以下结果:

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

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

说明

返回 value 的 JSON 格式的字符串表示形式。此函数支持支持可选的 pretty_print 参数。如果存在 pretty_print,返回值将以易读性为目标进行格式设置。

输入数据类型 返回值
任意类型的 NULL null
BOOL truefalse
INT64

value 在 [-253, 253] 范围(即可以无损地表示为 IEEE 754 双精度浮点数的整数的范围)内时,与 CAST(value AS STRING) 相同。此范围以外的值采用带引号的字符串形式表示。例如:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 大于 253,因此表示为带引号的字符串。

NUMERIC

value 在 [-253, 253] 范围内并且没有小数部分时,与 CAST(value AS STRING) 相同。此范围以外的值采用带引号的字符串形式表示。例如:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-infNaN 分别表示为 Infinity-InfinityNaN

其余情况与 CAST(value AS STRING) 相同。

STRING 带引号的字符串值,根据 JSON 标准进行转义。具体来说,"\ 和从 U+0000U+001F 的控制字符会予以转义。
BYTES

带引号的 RFC 4648 base64 转义值。例如:

"R29vZ2xl" 是字节 b"Google" 的 base64 表示

DATE

带引号的日期。例如:

"2017-03-06"
TIMESTAMP

带引号的 ISO 8601 日期时间,其中 T 分隔日期和时间,Zulu/UTC 表示时区。例如:

"2017-03-06T12:34:56.789012Z"
DATETIME

带引号的 ISO 8601 日期时间,其中 T 分隔日期和时间。例如:

"2017-03-06T12:34:56.789012"
TIME

带引号的 ISO 8601 时间。例如:

"12:34:56.789012"
ARRAY

[elem1,elem2,...],其中每个 elem 均根据元素类型进行格式化。

格式化示例:

[
  elem1,
  elem2,
  ...
]

其中每个元素均根据元素类型进行格式化。空数组表示为 []

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

其中的各 field_value 根据其类型进行格式化。

格式化示例:

{
  "field_name1": field_value1,
  "field_name2": field_value2,
  ...
}

其中的各 field_value 根据其类型进行格式化。如果 field_value 是非空 ARRAY 或 STRUCT,元素会缩进到适当的级别。空结构体表示为 {}

名称重复的字段可能导致无法解析的 JSON。匿名字段用 "" 表示。

无效 UTF-8 字段名称可能导致无法解析的 JSON。字符串值根据 JSON 标准进行转义。具体来说,"\ 和从 U+0000U+001F 的控制字符会予以转义。

返回类型

值的 JSON 字符串表示形式。

示例

将表中的行转换为 JSON。

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

以上查询会生成以下结果:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

通过格式化将表中的行转换为 JSON。

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

以上查询会生成以下结果:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面