Query ArangoDB for Arrays

Question

I am having a problem querying ArangoDB in java for a value of Arrays. I have tried with both String[] and ArrayList, both with no success.

My query:

FOR document IN documents FILTER @categoriesArray IN document.categories[*].title RETURN document

BindParams:

Map<String, Object> bindVars = new MapBuilder().put("categoriesArray", categoriesArray).get();

categoriesArray contains a bunch of Strings. I'm not sure why it isn't returning any results, because if I query using:

FOR document IN documents FILTER "Politics" IN document.categories[*].title RETURN document

I get the results I am looking for. Just not when using an Array or ArrayList.

I also tried querying for:

FOR document IN documents FILTER ["Politics","Law] IN document.categories[*].title RETURN document

in order to emulate an ArrayList, but this doesn't return any results. I would query using a bunch of individual Strings, but there are too many and I get an error from the Java driver when querying with a String that long. Thus, I must query using an Array or ArrayList.

An example of the categoriesArray:

["Politics", "Law", "Nature"]

A sample image of the database:

Bandler

Answer 1

The reason is that the IN operator works by searching for the value on its left-hand side in each member of the array on the right side.

With the following the query, this will work if "Politics" is a member of document.categories[*].title:

FOR document IN documents FILTER "Politics" IN document.categories[*].title RETURN document

However the following will not work query even if "Politics" is a member of document.categories[*].title:

FOR document IN documents FILTER [ "Politics", "Law" ] IN document.categories[*].title RETURN document

This is because it will be searched for the exact value [ "Politics", "Law" ] in each member on the right side, and this will not be present. What you are probably looking for is a comparison that looks for "Politics" and "Law" separately, e.g.:

FOR document IN documents 
LET contained = (
  FOR title IN [ "Politics", "Law" ]   /* or @categoriesArray */
    FILTER title IN document.categories[*].title 
    RETURN title
)
FILTER LENGTH(contained) > 0
RETURN document

Unroot

How about LET categories = ["Politics", "Law"], LET cat_length = LENGTH(categories) & FILTER cat_length == LENGTH(INTERSECTION(categories, document.categories[*].title))? Code looks cleaner, but dunno about performance and memory-consumption. Or is it possible to add support for Sets in AQL? Like FILTER SET(["Politics", "Law"]) IN SET(document.categories[*].title). Or in JS, one can do: let arr = ["Law", "Science", "Politics"]; ["Politics","Law"].every(elem => arr.indexOf(elem) != -1). How about EVERY() and SOME() in AQL? SOME(["Law","Politics"], doc.categories[*].title)) – Witch

I agree, EVERY and SOME would be useful in AQL. I am not sure if providing them as functions would provide the most intuitive syntax, but would probably be easiest. – Unroot

Answer 2

Arango also (now) has Array Comparison Operators which allow searching ALL IN, ANY IN, or NONE IN

[ 1, 2, 3 ]  ALL IN  [ 2, 3, 4 ]  // false
[ 1, 2, 3 ]  ALL IN  [ 1, 2, 3 ]  // true
[ 1, 2, 3 ]  NONE IN  [ 3 ]       // false
[ 1, 2, 3 ]  NONE IN  [ 23, 42 ]  // true
[ 1, 2, 3 ]  ANY IN  [ 4, 5, 6 ]  // false
[ 1, 2, 3 ]  ANY IN  [ 1, 42 ]    // true
[ 1, 2, 3 ]  ANY ==  2            // true
[ 1, 2, 3 ]  ANY ==  4            // false
[ 1, 2, 3 ]  ANY >  0             // true
[ 1, 2, 3 ]  ANY <=  1            // true
[ 1, 2, 3 ]  NONE <  99           // false
[ 1, 2, 3 ]  NONE >  10           // true
[ 1, 2, 3 ]  ALL >  2             // false
[ 1, 2, 3 ]  ALL >  0             // true
[ 1, 2, 3 ]  ALL >=  3            // false
["foo", "bar"]  ALL !=  "moo"     // true
["foo", "bar"]  NONE ==  "bar"    // false
["foo", "bar"]  ANY ==  "foo"     // true

So you could now filter by:

FOR document IN documents 
    FILTER ["Politics", "Law] ANY IN (document.categories[*].title)[**]
    RETURN document

Censurable