Replace multiple columns with single JSON column

Question

I am running PostgreSQL 9.3.4. I have a table with 3 columns:

id	name	addr
1	n1	ad1
2	n2	ad2

I need to move the data to a new table with a JSON column like:

id	data
1	{"name": "n1", "addr": "ad1"}
2	{"name": "n2", "addr": "ad2"}

I tried:

SELECT t.id, row_to_json(t) AS data
FROM (SELECT id, name, addr FROM myt) t;

But that includes id in the result. So row_to_json is not the solution for me.

Is there a way to get only the columns I need (name & addr)?

• AliBZ: I am not sure if the answer is correct. I asked it 2 years ago. I also answered my question back then but didn't mark it as correct.

Answer 1

Simplest with the operator jsonb - text → jsonb to remove a single key in Postgres 9.5 or later - after converting the whole row with to_jsonb(). (Cast the result to json if you don't want jsonb.)

SELECT id, to_jsonb(t.*) - 'id' AS data
FROM myt t;

In Postgres 10 or later, you can also remove a whole array of keys with the operator jsonb - text[] → jsonb.

There is also a better option with json_build_object() in Postgres 9.4 or later:

SELECT id, json_build_object('name', name, 'addr', addr) AS data
FROM myt;

But there is an even simpler way since Postgres 9.3:

SELECT id, to_json((SELECT d FROM (SELECT name, addr) d)) AS data
FROM myt;

to_json() is mostly the same as row_to_json().
Find a couple more syntax variants in the fiddle.

db<>fiddle here
_{Old sqlfiddle (Postgres 9.6)}

Related answers:

• Select columns inside json_agg

• Return as array of JSON objects in SQL (Postgres)

• Return total number of rows and selected (aggregated) data

• Nacho B: This is a better answer, and the fiddle has the proof.
• jrz: another banger from Erwin, i'm your biggest fan

Answer 2

I found the answer from this link:

select * from (
 select id,
 (
 select row_to_json(d)
 from (
 select name, addr
 from myt d
 where d.id=s.id
 ) d
 ) as data
 from myt s
)

• Vérace: Dont' forget to mark your own answer as correct (no points though :-( ). I don't think that you can do this immediately, but it might help someone with a similar question in the future.
• Erwin Brandstetter: Aside from the missing table alias in the outer query, this is also more complex and expensive than necessary. I added another answer with a fiddle to demonstrate.
• TheRealChx101: Does this build an object array instead of returning duplicate outer query results?