Select Rows postgres SQL where Key = String for all elements of Jsonb Array-CodePudding

So I have a Postgres SQL table that has a column of the type jsonb array. Looks like this more or less:

[{"adi": "cat", "status": "ACTIVE"}, {"adi": "dog", "status": "ACTIVE"}, {"adi": "bird", "status": "INACTIVE"}]
[{"adi": "fish", "status": "ACTIVE"}, {"adi": "dog", "status": "ACTIVE"}, {"adi": "reptile", "status": "ACTIVE"}]

So I want to select rows that only have animals with status ACTIVE in column 2.

Any ideas of how to go about it?

Thank you!

CodePudding user response：

Assuming the only two values are ACTIVE and INACTIVE you can use a SQL JSON/Path expression:

select *
from the_table
where not the_column @@ '$[*].status == "INACTIVE"'

Another option is the contains operator @>

select *
from the_table
where not the_column @> '[{"status": "INACTIVE"}]'

This returns all rows that do not contain a status with INACTIVE