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
Last activity: Nov 1, 2024, 07:11 PM