23

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?

  • 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 '17 at 22:59
50

There is a better option with json_build_object() in Postgres 9.4+:

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

But there is also a simpler and faster way with row_to_json() in Postgres 9.3:

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

db<>fiddle here
Old SQL Fiddle on Postgres 9.6.

Related answers:

  • This is a better answer, and the fiddle has the proof. – MIguelele Aug 20 '18 at 15:15
4

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
)
  • 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 '15 at 23:02
  • 2
    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. – Erwin Brandstetter Feb 3 '15 at 3:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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