postgresql - 在 postgresql 中对 jsonb 键建立索引

Question

我正在使用 PostgreSQL。有什么方法可以创建索引只是字典键，而不是值。

例如想象一个像这样的 jsonb 列:

select data from tablename where id = 0;

answer: {1:'v1', 2:'v2'}

我想在 [1, 2] 键集(或键列表)上建立索引。要加快查询速度，例如:

select count(*) from tablename where data ? '2';

正如您在 docs 中看到的那样，有一种方法可以完全索引列(键 + 值):

CREATE INDEX idxgin ON api USING GIN (jdoc);

考虑到我在值中存储大量数据，这对我不利。

我以前试过这个:

CREATE INDEX test ON tablename (jsonb_object_keys(data));

错误是:

ERROR:  set-returning functions are not allowed in index expressions

此外，我不想将键作为值存储在字典中。

你能帮帮我吗？

Answer 1

您的示例没有多大意义，因为您的 WHERE 子句未指定 JSON 操作，并且您的示例输出不是有效的 JSON 语法。

您可以将集合返回函数(和聚合)隐藏到一个 IMMUTABLE 函数中:

create function object_keys(jsonb) returns text[] language SQL immutable as $$ 
    select array_agg(jsonb_object_keys) from jsonb_object_keys($1)
$$;

create index on tablename using gin ( object_keys(data));

如果你这样做了，你可以这样查询它:

select * from tablename where object_keys(data) @> ARRAY['2'];

您可以改为让函数返回一个包含数组的 JSONB，而不是返回一个 PostgreSQL 文本数组，如果您更愿意那样查询的话:

select * from tablename where object_keys_jsonb(data) @> '"2"';

您不能使用 ? 公式，因为在 JSONB 中，它专门用于对象而不是数组。如果你真的想使用 ?，你可以改为编写一个函数，将对象保持为对象，但将所有值转换为 JSON null 或空字符串，这样它们占用的空间就更少。