CODEWITHSUNDEEP
jsonpostgresql
Lil'Monster 987
Lil'Monster 987

Reputation: 107

Expand JSON array of object to record with PostgresQL

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.

Upvotes: 2

Views: 111

Answers (1)

Abelisto
Abelisto

Reputation: 15624

select
  id as contact_id,
  json_array_elements(data->'addresses') as address
from contacts;

Upvotes: 1

Related Questions