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?

user1822 · Accepted Answer · 2018-12-13 17:13:55Z

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, 2018 at 17:13

user1822

DrLou · Accepted Answer · 2018-12-14 14:39:58Z

0

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;

answered Dec 14, 2018 at 14:39

DrLou

1212 silver badges4 bronze badges

2 Answers 2