使用更大的運算符在 jsonb 數組中搜尋嵌套值

這是表定義（簡化）：

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}}
    ]}')
, (977656, 
   '{"COMMONS": {"DATE": {"value": "2018-03-11"}},
    "PAYABLE_INVOICE_LINES": [
        {"AMOUNT": {"value": 555.10}}
    ]}');

我想搜尋其中一個'PAYABLE_INVOICE_LINES'元素包含'value'大於 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。

對於數百萬個文件，此查詢非常昂貴 - 10 秒有 100 萬個。

我嘗試在 jsonb 對象的數組上添加 GIN 索引。但它似乎只在使用 jsonb 運算符（如@>.

你有一些想法來獲得更好的性能嗎？

這通常很難優化：jsonb這種測試沒有直接的運算符或索引支持。

EXISTS至少應該比您擁有的更快，同時還避免重複行（多個數組元素匹配）和pil結果中的附加（冗餘）列：

SELECT *
FROM   documents d
WHERE  EXISTS (
  SELECT FROM jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') pil 
  WHERE (pil->'AMOUNT'->>'value')::decimal > 1000
  );

有關的：

• JSONB內行中數組鍵值的索引範圍比較
• 加快嵌套在 jsonb 對像數組中的鍵值的範圍測試

為了使這個速度更快幾個數量級，提取每行的最大值並冗餘保存，或者IMMUTABLE在一個非常小且快速（但也是專門的）表達式索引中使用函式：

CREATE OR REPLACE FUNCTION f_doc_max_amout(jsonb)
 RETURNS numeric AS
$func$
  SELECT max((a->'AMOUNT'->>'value')::numeric)
  FROM   jsonb_array_elements($1) a
$func$ LANGUAGE sql IMMUTABLE;

CREATE INDEX documents_max_amount_idx
ON documents (f_doc_max_amout(data_block -> 'PAYABLE_INVOICE_LINES')); 

查詢（必須匹配索引表達式）：

SELECT *
FROM   documents d
WHERE  f_doc_max_amout(data_block -> 'PAYABLE_INVOICE_LINES') > 1000;

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/202739