pgjq

pig build pkg pgjq;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pgjq;		# install via package name, for the active PG version

pig install pgjq -v 18;   # install for PG 18
pig install pgjq -v 17;   # install for PG 17
pig install pgjq -v 16;   # install for PG 16
pig install pgjq -v 15;   # install for PG 15
pig install pgjq -v 14;   # install for PG 14

CREATE EXTENSION pgjq;

SELECT jq('[{"bar": "baz", "balance": 7.77}]'::jsonb, '.[0].bar');
-- "baz"

SELECT '[{"bar": "baz"}]' @@ '.[0].bar'::jqprog;
-- "baz"

SELECT jq('[true,false,[5,true,[true,[false]],false]]',
          '(..|select(type=="boolean")) |= if . then 1 else 0 end');
-- [1, 0, [5, 1, [1, [0]], 0]]

SELECT jq('[1,5,3,0,7]', '(.[] | select(. >= 2)) |= empty');
-- [1, 0]

SELECT jq(
    '{"jobs": [{"id": 9, "ok": true}, {"id": 100, "ok": false}]}'::jsonb,
    '.jobs[] | select(.ok == $ok and .id == 100) | .',
    '{"ok": false}'
);

SELECT jq('[{"cust":"baz","active":true,"trans":{"balance":100}}]',
          '(.[] | select(.active == true))') - '{trans}' @> '{"cust": "baz"}';
-- t

SELECT jq(pg_read_file('/path/to/data.json'), '.[]');