I am trying to form a jsonb object (not array) from the table (t1) below

id key value
1 hello world
1 dns
1 people 1000

I have tried

SELECT jsonb_agg(('{"' || key || '" : "' || value || '"}')::jsonb) as kv_pair FROM t1 GROUP BY id

This yields [{"hello":"world"}, {"dns":""}, {"people":1000}]

How do I flatten/ concatenate the array so the output is just a single object like so:

{"hello":"world" , "dns":"" ,  "people":1000}

It is important that I have a single object for an alembic migration.

Thanks in advance

1 Answer 1


turns out I was overcomplicating things and using the wrong function

SELECT id, jsonb_object_agg(key, value) as kv_pair
FROM t1 

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.