CODEWITHSUNDEEP
sqlpostgresql
Unknown
Unknown

Reputation: 423

Getting values from array of objects jsonb postgresql

I am storing some ids and names in a jsonb array of object like this

[{"id":"1","name":"abc"},{"id":"2","name":"cde"}]

My table looks like this

id      userinfo
1       [{"id":"1","name":"abc"},{"id":"2","name":"cde"}]
2       [{"id":"3","name":"fgh"},{"id":"4","name":"ijk"}]

I am trying to select all the records with id 1 but I just want to get ids in userinfo object I don't want names

I tried this

select distinct userinfo->'name' from table where id = 1

but this is giving me null value

This will work with this query

select distinct userinfo->0->'name' from table where id = 1

but I don't know the index so how can I use this query to get my desired result

Thanks

Upvotes: 1

Views: 1793

Answers (1)

user330315
user330315

Reputation:

You need to normalize the data by unnesting the array, then you can access each element.

select ui.info ->> 'id' as id,
       ui.info ->> 'name' as name
from the_table t
  cross join lateral jsonb_array_elements(t.userinfo) as ui(info)
 where t.id = 1;

Online example: http://rextester.com/FCNM11312

Upvotes: 1

Related Questions