I need to extract only specific keys from postgres json, Let us consider the following json
{"aaa":1,"bbb":2,"ccc":3,"ddd":7}
From the above json i need to select keys 'bbb' and 'ccc', that is
{"bbb":2,"ccc":3}
I used the following query , but it's deleting the keys
SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' - 'ddd}'
How can I select only specified keys?
you can explicitely specify keys, like here:
t=# with c(j) as (SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' - 'ddd}')
select j,jsonb_build_object('aaa',j->'aaa','bbb',j->'bbb') from c;
j | jsonb_build_object
------------------------------------------+----------------------
{"aaa": 1, "bbb": 2, "ccc": 3, "ddd": 7} | {"aaa": 1, "bbb": 2}
(1 row)
WITH data AS (
SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' col
)
SELECT kv.*
FROM data,
LATERAL (
SELECT jsonb_object(ARRAY_AGG(keyval.key::TEXT), ARRAY_AGG(keyval.value::TEXT))
FROM jsonb_each(col) keyval
WHERE keyval.key IN ('aaa', 'bbb', 'ccc')) kv
The solution works by expanding a JSONB (or JSON) object, filtering the keys, aggregating the filtered keys & values to create the final JSONB (or JSON) object.
However, this solution does not preserve nulls, i.e. if data had a row where col had value jsonb '{"aaa":1,"bbb":2, "ddd":7}', then the above solution would return jsonb '{"aaa":1,"bbb":2}'
To preserve nulls, the following form could be used.
WITH data AS (
SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' col
), keys(k) AS (
VALUES ('aaa'), ('bbb'), ('ccc')
)
SELECT col, jsonb_object(ARRAY_AGG(k), ARRAY_AGG(col->>k))
FROM data, keys
GROUP BY 1
