Question 205374 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Query JSON array of objects to return properties as columns

I have a Postgres addresses table with rows such as:

id data
1 {"name": "Scott", "addresses": [{"city": "London", "street": "street 1"}, {"city": "Berlin", "street": "street 2"}]}
2 {"name": "Tiger", "addresses": [{"city": "Tokyo", "street": "street 3"}, {"city": "Zurich", "street": "street 4"}]}
3 {"name": "Foooo", "addresses": [{"city": "Paris", "street": "street 5"}]}

where data is JSON

What query would produce the following result

id name city street
1 Scott London street 1
1 Scott Berlin street 2
2 Tiger Tokyo street 3
2 Tiger Zurich street 4
3 Foooo Paris street 5

It looks like json_array_elements is relevant here but I am unsure how to proceed.

Tags:

2

Answers


  1. 0 
    select id, data->>'name', t."City", t."Street"
from addresses, lateral 
   (select * from jsonb_to_recordset(data->'addresses') 
         as x("City" text, "Street" text) ) as t;

    DBFiddle demo

    EDIT: You edited your question after my reply. Here is edited version:

    select id, data->>'name', t.city, t.street
from addresses, lateral 
   (select * from jsonb_to_recordset(data->'addresses') 
         as x(city text, street text) ) as t;

    Dbfiddle demo

    Login or Signup to reply.
  2. 0

    Here is a way to do it using json_array_elements :

    select id, json_data->>'name' as name, json_data->>'city' as city, json_data->>'street' as street
from addresses, json_array_elements (data->'addresses') as json_data

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search