postgres + jsonb + get values of key from multidimentional array

Question

I am trying to get jsonb result based on matching key. I have DB table "listings" with number and data column.

    number | data  
      1    |  {"name": "XYZ company", "city": "toronto", "province": "ON", "people" : [
{ "firstName": "tom", "lastName": "hanks", 
   "phonenumber": [{"type": "mobile", "Number": "111111"}], 
    "Email": [{"type": "business", "address": "[email protected]"},{"type": "personal", "address": "[email protected]"}] }, 

{ "firstName": "sandra", "lastName": "petes", 
   "phonenumber": [{"type": "mobile", "Number": "333"}, {"type": "home", "Number": "444"}], 
    "Email": [{"type": "business", "address": "[email protected]"}] 
}
]}

I need to pull all values for data column with keys -

• people->firstname
• people->lastName
• people->phonenumber->Number
• people->Email->address

What I achieved so far is:

SELECT   number 
        ,jonb_array_length(jsonb_extract_path(data,'people')) as people_count
        ,jsonb_extract_path(data,'people','0','firstname') as FirstName
        ,jsonb_extract_path(data,'people','0','lastname') as LastName
        ,jsonb_extract_path(data,'people','0','email','Address'") as personEmail
        ,jsonb_extract_path(data,'people','0','phonenumber','Number') as personPhone
FROM    listings
WHERE   number='1';

However, this only gives me 0th element of people, I need to find all elements. Is there any way to achieve this in single query.

Thanks for your time!

Answer 1

You need to use the jsonb_array_elements() function to get all of the elements of the array. Since that function returns a set of rows, you need to use it as a row source.

SELECT '1' AS number, 
       jsonb_array_length(data->'people') AS people_count,
       people->>'firstname' AS FirstName,
       people->>'lastname' AS LastName,
       people->'email'->0->>'Address' AS personEmail,
       people->'phonenumber'->0->>'Number' as personPhone
FROM listings, jsonb_array_elements(data->'people') p(people)
WHERE number = '1';

This will result in a row for every person where number = '1'. The email and phone number objects are arrays too and I pick here just the first value. If you want all of them you need to just get the whole JSON arrays and then wrap this in an outer query where you do jsonb_array_elements() again.