How Can I get multi-Dimensional array by value

Question

In PostgreSQL 9.5 I use jsonb column called user_tag. This column has multi dimensional array like this:

[{"id": 1, "tag": "@xang"}, {"id": 2, "tag": "@wang"}]

I want to get when id is equal to 1. I can do this by following query:

SELECT * FROM mydb AS f WHERE f.user_tag #>> '{0,id}' = '1'

But, I have to provide 0 as a key. I want to search all id columns and return matched table rows. If I already know 0, why would i search in first place.

Any idea will be appreciated.

Note: SELECT * FROM mydb AS f WHERE f.user_tag ->> 'id' = '1' is not worked.

Answer 1

I found it myself. I wil llet you guys know so may other people get use it.

SELECT * FROM mydb AS f WHERE f.user_tag @> '[{"id":1}]'

That' s it. I can now get rows contains id=1