Filter elastic data on array count

Question

How can we fetch candidates which have at least one phone number from the below index data along with other conditions like must and should?

Using elastic version 6.*

        {
            "_index": "test",
            "_type": "docs",
            "_id": "1271",
            "_score": 1.518617,
            "_source": {
                "record": {
                    "createdDate": "2020-10-16T10:49:51.53",
                    "phoneNumbers": [
                        {
                            "type": "Cell",
                            "id": 0,
                            "countryCode": "+1",
                            "phoneNumber": "7845200448",
                            "extension": "",
                            "typeId": 700
                        }
                    ]
                },
                "entityType": "Candidate",                   
                "dbId": "1271",
                "id": "1271"
            }
        }

user_10348758 · Answer

You can use terms query that returns documents that contain one or more exact terms in a provided field.

Search Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "record.phoneNumbers.phoneNumber.keyword": [
              "7845200448"
            ]
          }
        }
      ]
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "stof_64388591",
        "_type": "_doc",
        "_id": "1",
        "_score": 1.0,
        "_source": {
          "record": {
            "createdDate": "2020-10-16T10:49:51.53",
            "phoneNumbers": [
              {
                "type": "Cell",
                "id": 0,
                "countryCode": "+1",
                "phoneNumber": "7845200448",
                "extension": "",
                "typeId": 700
              }
            ]
          },
          "entityType": "Candidate",
          "dbId": "1271",
          "id": "1271"
        }
      }
    ]

Update 1: For version 7.*

You need to use a script query, to filter documents based on the provided script.

{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": "doc['record.phoneNumbers.phoneNumber.keyword'].length > 0",
            "lang": "painless"
          }
        }
      }
    }
  }
}

For version 6.*

{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": "doc['record.phoneNumbers.phoneNumber.keyword'].values.length > 0",
            "lang": "painless"
          }
        }
      }
    }
  }
}

user_5088999 · Answer

You can use exists query for this purpose like below which is a lightweight query in comparison with scripts:

{
  "query": {
      "exists": {
        "field": "record.phoneNumbers.phoneNumber"
      }
  }
}

Filter elastic data on array count

2 Answers