ElasticSearch Query fields based on conditions on another field

Question

Mapping

PUT /employee
{
    "mappings": {
        "post": {
            "properties": {
                "name": {
                    "type": "keyword"
                },
                "email_ids": {                    
                    "properties":{
                        "id" : { "type" : "integer"},
                        "value" : { "type" : "keyword"}
                    }
                },
                "primary_email_id":{
                    "type": "integer"
                }
            }
        }
    }
}

Data

POST employee/post/1
{
    "name": "John",
    "email_ids": [
        {
            "id" : 1,
            "value" : "[email protected]"
        },
        {
            "id" : 2,
            "value" : "[email protected]"
        }
    ],
    "primary_email_id": 2 // Here 2 refers to the id field of email_ids.id ([email protected]).
    
}

I need help to form a query to check if an email id is already taken as a primary email?

eg: If I query for [email protected] I should get result as No as [email protected] is not a primary email id.

If I query for [email protected] I should get result as Yes as [email protected] is a primary email id for John.

Answer 1

As far as i know with this mapping you can not achive what you are expecting.

But, You can create email_ids field as nested type and add one more field like isPrimary and set value of it to true whenever email is primary email.

Index Mapping

PUT employee
{
  "mappings": {
    "properties": {
      "name": {
        "type": "keyword"
      },
      "email_ids": {
        "type": "nested", 
        "properties": {
          "id": {
            "type": "integer"
          },
          "value": {
            "type": "keyword"
          },
          "isPrimary":{
            "type": "boolean"
          }
        }
      },
      "primary_email_id": {
        "type": "integer"
      }
    }
  }
}

Sample Document

POST employee/_doc/1
{
  "name": "John",
  "email_ids": [
    {
      "id": 1,
      "value": "[email protected]"
    },
    {
      "id": 2,
      "value": "[email protected]",
      "isPrimary": true
    }
  ],
  "primary_email_id": 2
}

Query

You need to keep below query as it is and only need to change email address when you want to see if email is primary or not.

POST employee/_search
{
  "_source": false, 
  "query": {
    "nested": {
      "path": "email_ids",
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "email_ids.value": {
                  "value": "[email protected]"
                }
              }
            },
            {
              "term": {
                "email_ids.isPrimary": {
                  "value": "true"
                }
              }
            }
          ]
        }
      }
    }
  }
}

Result

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.98082924,
    "hits" : [
      {
        "_index" : "employee",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.98082924
      }
    ]
  }
}

Interpret Result:

Elasticsearch will not return result in boolean like true or false but you can implement it at application level. You can consider value of hits.total.value from result, if it is 0 then you can consider false otherwise true.

PS: Answer is based on ES version 7.10.