psql equivalent of pandas .to_dict('index')-CodePudding

I want to return a psql table, but I want to return it in json format.

Let's say the table looks like this...

id	name	value
1	joe	6
2	bob	3
3	joey	2

But I want to return it as an object like this...

{
  "1": {
    "name": "joe",
    "value": 6
  },
  "2": {
    "name": "bob",
    "value": 3
  },
  "3": {
    "name": "joey",
    "value": 2
  }
}

So if I were doing this with pandas and the table existed as a dataframe, I could transform it like this...

df.set_index('id').to_dict('index')

But I want to be able to do this inside the psql code.

The closest I've gotten is by doing something like this

select
  json_build_object (
    id,
    json_build_object (
      'name', name,
      'value', value
    )
  )
from my_table

But instead of aggregating this all into one object, the result is a bunch of separate objects separated by rows at the key level... that being said, it's kinda the same idea...

Any ideas?

CodePudding user response：

You want jsonb_object_agg() to get this:

select jsonb_object_agg(id, jsonb_build_object('name', name, 'value', value))
from my_table

But this is not going to work well for any real-world sized tables. There is a limit of roughly 1GB for a single value. So this might fail with an out-of-memory error with larger tables (or values inside the columns)