Getting values from array of objects jsonb postgresql

Question

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

user330315 · Accepted Answer · 2018-07-02 14:36:34Z

1

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;

Collectives™ on Stack Overflow

Getting values from array of objects jsonb postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related