1

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?

Alec Mather
  • 742
  • 5
  • 20
  • Here's a [similar thread](https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array/24006432) about json array output instead of json dict. – Zegarek Nov 27 '21 at 11:38

1 Answers1

3

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)

  • Ahh yes, you're correct.. what I'm returning is rather large so this might be a problem.. Nonetheless, good to know this exists! Thank you! – Alec Mather Nov 27 '21 at 16:58