CODEWITHSUNDEEP
sqljsonpostgresql-9.5
Alexei
Alexei

Reputation: 15664

Find text in any array's items

Column states has type jsonb

Example:

[{
        "dt": "2020-12-18T16:19:45.378+00:00",
        "id": "confirmed"
    },
    {
        "dt": "2020-12-16T12:11:45.210+00:00",
        "id": "delivery"
    },
    {
        "dt": "2020-12-16T11:25:08.444+00:00",
        "id": "new"

    }
]

If I want to find is first item's in array id = confirmed

I use this

select id, states from my_table where states->0->>'id' = 'confirmed'

Nice. It's work fine.

But... I need is check is exist id = confirmed in ANY array's items. Is it possible?

Upvotes: 0

Views: 17

Answers (1)

user330315
user330315

Reputation:

You can use the contains operator @>

select id, states 
from my_table 
where states @> '[{"id": "confirmed"}]'

Upvotes: 1

Related Questions