Querying multiple condition on same field

Elastic Stack Elasticsearch
1

These are the sample documents(event based) indexed in my ES cluster version 6.8

{
  "user_id": "user1",
  "account_id": "account1",
  "event_name": "event-1",
  "created_dt": "2019-09-30T08:40:42.297Z"
}

{
"user_id": "user1",
"account_id": "account1",
"event_name": "event-2",
"created_dt": "2019-10-30T08:40:42.297Z"
}

{
"user_id": "user2",
"account_id": "account1",
"event_name": "event-1",
"created_dt": "2019-10-30T08:40:42.297Z"
}

What i am trying to accomplish is fetching the list of users who have taken event_name "event-1" and "event-2" on stipulated time dynamically.

The query below which is having 2 must condition on same field "event_name" which eventually results empty.

{
"query": {
"bool": {
  "must": [
    {
      "match": {
        "account_id": "account1"
      }
    },
    {
      "bool": {
        "must": [
          {
            "match": {
              "event_name.raw": "event-1"
            }
          },
          {
            "range": {
              "created_dt": {
                "gte": "2019-09-30",
                "lte": "2019-10-30"
              }
            }
          }
        ]
      }
    },
    {
      "bool": {
        "must": [
          {
            "match": {
              "event_name.raw": "event-2"
            }
          },
          {
            "range": {
              "created_dt": {
                "gte": "2019-10-30",
                "lte": "2019-11-05"
              }
            }
          }
        ]
      }
    }
  ]
}
},
"size": 0,
"aggs": {
"user_list": {
  "terms": {
    "field": "user_id.raw"
  }
}
}
}

what is the ideal way to get the desired bucket documents? It can be done in SQL using IN and a sub query but couldn't look easier in ES

2

any update please?

3

A SQL IN clause often results in a join, which Elasticsearch does not support. To do this you may need to resort to an entity-centric index. You may also be able to do this through the use of transforms.

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.