JSONB 支持半结构化数据

您可以使用 JSON 和 JSONB 来提供半结构化数据。这对于包含用户定义的字段的数据最有用，例如由各个用户定义的并且因用户而异的字段名称。我们建议以半结构化的方式使用它，例如

CREATE TABLE metrics (
  time TIMESTAMPTZ,
  user_id INT,
  device_id INT,
  data JSONB
);

复制

当您使用 JSON 定义模式时，请确保将常用字段（例如 time、user_id 和 device_id）拉出 JSONB 结构并存储为列。这是因为字段访问在表列上比在 JSONB 结构内部更有效率。存储也更有效率。

您还应该使用 JSONB 数据类型，即以二进制格式存储的 JSON，而不是 JSON 数据类型。JSONB 数据类型在存储开销和查找性能方面都更有效率。

注意

对于用户定义的数据，请使用 JSONB，而不是稀疏数据。这对于大多数数据集效果最佳。对于稀疏数据，请使用可空字段，如果可能，在压缩文件系统（如 ZFS）之上运行。除非数据非常稀疏（例如，一行中超过 95% 的字段为空），否则这将比 JSONB 数据类型效果更好。

当您跨所有字段索引 JSONB 数据时，通常最好使用 GIN（通用倒排）索引。在大多数情况下，您可以使用默认的 GIN 运算符，如下所示

CREATE INDEX idxgin ON metrics USING GIN (data);

复制

有关 GIN 索引的更多信息，请参阅 PostgreSQL 文档。

此索引仅优化 WHERE 子句使用 ?、?&、?| 或 @> 运算符的查询。有关这些运算符的更多信息，请参阅 PostgreSQL 文档。

JSONB 列有时具有包含值的常用字段，这些值对于单独索引很有用。这样的索引对于字段值的排序操作、多列索引以及专用类型（例如 postGIS 地理类型）的索引非常有用。对单个字段值进行索引的另一个优点是，它们通常比整个 JSONB 字段上的 GIN 索引小。要创建这样的索引，通常最好在访问字段的表达式上使用部分索引。例如

CREATE INDEX idxcpu
  ON metrics(((data->>'cpu')::double precision))
  WHERE data ? 'cpu';

复制

在此示例中，被索引的表达式是 data JSONB 对象内部的 cpu 字段，转换为 double 类型。强制转换通过存储更小的 double 类型（而不是字符串）来减小索引的大小。WHERE 子句确保索引中仅包含包含 cpu 字段的行，因为 data ? 'cpu' 返回 true。这也有助于通过不包含没有 cpu 字段的行来减小索引的大小。请注意，为了使查询使用索引，它必须在 WHERE 子句中包含 data ? 'cpu'。

此表达式也可以与多列索引一起使用，例如，通过添加 time DESC 作为前导列。但是请注意，要启用仅索引扫描，您需要 data 作为列，而不是完整表达式 ((data->>'cpu')::double precision)。