Postgresql : merge rows of json key-value into one flat dict (not an array)

Question

In Postgresql, I want to build a flat JSON "dict" out of returned rows of key/values

When I call :

select json_agg( jsonb_build_object(key, COALESCE(value,'')) ) from props;

I get spurious braces around the pairs:

 [{"email": "[email protected]"}, {"phone": "0222222222"}, {"sex": "male"}]

I would like to get this flattened like this instead:

 {"email": "[email protected]", "phone": "0222222222", "sex": "male"}

How shall I write the query ? (there are no conflicts on keys)

user1822 · Accepted Answer · 2023-02-14 09:57:01Z

2

If you have unique keys, you can use jsonb_object_agg()

select jsonb_object_agg(key, COALESCE(value,''))
from props;

answered Feb 14, 2023 at 9:57

user1822

1 Answer 1