How to filter JSON in SphinxQL?

Question

I have a JSON field with a simple numeric array

[46,70,34]

How can I filter query selecting only those rows where present let's say '70' in a JSON field? Something similar to JSON_CONTAINS in MySQL.

I have already tried

SELECT id, name, IN(tags, '70') AS c FROM index WHERE c = 1;

and

SELECT id, name, ALL(var='70' FOR var IN tags) as c FROM index WHERE c=1;

But it doesn't work.

Answer 1

Actully think...

SELECT id, name FROM index WHERE tags = 70;

should just work. JSON arrays, end up working 'like' a MVA.

Otherwise, you would want ANY() (not ALL()), because only one of the values in the JSON array would be 70, not all of them (46 wouldn't match for example!)

SELECT id, name, ANY(var=70 FOR var IN tags) as c FROM index WHERE c=1;

(finally as a numeric value, shouldn't be quoted!)