CODEWITHSUNDEEP
sqlpostgresqljsonb
Janman
Janman

Reputation: 67

Get a list of all objects with the same key inside a jsonb array

I have a table mytable and a JSONB column employees that contains data like this:

[      {
         "name":"Raj",
         "email":"[email protected]",
         "age":32
          
},
      {
         "name":"Mohan",
         "email":"[email protected]",
         "age":21
          
        }   
]

I would like to extract only the names and save them in a list format, so the resulting cell would look like this:

['Raj','Mohan']

I have tried

select l1.obj ->> 'name' names
from mytable t
cross join jsonb_array_elements(t.employees)   as l1(obj)

but this only returns the name of the first array element. How do I get the name of all array elements? Thanks!

PostgreSQL 11.8

Upvotes: 0

Views: 1249

Answers (1)

user330315
user330315

Reputation:

In Postgres 12, you can use jsonb_path_query_array():

select jsonb_path_query_array(employees, '$[*].name') as names
from mytable

In earlier versions you need to unnest then aggregate back:

select (select jsonb_agg(e -> 'name') 
        from jsonb_array_elements(employees) as t(e)) as names
from mytable

Upvotes: 3

Related Questions