@User @User do you know *JSONPATHARRAY*(jsonField, 'jsonPath') can be used in a WHERE clause to find out if the array contains a certain value?

I think you need to use

JsonExtractScalar

with an array type to extract a MV field

is there an example or syntax manual for this?

E.g.

where jsonExtractScalar(json, '$.a', 'STRING_ARRAY') = 'abc'

I suppose the above feature can not utilize Json index, right?

Yes

I am still a bit confused about which one to use in the where clause.

jsonExtractScalar

or

JSON_MATCH

If you have json index generated for the column,

JSON_MATCH

should be much faster

got it. thanks.