ArangoDB AQL deep array scan

Question

I have a collection of customers with their visited places, organised as follows:

{
  "customer_id": 151,
  "first_name": "Nakia",
  "last_name": "Boyle",
  "visited_places": [
    {
      "country": "Liberia",
      "cities": [
        "Mullerside",
        "East Graham"
      ]
    },
    {
      "country": "Rwanda",
      "cities": [
        "West Kristofer",
        "Effertzbury",
        "Stokeston",
        "South Darionfort",
        "Lewisport"
      ]
    }
  ]
}

I am trying to find all customers that have visited a specific city in a specific country. I've got it working like this:

FOR target IN usertable 
FILTER [] != target.visited_places[* FILTER CURRENT.country == @country AND CONTAINS(CURRENT.cities, @city)]
LIMIT @limit 
RETURN target

The query seems cumbersome and I am not sure if it is performant.

Is there any better way to do this in terms of readability and performance?

score 0 · Accepted Answer · answered Oct 06 '21 at 12:08

0

You could filter by country and create a persistent array index for that on visited_places[*].country but you still need a secondary condition that ensures that the country and city you look for occur in the same array element:

FOR doc IN usertable
  FILTER @country IN doc.visited_places[*].country
  FILTER LENGTH(doc.visited_places[* FILTER CURRENT.country == @country AND @city IN CURRENT.cities])
  RETURN doc

answered Oct 06 '21 at 12:08

CodeManX

11,159
5
49
70

Thanks! Will an index on visited_places[*].country[*].city also help the secondary condition? – 3bst0r Oct 13 '21 at 05:54
No. It would need to be a single index, but indexing nested arrays is not supported by regular indexes. Moreover, inline expressions do not get optimized but are always executed on the fly. You could use ArangoSearch for indexing which should be able to find matches faster but you will still need a post-FILTER to ensure that country and city co-occur in the same sub-object. – CodeManX Oct 14 '21 at 20:37

ArangoDB AQL deep array scan

1 Answers1