本页介绍了在使用 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(TotalCapacity);
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