Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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}}
     ]}');
2 rows affected

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


document_id
data_block


878979
{"COMMONS": {"DATE": {"value": "2017-03-11"}}, "PAYABLE_INVOICE_LINES": [{"AMOUNT": {"value": 52408.53}}, {"AMOUNT": {"value": 654.23}}]}


-- specialized, fast solution:
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;


document_id
data_block


878979
{"COMMONS": {"DATE": {"value": "2017-03-11"}}, "PAYABLE_INVOICE_LINES": [{"AMOUNT": {"value": 52408.53}}, {"AMOUNT": {"value": 654.23}}]}