Expand JSON array of object to record with PostgresQL

user_1841335 0
Viewed 73

I'm trying to expand an array of object in JSON with PostgreSQL.

TABLE CONTACTS (id int, data json);

1, {'firstName': 'John', 'lastName': 'Doe', 'addresses': [{'street': '1 Heaven St.', city: 'Wonderland'}, {'street': '3 Hell St.', city: 'Scarycity'}] }
2, {'firstName': 'Shiv', 'lastName': 'Durham', 'addresses': [{'street': '1 Sugar St.', city: 'Sweet City'}] }

And I want to turn it into view as 

contact_id|address                                        |
1         |{'street': '1 Heaven St.', city: 'Wonderland'} |
1         |{'street': '3 Hell St.', city: 'Scarycity'}    |
2         |{'street': '1 Sugar St.', city: 'Sweet City'}  |

I tried some native function but i failed to get wanted result.

postgresql json
1 Answers
select
  id as contact_id,
  json_array_elements(data->'addresses') as address
from contacts;
user_5931440
Related