Flatten/ Concatenate Postgres JSONB array



  • 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



  • turns out I was overcomplicating things and using the wrong function

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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2