本页面介绍了如何在使用 Spanner 时处理 JSONB 数据类型。
JSONB 是一种 PostgreSQL 数据类型，用于在 Spanner PostgreSQL 方言中存储半结构化数据。JSONB 可存储采用 JavaScript 对象表示法 (JSON) 格式（遵循 RFC 7159 中所述的规范）的数据。
规格
Spanner JSONB 数据类型存储输入文档的标准化表示形式。这意味着：
- 引号和空白字符不会保留。
- 不支持注释。含有注释的事务或查询会失败。
- 对象键首先按键长度排序，然后按等效对象键长度以字典顺序排序。如果对象键重复，则仅保留最后一个。
- 原始类型（
string、boolean、number和null）会保留其类型和值。string类型值会完全保留。- 尾随零会保留。
number类型值的输出格式不使用科学计数法。
JSONBnull值会被视为 SQL 非NULL。例如：SELECT null::jsonb IS NULL; -- Returns true SELECT 'null'::jsonb IS NULL; -- Returns false SELECT '{"a":null}'::jsonb -> 'a' IS NULL; -- Returns false SELECT '{"a":null}'::jsonb -> 'b' IS NULL; -- Returns true SELECT '{"a":null}'::jsonb -> 'a'; -- Returns a JSONB 'null' SELECT '{"a":null}'::jsonb -> 'b'; -- Returns a SQL NULLJSONB 数组元素顺序会保留。
限制
使用 Spanner JSONB 时，存在以下限制：
to_jsonb函数的参数只能来自 Spanner 支持的 PostgreSQL 数据类型。- 数值类型值的小数点前最多可有 4,932 位数，小数点后最多可有 16,383 位数。
- 标准化存储格式允许的最大大小为 10 MB。
JSONB文档必须采用 UTF-8 编码。涉及采用其他格式编码的JSONB文档的事务或查询将返回错误。
创建包含 JSONB 列的表
您可以在创建表时向表中添加 JSONB 列。
CREATE TABLE Venues (
VenueId BIGINT PRIMARY KEY,
VenueName VARCHAR(1024),
VenueAddress VARCHAR(1024),
VenueFeatures JSONB,
DateOpened TIMESTAMPTZ
);
示例 VenueFeatures JSONB 对象如下：
{
"rating": 4.5,
"capacity":"1500",
"construction":"brick",
"tags": [
"multi-cuisine",
"open-seating",
"stage",
"public address system"
]
}
在现有表中添加和移除 JSONB 列
您可以使用 ALTER 语句添加 JSONB 列并将其删除，如下所示：
ALTER TABLE Venues ADD COLUMN VenueDetails JSONB;
ALTER TABLE Venues DROP COLUMN VenueDetails;
以下示例展示了如何使用 Spanner 客户端库将名为 VenueDetails 的 JSONB 列添加到 Venues 表。
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
修改 JSONB 数据
您可以像修改任何其他列一样修改 JSONB 列。
示例如下：
UPDATE Venues SET VenueFeatures = '{"rating": 4.5, "tags":["multi-cuisine", "open-seating"] }'
WHERE VenueId = 1;
以下示例展示了如何使用 Spanner 客户端库更新 JSONB 数据。
C++
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
C#
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Go
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Java
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Node.js
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
PHP
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Python
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Ruby
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
将 JSON 数据编入索引
您可以将二级索引和搜索索引与 JSONB 数据搭配使用，从而加快查询 JSONB 数据的速度。Spanner 不支持在二级索引中使用 JSONB 类型列作为键。
使用二级索引
在根据 JSONB 文档中的标量值进行过滤时，二级索引非常有用。如需将二级索引与 JSONB 搭配使用，请创建一个生成的列，以提取相关的标量数据并将其转换为相应的 SQL 数据类型。然后，您可以针对此生成的列创建二级索引。索引可在符合条件的查询对生成的列运行时加快其速度。
在以下示例中，您将创建一个 VenuesByCapacity 索引，数据库会使用该索引查找容量大于 1000 的场馆。Spanner 会使用索引查找相关行，而不是检查每一行，从而提高查询性能（尤其是对于大型表）。
ALTER TABLE Venues (
ADD COLUMN VenueCapacity BIGINT GENERATED ALWAYS AS ((VenueFeatures->>'capacity')::BIGINT) VIRTUAL,
DateOpened TIMESTAMPTZ
);
CREATE INDEX VenuesByCapacity ON Venues(VenueCapacity);
SELECT VenueName
FROM Venues
WHERE VenueCapacity > 1000;
使用搜索索引
当您针对动态或多变的 JSONB 文档进行查询时，搜索索引会非常有用。与二级索引不同，您可以针对存储在 JSONB 列中的任何 JSONB 文档创建搜索索引。搜索索引会自动适应 JSON 文档间的变化、不同行之间的变化以及随时间的变化。
在以下示例中，您将创建一个 VenuesByVenueDetails 搜索索引，数据库会使用该索引查找具有特定详细信息（例如大小和营业时间）的场馆。Spanner 会使用索引查找相关行，而不是检查每一行，从而提高查询性能（尤其是对于大型表）。
ALTER TABLE Venues
ADD COLUMN VenueDetails_Tokens spanner.tokenlist
GENERATED ALWAYS AS (spanner.tokenize_jsonb(VenueDetails)) VIRTUAL HIDDEN;
CREATE SEARCH INDEX VenuesByVenueDetails
ON Venues (VenueDetails_Tokens);
SELECT VenueName
FROM Venues
WHERE VenueDetails @> '{"labels": ["large"], "open": {"Friday": true}}'::jsonb;
如需了解详情，请参阅 JSON 搜索索引。
查询 JSONB 数据
您可以根据底层字段的值查询 JSONB 列。以下示例从 Venues 中提取 VenueId 和 VenueName，其中 VenueFeatures 的 rating 值大于 3.5。
SELECT VenueId, VenueName
FROM Venues
WHERE (VenueFeatures->>'rating')::FLOAT8 > 3.5;
以下示例展示了如何使用 Spanner 客户端库查询 JSONB 数据。
C++
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
C#
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Go
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Java
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Node.js
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
PHP
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Python
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
Ruby
如需了解如何安装和使用 Spanner 客户端库，请参阅 Spanner 客户端库。
如需向 Spanner 进行身份验证，请设置应用默认凭证。 如需了解详情，请参阅为本地开发环境设置身份验证。
不支持的 PostgreSQL JSONB 功能
Spanner JSONB 不支持以下开源 PostgreSQL JSONB 功能：
- 排序、比较和汇总
- PrimaryKey 和 ForeignKey
- 索引编制，包括 GIN 索引。您可以改用 Spanner 搜索索引，它可以像 GIN 索引一样加速相同的 JSONB 操作。如需了解详情，请参阅将 JSON 数据编入索引。
- 将
JSONB列更改为任何其他数据类型或从任何其他数据类型更改为此类型 - 在使用 PostgreSQL 传输协议的工具中，使用包含无类型的 JSONB 参数的参数化查询
在查询引擎中使用强制转换。与开源 PostgreSQL 不同，不支持将
JSONB隐式强制转换为文本。您必须从JSONB类型进行显式类型转换来匹配函数签名。例如：SELECT concat('abc'::text, '{"key1":1}'::jsonb); -- Returns error SELECT concat('abc'::text, CAST('{"key1":1}'::jsonb AS TEXT)); -- This works SELECT 3 + CAST('5'::jsonb AS INTEGER); -- This works