current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

2 Answers 2

up vote 1 down vote

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 pg 9.3:

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

SQL Fiddle.
Cast to ::text is only for a sanitized display in the fiddle.

Related answers:

share|improve this answer
up vote 0 down vote

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
)
share|improve this answer
    
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 at 23:02
    
Yeah, I was planning to do that. Thanks. –  AliBZ Feb 2 at 23:11
    
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 at 3:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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