PostgreSQL - how to generate an array containing column name and column data...?

Question

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

into:

{ "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?

score 1 · Answer 1 · answered Dec 13 '18 at 17:13

1

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.

answered Dec 13 '18 at 17:13

score 0 · Answer 2 · answered Dec 14 '18 at 14:39

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;

2 Answers2