Get rows whose JSON array contains an object with a value

I have a query that returns one JSON column named record, which is generated by a subquery.

SELECT record FROM (my json query) records;

Each record contains an array of tag objects:

{
  "tags": [
    {
      "id": 3,
      "name": 'foo'
    },
    {
      "id": 10,
      "name": 'bar'
    }
  ],
  // other props
}

I want to modify my query to select all records where tags contains a tag with a specific ID but I’m not sure how to go about that without adding another subquery. Are there any functions or operations I’m missing that can make this possible using just the where clause?

In pseudo-code, is there anything like this I can do?

SELECT record FROM (my json query) records
WHERE 3 = ANY(get_ids_from_objects(record->'tags', 'id'));

For code reasons, I’d also prefer not to have to modify the subquery that creates the JSON structure, which is why I’ve excluded it.

Answer

You can use the contains operator @>

select *
from (your query)
where record @> '{"tags": [{"id": 3}]}'

This assumes that the column record is of type jsonb (which it should be). If it’s not, you need to cast it: record::jsonb

Advertisement

Answer