How to search JSON array field in MySQL?

Question

I have a column which has json type arrays like

row1: ["abc", 10, null, true, false]
row2: ["def", 10, null, true, false]

How can i use where clause to find "abc" contained rows? My json arrays has no key. They only have values.

select * from myTable where JSON_SEARCH(myColumn,"all","abc")

returns 0 rows

GMB · Accepted Answer · 2020-02-17 14:50:47Z

1

where json_contains(myColumn, '"abc"')

answered Feb 17, 2020 at 14:45

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

it returns Error Code: 3141. Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.

It requires double quotes like '"abc"'. When I try like that, it works

@hakiko: indeed, thanks for spotting that. I fixed my answer accordingly.

If you are using the multi-value indexes then you can use functions like MEMBER OF see dev.mysql.com/doc/refman/8.0/en/json-search-functions.html