Elastic Search - complex query with array types and nested object properties

Question

Given the following mapping I need to get results that match the following criteria

1. Exact match on first name, last name, date_of_birth and Active = true OR
2. Exact match on first name, last name, Active = true and 1 email out of possible multiples OR
3. Exact match on first name, last name, Active = true and 1 traveldocument number out of possible multiples

Where email and travel document can refer to a collection of items.

    {
    "profile":{
        "properties":{

            "date_of_birth":{
                "type":"date",
                "store":"no"
            },
            "first_name":{
                "type":"string",
                "store":"no"
            },
            "last_name":{
                "type":"string",
                "store":"no"
            },
            "email":{
                "type":"string",
                "store":"no"
            },
            "active":{
                "type":"string",
                "store":"no"
            },
            "travel_document":{
              "properties" : {
                   "countryOfCitizenship" : {"type" : "string"},
                   "countryOfIssue" : {"type" : "string"},
                   "expirationDate" : {"type" : "date"},
                   "nationality" : {"type" : "string"},
                   "number" : {"type" : "string"},
                   "addressLines" : {"type": "string"},
                   "issuedForAreaCode" : {"type": "string"},
                   "type" : {"type": "string"}
                }
            }
        }
    }
}

Is there a way I can execute this kind of search in elasticsearch? Can I do this with Nested Queries ?

Answer 1

Yes you can.

First, to answer your question about nested queries:

If you need to query more than one field in the SAME OBJECT in a collection of objects (eg travel_document.nationality and travel_document.expirationDate then you need to change travel_document from type object to type nested and to use nested queries.

In the example queries you have given, you haven't shown that you need this functionality. Instead, you are asking if ANY travel_document has a value. So in this case, you don't need to use the nested functionality.

(If you think you may need to do queries on correlated fields in the future, then you probably do want to use nested. You can also set include_in_root to index the nested objects both as separate nested objects and in the main document).

For the query below, I've assumed that travel_document is not nested.

Second: your use of "exact match" in the name fields.

By default, string fields are analyzed, so "Mary Jane" would be indexed as the terms ['mary','jane']. If you run a query on that field looking for "Mary", then it will match, because the field does indeed contain "mary". However, this is not an exact match.

If you want to do exact matching, then you need to make the field not_analyzed, in which case "Mary Jane" would be indexed as the single term "Mary Jane", and a query for "Mary" would not match. The downside would be that you couldn't use full text queries on the name field in this case.

Similarly, it may make more sense to have the email field not_analyzed (or use a custom analyzer with the keyword tokenizer - which doesn't tokenize the string - and the lowercase token filter).

In the query below, I've assumed that your name fields are analyzed and that your email field is not analyzed:

curl -XGET 'http://127.0.0.1:9200/my_index/properties/_search?pretty=1'  -d '
{
   "query" : {
      "filtered" : {
         "query" : {
            "bool" : {
               "must" : [
                  {
                     "match_phrase" : {
                        "first_name" : "mary jane"
                     }
                  },
                  {
                     "match_phrase" : {
                        "last_name" : "smith"
                     }
                  }
               ]
            }
         },
         "filter" : {
            "and" : [
               {
                  "term" : {
                     "active" : 1
                  }
               },
               {
                  "or" : [
                     {
                        "term" : {
                           "date_of_birth" : "1980-01-01"
                        }
                     },
                     {
                        "terms" : {
                           "email" : [
                              "mary@smith.com",
                              "maryjane@smith.com"
                           ]
                        }
                     },
                     {
                        "terms" : {
                           "travel_document.number" : [
                              "1234",
                              1235
                           ]
                        }
                     }
                  ]
               }
            ]
         }
      }
   }
}
'