此页面由 Cloud Translation API 翻译。

处理 GoogleSQL 中的 JSON 数据

本文档介绍如何创建包含 JSON 列的表、将 JSON 数据插入 BigQuery 表以及查询 JSON 数据。

BigQuery 使用 JSON 数据类型原生支持 JSON 数据。

JSON 是一种广泛使用的格式，允许半结构化数据，因为它不需要架构。应用可以使用“读取时架构”方法，通过这种方法，应用可以提取数据，然后根据这些数据架构的相关假设进行查询。此方法与 BigQuery 中的 STRUCT 类型不同，后者需要一个固定架构，该架构对存储在 STRUCT 类型的列中的所有值强制执行。

通过使用 JSON 数据类型，您可以将半结构化 JSON 加载到 BigQuery 中，而无需预先为 JSON 数据提供架构。这允许您存储和查询并不总是遵循固定架构和数据类型的数据。通过提取 JSON 数据作为 JSON 数据类型，BigQuery 可以单独编码和处理每个 JSON 字段。然后，您可以使用字段访问运算符查询 JSON 数据中的字段和数组元素值，这使 JSON 查询变得直观且经济实惠。

限制

如果您使用批量加载作业将 JSON 数据注入到表中，则源数据必须采用 CSV、Avro 或 JSON 格式。不支持其他批量加载格式。
JSON 数据类型的嵌套上限为 500。
不能使用旧版 SQL 来查询包含 JSON 类型的表。
无法对 JSON 列应用行级访问权限政策。

如需了解 JSON 数据类型的属性，请参阅 JSON 类型。

创建具有 `JSON` 列的表。

您可以使用 SQL 或使用 bq 命令行工具创建包含 JSON 列的空表。

SQL

使用 CREATE TABLE 语句并声明一个 JSON 类型的列。

在 Google Cloud 控制台中，转到 BigQuery 页面。

转到 BigQuery

在查询编辑器中，输入以下语句：

CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);

点击运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

bq

使用 bq mk 命令并提供数据类型为 JSON 的表架构。

bq mk --table mydataset.table1 id:INT64,cart:JSON

您无法对表在 JSON 列上进行分区或聚簇，因为 JSON 类型上未定义等式和比较运算符。

创建 `JSON` 值

您可以通过以下方式创建 JSON 值：

使用 SQL 创建 JSON 字面量。
使用 PARSE_JSON 函数将 STRING 值转换为 JSON 值。
使用 TO_JSON 函数将 SQL 值转换为 JSON 值。
使用 JSON_ARRAY 函数从 SQL 值创建 JSON 数组。
使用 JSON_OBJECT 函数从键值对创建 JSON 对象。

创建 `JSON` 值

以下示例将 JSON 值插入表中：

INSERT INTO mydataset.table1 VALUES
(1, JSON '{"name": "Alice", "age": 30}'),
(2, JSON_ARRAY(10, ['foo', 'bar'], [20, 30])),
(3, JSON_OBJECT('foo', 10, 'bar', ['a', 'b']));

将 `STRING` 类型转换为 `JSON` 类型

以下示例使用 PARSE_JSON 函数转换 JSON 格式的 STRING 值。该示例会将现有表中的列转换为 JSON 类型，并将结果存储到新表中。

CREATE OR REPLACE TABLE mydataset.table_new
AS (
  SELECT
    id, SAFE.PARSE_JSON(cart) AS cart_json
  FROM
    mydataset.old_table
);

此示例中使用的 SAFE 前缀可确保将所有转换错误作为 NULL 值返回。

将架构化数据转换为 JSON

以下示例使用 JSON_OBJECT 函数将键值对转换为 JSON。

WITH Fruits AS (
SELECT 0 AS id, 'color' AS k, 'Red' AS v UNION ALL
SELECT 0, 'fruit', 'apple' UNION ALL
SELECT 1, 'fruit','banana' UNION ALL
SELECT 1, 'ripe', 'true'
)

SELECT JSON_OBJECT(ARRAY_AGG(k), ARRAY_AGG(v)) AS json_data
FROM Fruits
GROUP BY id

结果如下：

+----------------------------------+
| json_data                        |
+----------------------------------+
| {"color":"Red","fruit":"apple"}  |
| {"fruit":"banana","ripe":"true"} |
+----------------------------------+

将 SQL 类型转换为 `JSON` 类型

以下示例使用 TO_JSON 函数将 SQL STRUCT 值转换为 JSON 值：

SELECT TO_JSON(STRUCT(1 AS id, [10,20] AS coordinates)) AS pt;

结果如下：

+--------------------------------+
| pt                             |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

注入 JSON 数据

您可以通过以下方式将 JSON 数据注入到 BigQuery 表中：

使用批量加载作业可以将以下格式文件中的数据加载到 JSON 列。
- CSV
- Avro
- JSON
使用 BigQuery Storage Write API。
使用旧版 tabledata.insertAll 流式传输 API

从 CSV 文件加载

以下示例假定您有一个名为 file1.csv 的 CSV 文件，其中包含以下记录：

1,20
2,"""This is a string"""
3,"{""id"": 10, ""name"": ""Alice""}"

请注意，第二列包含编码为字符串的 JSON 数据。其中涉及对 CSV 格式的引号进行正确转义。在 CSV 格式中，使用两个字符序列 "" 对引号进行转义。

如需使用 bq 命令行工具加载此文件，请使用 bq load 命令：

bq load --source_format=CSV mydataset.table1 file1.csv id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

从以换行符分隔的 JSON 文件加载

以下示例假定您有一个名为 file1.jsonl 的文件，其中包含以下记录：

{"id": 1, "json_data": 20}
{"id": 2, "json_data": "This is a string"}
{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

如需使用 bq 命令行工具加载此文件，请使用 bq load 命令：

bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.table1 file1.jsonl id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

使用 Storage Write API

您可以使用 Storage Write API 提取 JSON 数据。以下示例使用 Storage Write API Python 客户端将数据写入具有 JSON 数据类型列的表中。

定义协议缓冲区以保存序列化的流式数据。JSON 数据编码为字符串。在以下示例中，json_col 字段包含 JSON 数据。

message SampleData {
  optional string string_col = 1;
  optional int64 int64_col = 2;
  optional string json_col = 3;
}

将每行的 JSON 数据格式设置为 STRING 值：

row.json_col = '{"a": 10, "b": "bar"}'
row.json_col = '"This is a string"' # The double-quoted string is the JSON value.
row.json_col = '10'

将行附加到写入流，如代码示例中所示。客户端库处理序列化到协议缓冲区格式。

如果您无法设置传入 JSON 数据的格式，则需要在代码中使用 json.dumps() 方法。示例如下：

import json

...

row.json_col = json.dumps({"a": 10, "b": "bar"})
row.json_col = json.dumps("This is a string") # The double-quoted string is the JSON value.
row.json_col = json.dumps(10)

...

使用旧版流式传输 API

以下示例从本地文件加载 JSON 数据，并使用旧版流式传输 API 将其流式传输到具有名为 json_data 的 JSON 数据类型列的 BigQuery 表。

from google.cloud import bigquery
import json

# TODO(developer): Replace these variables before running the sample.
project_id = 'MY_PROJECT_ID'
table_id = 'MY_TABLE_ID'

client = bigquery.Client(project=project_id)
table_obj = client.get_table(table_id)

# The column json_data is represented as a JSON data-type column.
rows_to_insert = [
    {"id": 1, "json_data": 20},
    {"id": 2, "json_data": "This is a string"},
    {"id": 3, "json_data": {"id": 10, "name": "Alice"}}
]

# If the column json_data is represented as a String data type, modify the rows_to_insert values:
#rows_to_insert = [
#    {"id": 1, "json_data": json.dumps(20)},
#    {"id": 2, "json_data": json.dumps("This is a string")},
#    {"id": 3, "json_data": json.dumps({"id": 10, "name": "Alice"})}
#]

# Throw errors if encountered.
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_insert_rows

errors = client.insert_rows(table=table_obj, rows=rows_to_insert)
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

如需了解详情，请参阅将数据流式插入到 BigQuery 中。

查询 JSON 数据

本部分介绍如何使用 GoogleSQL 从 JSON 中提取值。JSON 区分大小写，并且支持在字段和值中使用 UTF-8 格式。

本部分中的示例使用下表：

CREATE OR REPLACE TABLE mydataset.table1(id INT64, cart JSON);

INSERT INTO mydataset.table1 VALUES
(1, JSON """{
        "name": "Alice",
        "items": [
            {"product": "book", "price": 10},
            {"product": "food", "price": 5}
        ]
    }"""),
(2, JSON """{
        "name": "Bob",
        "items": [
            {"product": "pen", "price": 20}
        ]
    }""");

以 JSON 格式提取值

对于 BigQuery 中的 JSON 类型，您可以使用字段访问运算符访问 JSON 表达式中的字段。以下示例返回 cart 列的 name 字段。

SELECT cart.name
FROM mydataset.table1;

+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

如需访问数组元素，请使用 JSON 下标运算符。以下示例返回 items 数组的第一个元素：

SELECT
  cart.items[0] AS first_item
FROM mydataset.table1

+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+

您还可以使用 JSON 下标运算符按名称引用 JSON 对象的成员：

SELECT cart['name']
FROM mydataset.table1;

+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

对于下标操作，括号中的表达式可以是任意字符串或整数表达式，包括非常量表达式：

DECLARE int_val INT64 DEFAULT 0;

SELECT
  cart[CONCAT('it','ems')][int_val + 1].product AS item
FROM mydataset.table1;

+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

字段访问和下标运算符均返回 JSON 类型，因此您可以将使用它们进行链接，或将结果传递给采用 JSON 类型的其他函数。

这些运算符是 JSON_QUERY 函数的语法权限。例如，表达式 cart.name 等效于 JSON_QUERY(cart, "$.name")。

如果在 JSON 对象中找不到具有指定名称的成员，或者 JSON 数组中没有具有指定位置的元素，则这些运算符会返回 SQL NULL。

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;

+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | NULL        |
| NULL    | 5           |
+---------+-------------+

没有在 JSON 数据类型上定义等式和比较运算符。因此，您不能直接在 GROUP BY 或 ORDER BY 等子句中使用 JSON 值。请改为使用 JSON_VALUE 函数将字段值提取为 SQL 字符串，如下一部分所述。

以字符串形式提取值

JSON_VALUE 函数提取标量值并将其作为 SQL 字符串返回。如果 cart.name 未指向 JSON 中的标量值，则返回 SQL NULL。

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;

+-------+
| name  |
+-------+
| Alice |
+-------+

您可以在需要等式或比较的上下文（例如 WHERE 子句和 GROUP BY 子句）中使用 JSON_VALUE 函数。以下示例展示了根据 JSON 值进行过滤的 WHERE 子句：

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';

+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

或者，您也可以使用 STRING 函数来提取 JSON 字符串并将该值以 SQL STRING 形式返回。例如：

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+

除了 STRING 之外，您可能还需要提取 JSON 值并将其以另一个 SQL 数据类型返回。您可以使用以下值提取函数：

如需获取 JSON 值的类型，您可以使用 JSON_TYPE 函数。

灵活转换 JSON

您可以使用 LAX Conversion 函数灵活且准确地将 JSON 值转换为标量 SQL 值。

以下示例展示了这些函数的强大功能。LAX_IN64 会自动地准确推断和处理输入。

SELECT LAX_INT64(JSON '"10"') AS id;

+----+
| id |
+----+
| 10 |
+----+

除了 LAX_IN64 之外，您还可以使用以下函数灵活地将其他 SQL 类型转换为 JSON：

从 JSON 中提取数组

JSON 可以包含 JSON 数组，这些数组并不直接等同于 BigQuery 中的 ARRAY<JSON> 类型。您可以使用以下函数从 JSON 中提取 BigQuery ARRAY：

JSON_QUERY_ARRAY：提取数组并将其作为 JSON 的 ARRAY<JSON> 返回。
JSON_VALUE_ARRAY：提取标量值的数组，并将其作为标量值的 ARRAY<STRING> 返回。

以下示例使用 JSON_QUERY_ARRAY 提取 JSON 数组。

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;

+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

要将数组拆分到各个元素中，请使用 UNNEST 运算符，它会返回一个表，该数组中的每个元素占一行。以下示例从 items 数组的每个成员中选择 product 成员：

SELECT
  id,
  JSON_VALUE(item.product) AS product
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;

+----+---------+
| id | product |
+----+---------+
|  1 | book    |
|  1 | food    |
|  2 | pen     |
+----+---------+

下一个示例是类似的，但使用 ARRAY_AGG 函数将值聚合回 SQL 数组中。

SELECT
  id,
  ARRAY_AGG(JSON_VALUE(item.product)) AS products
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
GROUP BY id
ORDER BY id;

+----+-----------------+
| id | products        |
+----+-----------------+
|  1 | ["book","food"] |
|  2 | ["pen"]         |
+----+-----------------+

如需详细了解数组，请参阅在 GoogleSQL 中使用数组。

JSON null

JSON 类型具有不同于 SQL NULL 的特殊 null 值。JSON null 不被视为 SQL NULL 值，如以下示例所示。

SELECT JSON 'null' IS NULL;

+-------+
| f0_   |
+-------+
| false |
+-------+

提取具有 null 值的 JSON 字段时，行为取决于函数：

JSON_QUERY 函数返回 JSON null，因为它是有效的 JSON 值。
JSON_VALUE 函数返回 SQL NULL，因为 JSON null 不是标量值。

以下示例显示了不同的行为：

SELECT
  json.a AS json_query, -- Equivalent to JSON_QUERY(json, '$.a')
  JSON_VALUE(json, '$.a') AS json_value
FROM (SELECT JSON '{"a": null}' AS json);

+------------+------------+
| json_query | json_value |
+------------+------------+
| null       | NULL       |
+------------+------------+

处理 GoogleSQL 中的 JSON 数据

限制

创建具有 JSON 列的表。

SQL

bq

创建 JSON 值

创建 JSON 值

将 STRING 类型转换为 JSON 类型

将架构化数据转换为 JSON

将 SQL 类型转换为 JSON 类型

注入 JSON 数据

从 CSV 文件加载

从以换行符分隔的 JSON 文件加载

使用 Storage Write API

使用旧版流式传输 API

查询 JSON 数据

以 JSON 格式提取值

以字符串形式提取值

灵活转换 JSON

从 JSON 中提取数组

JSON null

创建具有 `JSON` 列的表。

创建 `JSON` 值

创建 `JSON` 值

将 `STRING` 类型转换为 `JSON` 类型

将 SQL 类型转换为 `JSON` 类型