Filter ElasticSearch results based on AND condition in an array

Question

Below is the data sample of a single record in my Index:

{
        "_index" : "test_index",
        "_type" : "_doc",
        "_id" : "49605102905391763685971719283371021096086998966740189186",
        "_score" : 2.8858113,
        "_source" : {
          "properties" : {
            "activity" : [
              {
                "activityId" : 39,
                "actionVersion" : 2,
                "actionIdx" : 3
              },
              {
                "activityId" : 39,
                "actionVersion" : 2,
                "actionIdx" : 4
              },
              {
                "activityId" : 39,
                "actionVersion" : 2,
                "actionIdx" : 5
              },
              {
                "activityId" : 42,
                "actionVersion" : 2,
                "actionIdx" : 3
              },
              {
                "activityId" : 42,
                "actionVersion" : 2,
                "actionIdx" : 4
              },
              {
                "activityId" : 42,
                "actionVersion" : 2,
                "actionIdx" : 5
              }
            ]
          }
        }
      }

I want to filter results based on activitiyId 42 and actionIdx 3. I'm using below query trying to get results.

{"size":-1,
  "query": {
    "bool": {
            "should": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "activityId": 42
                            }
                        },
                        {
                            "match": {
                                "actionIdx": 3
                            }
                        }
                        
                        
                    ]
                }
            }
        }
  }
}

I'm getting the records that contain activityid 42 and actionidx 3 but my requirement is to have records that contain activity 42 and actionidx 3 in the same object. Below are my results with above search query:

      "activity" : [
              {
                "activityId" : 39,
                "actionVersion" : 2,
                "actionIdx" : 2
              },
              {
                "activityId" : 28,
                "actionVersion" : 2,
                "actionIdx" : 3
              },
              {
                "activityId" : 42,
                "actionVersion" : 2,
                "actionIdx" : 2
              },
              {
                "activityId" : 41,
                "actionVersion" : 2,
                "actionIdx" : 3
              }
            ]

My expected output to be:

"activity" : [
              {
                "activityId" : 39,
                "actionVersion" : 2,
                "actionIdx" : 2
              },
              {
                "activityId" : 28,
                "actionVersion" : 2,
                "actionIdx" : 3
              },
              {
                "activityId" : 42,
                "actionVersion" : 2,
                "actionIdx" : 3
              }
            ]

Here's is the mapping index. Activity is not nested

"properties" : {
            "properties" : {
              "activity" : {
                "properties" : {
                  "actionIdx" : {
                    "type" : "long"
                  },
                  "actionVersion" : {
                    "type" : "long"
                  },
                  "activityId" : {
                    "type" : "long"
                  }
                }
              }
            }
          }

user_4039431 · Answer

you need to use nested data type and query to achieve that, right now as you are not using it, its considered as object data type(default) and nested documents clearly mention the issue you are facing.

When ingesting key-value pairs with a large, arbitrary set of keys, you might consider modeling each key-value pair as its own nested document with key and value fields. Instead, consider using the flattened data type, which maps an entire object as a single field and allows for simple searches over its contents. Nested documents and queries are typically expensive, so using the flattened data type for this use case is a better option.

Index Mapping

    {
  "mappings": {
    "properties": {
      "properties": {
        "properties": {
          "activity": {
            "type": "nested"
          }
        }
      }
    }
  }
}

Index Data:

{
    "properties": {
        "activity": [
            {
                "activityId": 39,
                "actionVersion": 2,
                "actionIdx": 3
            },
            {
                "activityId": 39,
                "actionVersion": 2,
                "actionIdx": 4
            },
            {
                "activityId": 39,
                "actionVersion": 2,
                "actionIdx": 5
            },
            {
                "activityId": 42,
                "actionVersion": 2,
                "actionIdx": 3
            },
            {
                "activityId": 42,
                "actionVersion": 2,
                "actionIdx": 4
            },
            {
                "activityId": 42,
                "actionVersion": 2,
                "actionIdx": 5
            }
        ]
    }
}

Search Query:

{
  "query": {
    "nested": {
      "path": "properties.activity",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "properties.activity.activityId": 42
              }
            },
            {
              "match": {
                "properties.activity.actionIdx": 3
              }
            }
          ]
        }
      },
      "inner_hits":{}
    }
  }
}

Search Result

 "hits": [
                {
                  "_index": "fd_cb1",
                  "_type": "_doc",
                  "_id": "1",
                  "_nested": {
                    "field": "properties.activity",
                    "offset": 3
                  },
                  "_score": 2.0,
                  "_source": {
                    "activityId": 42,
                    "actionVersion": 2,
                    "actionIdx": 3
                  }
                }
              ]

Filter ElasticSearch results based on AND condition in an array

1 Answers