title: postgres + jsonb + get values of key from multidimentional array
body: <p>I am trying to get jsonb result based on matching key.
  I have DB table "listings" with number and data column.</p>
  
  <pre><code>    number | data  
        1    |  {"name": "XYZ company", "city": "toronto", "province": "ON", "people" : [
  { "firstName": "tom", "lastName": "hanks", 
     "phonenumber": [{"type": "mobile", "Number": "111111"}], 
      "Email": [{"type": "business", "address": "tom@xyz.com"},{"type": "personal", "address": "tom@mailinator.com"}] }, 
  
  { "firstName": "sandra", "lastName": "petes", 
     "phonenumber": [{"type": "mobile", "Number": "333"}, {"type": "home", "Number": "444"}], 
      "Email": [{"type": "business", "address": "sandra@xyz.com"}] 
  }
  ]}
  </code></pre>
  
  <p>I need to pull all values for data column with keys -</p>
  
  <ul>
  <li>people->firstname</li>
  <li>people->lastName</li>
  <li>people->phonenumber->Number</li>
  <li>people->Email->address</li>
  </ul>
  
  <p>What I achieved so far is:</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks for your time!</p>