43

I am running postgresql 9.3.4. I have a table with 3 fields:

id   name  addr
---  ----  ----
1    n1    ad1
2    n2    ad2
...

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

id   data
---  ----
1    {'name': 'n1', 'addr': 'ad1'}
2    {'name': 'n2', 'addr': 'ad2'}
...

row_to_json is not the solution for me as SELECT t.id, row_to_json(t) as data FROM (select id, name, addr from myt) t adds id to the result as well. Is there a way to choose the fields I need (name & addr) in my data field?

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

94

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:

1
  • 2
    This is a better answer, and the fiddle has the proof.
    – Nacho B
    Aug 20, 2018 at 15:15
10

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 that you have read and understand our privacy policy and code of conduct.

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