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?

`$.option_id` requires `option_id` to be a key of the top-level object, not a nested object in an array. — Barmar, Jun 21 '21 at 21:09
Is there a way to search with this structure or I have to change it? — Sasha, Jun 21 '21 at 21:10

ysth · Accepted Answer · 2021-06-21T21:27:13.193

2

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}')

edited Jun 21 '21 at 21:27

answered Jun 21 '21 at 21:21

ysth

1 Answers1