How to select only specific keys from postgres json

Question

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?

Answer 1

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)