Select specific field and get result from array in jsonb field

Question

If I put this data to jsonb field:

{'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 1 } }

I was able to select name with this query:

SELECT data_field->>'name' from users where ....

However, When they are in array, this query doesn't work:

[
   {'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 1 } },
   {'name': 'john', 'email': '[email protected]', 'sub': { 'id': 2 } }
]

How can I select each name in array?

sticky bit · Accepted Answer · 2019-05-24 00:04:32Z

4

Do a lateral cross join of jsonb_array_elements() to get the array's elements. Then you can extract the name from each element.

SELECT sr.*,
       jae.e->>'name'
       FROM survey_results sr
            CROSS JOIN LATERAL jsonb_array_elements(sr.data_field) jae (e);

answered May 24, 2019 at 0:04

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

1 Answer 1