Checking if jsonb object has a specific value in array object

Question

I'm trying to find all rows with a certain value in an array. Currently use

select * from test 
where data -> 'systems' -> 0 ->> 'name' = 'Stardust' 
or info -> 'systems' -> 1 ->> 'name' = 'Stardust';

The systems array can theoretically be infinitely long. Is there a way to say that when any element in the array contains this value the row should be returned? The JSON data structure looks like this:

{
  "systems": [
    {"host": "example.com", "name": "Stardust", "impact": 1}, 
    {"host": "example2.com", "name": "Milkyway", "impact": 0}
  ]
}

Rakhi Agrawal · Accepted Answer · 2021-04-22 11:38:18Z

6

You can do something like this :

If the JSON contains another JSON Object

select * from test where data->'systems' @> '[{"name":"Stardust"}]';

If the JSON array contains strings

select * from test where data->'systems' ? 'Stardust';

Please refer the jsonb Operators section here for more different functions you can use.

edited Apr 22, 2021 at 11:38

answered Apr 22, 2021 at 10:01

Rakhi Agrawal

9279 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Daniel Stefan Klose Over a year ago

It does not seem to work for me. Look at the edit I posted about the structure of the json. Is there anything wrong with that?

Daniel Stefan Klose Over a year ago

Okay got it. It should be '[{"name":"Stardust"}]' not '{"name":"Stardust"}'.

Rakhi Agrawal Over a year ago

yeah correct. Let me edit and update the same. I missed the brackets I think. Happy you worked it out :)

Rakhi Agrawal Over a year ago

If you got the answer to your query and if you think my answer was a bit helpful then could you please mark it as answer?

Bartekus Over a year ago

This works great, thank you for putting me on the right track!

Collectives™ on Stack Overflow

Checking if jsonb object has a specific value in array object

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related