Postgres中JSONB嵌套数组中字符串值的索引

Question

我有一个带有JSONB(PostgreSQL 12(的复杂对象，它在嵌套数组中有嵌套数组等等。我搜索所有包含特定条件的发票。

create table invoice:
invoice_number primary key text not null,
parts: jsonb,
...

对象：

"parts": [
{ 
"groups": [
{
"categories": [
{
"items": [
{
...
"articleName": "article1",
"articleSize": "M",
},
{
...
"articleName": "article2"
"articleSize": "XXL",
}
]
}
] 
}
] 
},
{
"groups": [
...
] 
},
]

我建立了一个本地查询来搜索带有特定文章的项目名称：

select * from invoice i, 
jsonb_array_elements(i.invoice_parts) parts, 
jsonb_array_elements(parts -> 'groups') groups, 
jsonb_array_elements(groups -> 'categories') categories, 
jsonb_array_elements(categories -> 'items') items 
where items ->> 'articleName' like '%name%' and items ->> 'articleSize' = 'XXL';

我想我可以通过索引提高搜索速度。我读过关于三角图索引的文章。对于我的案例来说，这是最好的索引类型吗？如果是->如何为如此复杂的对象构建它。

谢谢你的建议。

Answer 1

可能加快速度的唯一选项是在parts列上创建一个GIN索引，并使用JSON路径运算符：

select *
from invoice
where parts @? '$.parts[*].groups[*].categories[*].items[*] ? (@.articleName like_regex "name" && @.articleSize == "XXL")'

但我怀疑这是否足够快，即使这使用了指数。