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!

Patrick · Accepted Answer · 2016-08-19 16:54:38Z

6

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.

answered Aug 19, 2016 at 16:54

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

2 Comments

stack_d_code Over a year ago

one more thing I wanted to get done in this query is jsonb_extract_path_text for 0th elements of phonenumber and email. I have tried jsonb_extract_path_text(people->'email'->0->>'Address') and also people->'email'->0->>jsonb_extract_path_text('Address'). Nothing seems to work.. Any idea if it is at all possible?

Patrick Over a year ago

The function jsonb_extract_path() expects a jsonb parameter, followed by the path as a text[], so the format is jsonb_extract_path(data, '{people, 0, Email, 0, address}') to get "[email protected]". In my solution above where the "people" array is already unpacked you would use jsonb_extract_path(people, '{Email, 0, address}') to get the first email address of all people.

Collectives™ on Stack Overflow

postgres + jsonb + get values of key from multidimentional array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related