AQL: Partial match in array of strings

Question

I have a table that looks like this:

{"_key": "1", "name": "George Washington", "cars": ["Ford Focus"]}
{"_key": "2", "name": "John Adams", "cars": ["Pontiac Firebird", "Toyota Corolla"]}
{"_key": "3", "name": "Thomas Jefferson", "cars": ["Toyota Corolla"]}
{"_key": "4", "name": "James Madison", "cars": ["Ford Mustang", "Porsche 911"]}

Now I would like to find the names of all persons who have a Ford (i.e., ["George Washington", "James Madison"]). Of course

FOR doc IN documents 
    FILTER ("Ford Focus" IN doc.cars OR "Ford Mustang" IN doc.cars) 
    RETURN doc.name

works, but suppose I do not know what types of Fords exist. How can I search for a part of a string in an array?

score 2 · Accepted Answer · answered Oct 06 '17 at 06:41

One way is to do it like this:

FOR doc IN documents 
  LET matchingCars = (
    FOR car IN doc.cars 
      FILTER LOWER(car) LIKE('ford%') 
      LIMIT 1 
      RETURN 1
  ) 
  FILTER LENGTH(matchingCars) > 0 
  RETURN doc.name

A few notes: the query will also work without LIMIT, but using LIMIT will stop searching for matching cars in a document once one has been found in it. The query uses LIKE function for string matching, but you can of course use any of the others. Finally I lowercased the value of the car attribute before comparing it, as the comparison is potentially expected to be case-insensitive. Please adjust to your needs.

AQL: Partial match in array of strings

1 Answers1