Sample Header Ad - 728x90

How to create a JSON object from table rows containing key-value pairs?

1 vote
1 answer
1146 views
I am trying to form a jsonb object (not array) from the table (t1) below | id | key |value | |--------------|------------|------| | 1 | hello|world | | 1 | dns | 192.2.8.0| | 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":"192.2.8.0"}, {"people":1000}] How do I flatten/ concatenate the array so the output is just a single object like so: {"hello":"world" , "dns":"192.2.8.0" , "people":1000} It is important that I have a single object for an Alembic migration. Thanks in advance
Asked by thesunnyscientist (121 rep)
Mar 15, 2022, 11:21 PM
Last activity: Nov 1, 2024, 07:11 PM