CODEWITHSUNDEEP
elasticsearch
stackoverflowsucks
stackoverflowsucks

Reputation: 247

Elasticsearch term filter with multiple values

I have the following mappings:

PUT /files
{
   "mappings": {
      "file": {
         "properties": {
            "FileID": {
               "type": "integer"
            },
            "FolderID": {
               "type": "integer"
            }
         }
      }
   }
}

My data is:

PUT /clients/client/1
{
    "id":"1",
    "name":"Joe Doe", 
    "FolderIDs":["577173","245340","777035"],
    "Emails" : ["some@email.com", "other@email.com"]
}



PUT /files/file/1
{
    "FileID": "10550",
    "FolderID" : "577173"
}

My query is:

GET /_search
{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "terms": {
               "FolderID": {
                  "index": "clients",
                  "type": "client",
                  "id": "1",
                  "path": "FolderIDs"
               }
            }
         }
      }
   }
}

This returns the file with ID 10550, great. My question is how to do this on an email field with a list of emails eg.

mapping:

PUT /emails
{
   "mappings": {
      "email": {
         "properties": {
            "EmailID": {
               "type": "integer"
            },
            "ADDRESS_FROM": {
               "type": "string",
               "index" : "not_analyzed"
            }
      } 
   } 
}

data:

PUT /emails/email/1
{
    "EmailID": "8335",
    "ADDRESS_FROM" : "random@email.com user@email.com"
}

How do I build a query that returns emails that DO NOT have any of the emails in ADDRESS_FROM from the client Emails field?

i.e.

client 1 has ["some@email.com", "other@email.com"] so return email 1 because ADDRESS_FROM does not contain any of client 1 Emails ("random@email.com user@email.com").

I have tried something similar to this (does not work):

GET /_search
{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
   "filter": {
      "bool": {
         "must_not": [
            {
               "terms": {
                  "ADDRESS_FROM": {
                     "index": "clients",
                     "type": "client",
                     "id": "1",
                     "path": "Emails"
                  }
               }
            }
         ]
      }
   }
}

Upvotes: 1

Views: 5681

Answers (1)

keety
keety

Reputation: 17461

Ensure that the Emails field in clients index is set to not_analyzed.

Ideally the AddressForm field should be an array of emails. i.e "ADDRESS_FROM" : ["random@email.com", "user@email.com"]

instead of

"ADDRESS_FROM" : "random@email.com user@email.com"

If changing document structure is not an option you would need to use whitespace analyzer for AddressForm .

The below example demonstrates this :

PUT /clients
{
   "mappings": {
      "client": {
         "properties": {
            "FolderIDs": {
               "type": "integer"
            },
           "Emails" : {
               "type": "string",
               "index" : "not_analyzed"
            }
         }
      }
   }
}
PUT /clients/client/1
{
    "id":"1",
    "name":"Joe Doe", 
    "FolderIDs":["577173","245340","777035"],
    "Emails" : ["some@email.com", "other@email.com"]
}

PUT /clients/client/2
{
    "id":"1",
    "name":"Joe Doe", 
    "FolderIDs":["577173","245340","777035"],
    "Emails" : ["random@email.com", "other@email.com"]
}

PUT /emails
{
   "mappings": {
      "email": {
         "properties": {
            "EmailID": {
               "type": "integer"
            },
            "ADDRESS_FROM": {
               "type": "string",
               "analyzer": "whitespace"
            }
         }
      }
   }
}

PUT /emails/email/1
{
    "EmailID": "8335",
    "ADDRESS_FROM" : "random@email.com user@email.com"
}

Example Query 1:

POST emails/_search
{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "bool": {
               "must_not": [
                  {
                     "terms": {
                        "ADDRESS_FROM": {
                           "index": "clients",
                           "type": "client",
                           "id": "1",
                           "path": "Emails"
                        }
                     }
                  }
               ]
            }
         }
      }
   }
}

 "hits": {
      "total": 1,
      "max_score": 1,
      "hits": [
         {
            "_index": "emails",
            "_type": "email",
            "_id": "1",
            "_score": 1,
            "_source": {
               "EmailID": "8335",
               "ADDRESS_FROM": "random@email.com user@email.com"
            }
         }
      ]
   }

Example Query2 (0 hits):

POST emails/_search
{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "bool": {
               "must_not": [
                  {
                     "terms": {
                        "ADDRESS_FROM": {
                           "index": "clients",
                           "type": "client",
                           "id": "2",
                           "path": "Emails"
                        }
                     }
                  }
               ]
            }
         }
      }
   }
}

  "hits": {
      "total": 0,
      "max_score": null,
      "hits": []
   }

Upvotes: 1

Related Questions