Using nested JSON field in WHERE

General Discussions - English
1

Ahoj all,
Can someone tell me why this query is not working?

ANY should return 1 or 0 so why i cannot use it with JSON field?

SELECT id FROM index WHERE ANY(jsondata.attribute.values) IN (57);

It returns an error

(1064): P01: syntax error, unexpected SUBKEY, expecting ':' or ')' near '.attribute.values) IN (57)'

jsondata.attribute.values is MVA field, indexed as array through official php API.

If i use standard MVA field it works OK.

2

jsondata.attribute.values is MVA field

It can be either JSON or MVA. For json arrays please use the syntax as shown here Manticore Search Manual: Functions > Arrays and conditions functions

3

Thanks, this seems to work, but i have to rewrite my code.
I cannot understand why must be JSON field in SELECT and not in WHERE
Why is there keyword ANY if i must write query like this for multiple values

SELECT id, ANY(x=57 OR x=12 FOR x IN jsondata.attribute.values) cond FROM index WHERE cond=1;

I mean, is this approach effective? Or it doesn’t matter, and what about “cond” in results a waste of memory.

4

JSON values made for simple use. Not sure how unstructured data of various type can be effective.