49

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

1
  • 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.
    – AliBZ
    Jan 8, 2017 at 22:59

2 Answers 2

100

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:

2
  • 2
    This is a better answer, and the fiddle has the proof.
    – Nacho B
    Aug 20, 2018 at 15:15
  • 1
    another banger from Erwin, i'm your biggest fan
    – jrz
    Oct 17, 2023 at 15:21
12

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
)
3
  • 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.
    – Vérace
    Feb 2, 2015 at 23:02
  • 4
    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. Feb 3, 2015 at 3:31
  • Does this build an object array instead of returning duplicate outer query results? Dec 12, 2021 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.