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
– Barmar, Commented Jun 21, 2021 at 21:09
Is there a way to search with this structure or I have to change it? — Sasha
– Sasha, Commented Jun 21, 2021 at 21:10

ysth · Accepted Answer · 2021-06-21 21:27:13Z

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

answered Jun 21, 2021 at 21:21

ysth

99.1k6 gold badges126 silver badges219 bronze badges

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

1 Answer 1