clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555260 fiddles created (37458 in the last week).

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
 hidden batch(es)


-- 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}}]}
 hidden batch(es)


-- 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'));
 hidden batch(es)


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}}]}
 hidden batch(es)