本页面介绍了在使用 Spanner 时如何使用 JSONB
数据类型。
JSONB
是一种 PostgreSQL 数据类型,用于保存 Spanner PostgreSQL 方言的半结构化数据。JSONB
存储采用 JavaScript 对象表示法 (JSON) 格式的数据,该格式遵循 RFC 7159 中所述的规范。
规格
Spanner JSONB
数据类型存储输入文档的标准化表示形式。这意味着:
- 系统不会保留引号和空白字符。
- 不支持注释。包含评论的事务或查询会失败。
- 对象键首先按键长度排序,然后按等效对象键长度的字典顺序排序。如果存在重复的对象键,则仅保留最后一个键。
- 基元类型(
string
、boolean
、number
和null
)会保留其类型和值。- 系统会精确保留
string
类型值。 - 尾随零会被保留。
number
类型值的输出格式不使用科学计数法。
- 系统会精确保留
JSONB
null
值被视为 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 NULL
保留 JSONB 数组元素顺序。
限制
Spanner JSONB
存在以下限制:
to_jsonb
函数的参数只能来自 Spanner 支持的 PostgreSQL 数据类型。- 数字类型值的小数点前可有 4932 位数,小数点后可有 16383 位数。
- 标准化存储格式允许的大小上限为 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 客户端库向 Venues
表添加名为 VenueDetails
的 JSONB
列。
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 进行身份验证,请设置应用默认凭据。如需了解详情,请参阅为本地开发环境设置身份验证。
查询 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 索引。如需了解详情,请参阅编入索引。
- 将
JSONB
列转换为任何其他数据类型或从其更改为其他数据类型 - 在使用 PostgreSQL 有线协议的工具中使用包含非类型化 JSONB 参数的参数化查询
查询引擎中的强制转换。与标准 PostgreSQL 不同,不支持从
JSONB
强制转换为文本。只有有效的JSON
字符串才能强制转换为JSONB
类型,以与函数签名匹配。示例:SELECT concat('abc'::text, '{"key1":1}'::jsonb); -- Returns error SELECT concat('abc'::text, CAST('{"key1":1}'::jsonb AS TEXT)); -- This works
编制索引
JSONB
列不支持编入索引。但是,您可以在生成的列上创建索引,以从 JSONB
列中提取标量值。
CREATE TABLE Venues (
VenueId BIGINT PRIMARY KEY,
VenueName VARCHAR(1024),
VenueAddress VARCHAR(1024),
VenueFeatures JSONB,
TotalCapacity BIGINT GENERATED ALWAYS AS ((VenueFeatures->>'capacity')::BIGINT) STORED,
DateOpened TIMESTAMPTZ
);
CREATE INDEX VenuesByCapacity ON Venues(TotalCapacity);