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)

1 Answer 1


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

select jsonb_object_agg(key, COALESCE(value,''))
from props;
  • How simple ! Looks like I am too much trying to use sql and only then json fonctions !
    – MoonCactus
    Feb 14, 2023 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.