Expand JSON array of object to record with PostgresQL

Question

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.

Abelisto · Accepted Answer · 2020-03-10 06:41:26Z

1

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

answered Mar 10, 2020 at 6:41

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Expand JSON array of object to record with PostgresQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related