在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';

正如您在文档中所看到的，有一种方法可以对整个列（键和值）进行索引：

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

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

你能帮帮我吗？

- Amir Mansoubi

---

索引是为了支持查询而创建的。您必须向我们展示您想要使用该索引支持哪种查询（where条件）。例如，您拥有的索引将支持“where data ? '1'”这样的查询。 - user330315

使用 where data ? '?'。 - user330315

是的。但这会索引所有数据（键+值），这对我来说不太好。 我只想索引键。 - Amir Mansoubi

数据中的值太大了，我不想增加更多不必要的负载。 通常只需要知道键包含我搜索的ID就足够了。 - Amir Mansoubi

你可以对数据模型进行规范化，然后为关键列建立索引。 - user330315

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或空字符串，以便它们占用更少的空间。

- jjanes

---

谢谢回答。我在问题中添加了另一个查询。 这个索引是否支持像这样的查询： “select count(*) from tablename where data ? '2';” - Amir Mansoubi