如何在PostgreSQL中对深度嵌套的jsonb属性创建索引

发布于01月11日

我有一个名为orders的表，其中包含一个名为data的列，其数据类型为jsonb，如下所示

"data": [
  "items": [
   {"name": "Peter"},
   {"name": "John"}
  ]
]

这是用于检索唯一名称列表的查询:

    SELECT distinct NameList->'name' AS uniqueNames
FROM orders
         CROSS JOIN jsonb_array_elements(data) itemsData
         CROSS JOIN jsonb_array_elements(itemsData->'items') NameList
WHERE NameList != '[]'

在这里，data有很多其他信息，但我对items感兴趣.因此，如何在NameList != '[]'上创建索引？

推荐答案

如果您正在寻找性能，在PostgreSQL12+jsonpath上应该比这更快，即使没有索引支持.Demo at db<>fiddle:

select distinct jsonb_path_query(data,'$.data[*].items[*].name') 
from orders 
where data @? '$.data[*].items[*].name';

@? "path-exists" operator的功能与您try 执行的操作相同:它会判断该路径下是否有name.关键区别在于它在单个jsonpath表达式中做到了这一点，而且由于有了[*]数组访问器，它不需要取消任何层的嵌套或者根本不使用集返回函数.

您可以通过让索引缩小不需要判断的行的范围，重新使用上面where中的条件来提高其性能:

create index on orders((data @? '$.data[*].items[*].name'));

你的问题有几个问题

您提供的jsonb值格式不正确.data是一个键，所以它需要在一个对象中.我猜它的值是一个数组，正如它包含的方括号所示，事实上你正在调用jsonb_array_elements().items也是一个键，所以它需要在一个对象中，作为data数组的一个元素.所以你少了一对外部的花括号，还有一个在items周围:
```
{   "data": [
        {   "items": [
                {   "name": "Peter"},
                {   "name": "John" }
            ]
        }
    ]
}
```
该查询在语法上或逻辑上无效:
1. 您为jsonb_array_elements()的结果集指定了别名，但没有命名它们的字段/列，因此引用的是它们的整个记录，而不是其中的内容:
```
CROSS JOIN jsonb_array_elements(data) itemsData
CROSS JOIN jsonb_array_elements(itemsData->'items') NameList
```
  定义结果集and的名称及其字段名，以便能够这样使用它:
```
CROSS JOIN jsonb_array_elements(data) AS elements1(itemsData)
CROSS JOIN jsonb_array_elements(itemsData->'items') AS elements2(NameList)
```
2. 如果您判断一个空的jsonb数组，则NameList != '[]'可以工作，但您只是使用了一个函数将其打开并分解为单独的元素；在上面的修复之前，NameList是保存单个数组元素的记录.修复之后，它是数组元素.我想(itemsData->'items')是你要判断的数组
```
CROSS JOIN jsonb_array_elements(data) AS elements1(itemsData)
CROSS JOIN jsonb_array_elements(itemsData->'items') AS elements2(NameList)
WHERE (itemsData->'items') != '[]'
```
  但该条件并没有真正的帮助:如果该数组为空，则该函数无论如何都不会产生任何结果，因此不需要将其过滤掉.它也不能用来估计表的哪些行可以跳过，因为它必须深入到每一行才能确定这一点，而且因为这是通过集返回函数执行的，所以您不能构建与此条件相对应的表达式索引(见下文).
jsonb_array_elements()是一个集返回函数，所以不能在an expression index中使用:
```
ERROR:  set-returning functions are not allowed in index expressions
```
尽管如此，由于你的数组判断可以被修复，一个类似的修复可以被用来给你一个有点可用的索引:
```
create index on orders((data->'data')) 
where (jsonb_array_length(data->'data')>0);
```
如果你的data有时是空的，而且你经常判断它的内容，那么这可能会有帮助，但你似乎担心的是里面有items个列表，这对你的帮助不大.