处理 GoogleSQL 中的 JSON 数据

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

本文档介绍如何创建包含 JSON 列的表、将 JSON 数据插入 BigQuery 表以及查询 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 类型的列。

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

   转到 BigQuery

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

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

3. 点击 运行。

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

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_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'

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

使用旧版流式传输 API

以下示例从本地文件加载 JSON 数据并使用旧版流式传输 API 将其流式传输到 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 string.
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 数据类型返回。您可以使用以下值提取函数：

• STRING
• BOOL
• INT64
• FLOAT64

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

灵活转换 JSON

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

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

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

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

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

• LAX_STRING
• LAX_BOOL
• LAX_INT64
• LAX_FLOAT64

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

注意：此行为不适用于存储在 STRING 类型中的 JSON 值。传递 STRING 值时，JSON_QUERY 函数会返回 SQL NULL 值，而不是 JSON null 值。