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)

Answer 1

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

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

• MoonCactus: How simple ! Looks like I am too much trying to use sql and only then json fonctions !