Mysql - JSON_CONTAINS find within array

Question

I have json column with following data:

[{"option_id": 1, "category_id": 1}, {"option_id": 2, "category_id": 2}]

I am trying to find records with option_id = 1

This is query I am trying:

select count(*) as aggregate from `complaint_forms` 
where json_contains(`outcome_options`, '1', '$."option_id"') 

Count is 0. What am I doing wrong here?

Answer 1

You want to specify the candidate as an object with the desired attribute and value, not just the value, and not specify a path:

select count(*) as aggregate from `complaint_forms` 
where json_contains(`outcome_options`, '{"option_id":1}')

fiddle