Postgres jsonb 使用更大的运算符在数组中搜索（使用 jsonb_array_elements）

我尝试寻找解决方案，但没有找到适合我的情况的任何内容......

这是数据库声明（简化）：

CREATE TABLE documents (
    document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    data_block jsonb NULL
);

这是插入的一个例子。

INSERT INTO documents (document_id, data_block)
VALUES(878979, 
    {"COMMONS": {"DATE": {"value": "2017-03-11"}},
     "PAYABLE_INVOICE_LINES": [
         {"AMOUNT": {"value": 52408.53}}, 
         {"AMOUNT": {"value": 654.23}}
     ]});
INSERT INTO documents (document_id, data_block)
VALUES(977656, 
    {"COMMONS": {"DATE": {"value": "2018-03-11"}},
     "PAYABLE_INVOICE_LINES": [
         {"AMOUNT": {"value": 555.10}}
     ]});

我想搜索其中 PAYABLE_INVOICE_LINES 之一的行值大于 1000.00 的所有文档

我的查询是

select *
from documents d
cross join lateral jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') as pil 
where (pil->'AMOUNT'->>'value')::decimal >= 1000

但是，由于我想限制为 50 个文档，因此我必须对 document_id 进行分组并将结果限制为 50 个。

对于数百万个文档，此查询非常昂贵……100 万个文档需要 10 秒。

您有什么想法可以取得更好的表现吗？

Thanks

代替cross join lateral use where exists:

select *
from documents d
where exists (
  select 1
  from jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') as pil
  where (pil->'AMOUNT'->>'value')::decimal >= 1000)
limit 50;

Update

还有另一种方法，更复杂但也更有效。

创建函数返回最大值JSONB数据，像这样：

create function fn_get_max_PAYABLE_INVOICE_LINES_value(JSONB) returns decimal language sql as $$
  select max((pil->'AMOUNT'->>'value')::decimal)
  from jsonb_array_elements($1 -> 'PAYABLE_INVOICE_LINES') as pil $$

在此函数上创建索引：

create index idx_max_PAYABLE_INVOICE_LINES_value
  on documents(fn_get_max_PAYABLE_INVOICE_LINES_value(data_block));

在查询中使用函数：

select *
from documents d
where fn_get_max_PAYABLE_INVOICE_LINES_value(data_block) > 1000
limit 50;

在这种情况下，将使用索引，并且在大量数据上查询会更快。

PS：通常limit配对有意义order by.