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"
                  }
                }
              }
            }
          }

Can u provide your index mapping ? I am working on your search query and will provide a working example — Amit
– Amit, Commented Aug 19, 2020 at 5:16
Hi @OpsterElasticsearchNinja, I've tried creating a new Index with nested type object, but still I'm facing the issue of not getting results. — user3982364
– user3982364, Commented Aug 26, 2020 at 10:23
I hv given complete example of nested type, did you try that? — Amit
– Amit, Commented Aug 26, 2020 at 10:27

Amit · Accepted Answer · 2020-08-19 08:44:17Z

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
                  }
                }
              ]

Hi I've tried the query but still it ain't working. Also the mapping for activity isn't nested.

Collectives™ on Stack Overflow

Filter ElasticSearch results based on AND condition in an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related