Select a row as JSON in Postgres

Question

Is there a straightforward way to put entire row from one Postgres table into a JSON column in another table?

Here's an example to illustrate what I'm looking to do. Let's say I've got a table people, with name, age, and data columns:

column type
-----------
name   text
age    int
data   json

I'd like to merge in my table of 2012_customers, which has a lot more columns. How would I stuff all those extra columns into the JSON column of people, preserving the column names as the data keys? Here's some pseudo-SQL for that:

insert into people
select 
name,
age,
all_fields_as_json() as json
from customers_2012

That or to_json probably does what I want, but I haven't been able to figure out the correct syntax for the example above. — Matt Hampel
– Matt Hampel, Commented Aug 3, 2016 at 22:24
try select name, age, row_to_json(customers_2012) from customers_2012 as your select part. — alzee
– alzee, Commented Aug 3, 2016 at 22:25
Got that just as you posted! Thanks! If you want to post the answer I'll remove mine and accept yours. — Matt Hampel
– Matt Hampel, Commented Aug 3, 2016 at 22:28

Matt Hampel · Accepted Answer · 2016-08-03 22:28:02Z

22

In this situation, the correct syntax is to use row_to_json(table_name):

insert into people
select 
name,
age,
row_to_json(customers_2012) as json
from customers_2012

answered Aug 3, 2016 at 22:28

Matt Hampel

5,25813 gold badges55 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Howard Lince III Over a year ago

Small addition: When qualifying the schema on the FROM statement, the schema does not get qualified in the row_to_json() call This means it looks like this sql select row_to_json(mytable) as json from other_schema.mytable;

ngalstyan Over a year ago

You can get it working with a table on non-default schema by aliasing the table. sql select row_to_json(t) as json from other_schema.mytable t

Collectives™ on Stack Overflow

Select a row as JSON in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related