I'd like to create a dictionary - an array - from a simple query, to include column names in output.

IE, I'd like to turn this:

SELECT id, last, first FROM names;

  id | last     | first        
001  | Smith    | John


{ "id": "001","last": "Smith", "first": "John" }

Yes, bonus points for the quotes and colons! Ha!

We do have this very interesting catalog function, which will return all column names into an array.

SELECT array_agg(column_name::TEXT) 
FROM information_schema.columns 
WHERE table_name = 'people';

How to aggregate (couldn't help myself) that into a regular query?


The first one can be done using a JSON function

select to_jsonb(n)
from names n;

I have no idea what you want to achieve with the second query.

| improve this answer | |
  • second box is not a query, it's the intended result. Yes, am aware of the jsonb structure, but how to add the column name elements? There's the rub! – DrLou Dec 13 '18 at 18:48
  • My query will return exactly what you want: rextester.com/TAFU29113 And I was not referring to the second "box", but to the second query (the one on information schema) – a_horse_with_no_name Dec 13 '18 at 19:02

Tks, a_horse_with_no_name, you put me on the right track (clearly needed to do a bit more studying here!)

to_jsonb() doesn't get me all the way there; row_to_json() was the winner:

# SELECT row_to_json(t) FROM (SELECT SELECT id, last, first FROM names WHERE id = '001') t;
| improve this answer | |
  • There is no difference between the result of to_jsonb() and row_to_json() (except the data type that is) – a_horse_with_no_name Dec 14 '18 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.