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)?

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

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
)