仅索引 json 数组上的属性 - Postgres

Question

我有一个表，其中包含一个名为"data"的jsonb字段，其中包含以下内容：

{
customerId: 1,
something: "..."
list: [{ nestedId: 1, attribute: "a" }, { nestedId: 2, attribute: "b" }]
}

我需要根据其"nestedId"属性检索整行，请注意该字段位于数组内。

在检查了查询计划后，我发现我可以从索引中受益。所以我补充说：

CREATE INDEX i1 ON mytable using gin ((data->'list') jsonb_path_ops))

根据我从文档中的理解，这为"列表"中的值创建了索引项，该解决方案解决了我的问题。

为了完成，请按照可用于检索数据的查询进行操作

SELECT data FROM mytable where data->'list' @> '[{"nestedId": 1}]'

Tho，我想知道我是否可以做更优化的索引。例如，是否可以仅为"nestedId"字段创建索引？

Answer 1

您可以使用功能索引，仅对数值编制索引，而不能为键编制索引。 您可能需要创建一个帮助程序函数才能执行此操作。

create function jsonb_objarray_to_intarray(jsonb,text) returns int[] immutable language sql as 
$$ select array_agg((x->>$2)::int) from jsonb_array_elements($1) f(x) $$;
create index on mytable using gin (jsonb_objarray_to_intarray(data->'list','nestedId'));
SELECT data FROM mytable where jsonb_objarray_to_intarray(data->'list','nestedId') @> ARRAY[3];

我以这种方式编写它，以便该函数可以在其他类似情况下重用。 如果你不关心它被重用，你可以通过将取消引用和键值硬编码到函数中来使使用它的代码看起来更漂亮：

create function mytable_to_intarray(jsonb) returns int[] immutable language sql as 
$$ select array_agg((x->>'nestedId')::int) from jsonb_array_elements($1->'list') f(x) $$;
create index on mytable using gin (mytable_to_intarray(data));
SELECT data FROM mytable where mytable_to_intarray(data) @> ARRAY[3];

现在，这些索引确实需要比原始索引更长的时间，但它们的大小大约是原来的一半，并且查询速度至少一样快。 更重要的是，计划者对选择性有更好的统计数据，因此在更复杂的查询中可能会提出更好的查询计划。