Elasticsearch query to get the list of documents with some minimum occurrence of a property

user_16788644 0
Viewed 607

I have a index with documents like this

[
 {
   "customer_id" : "123",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-23"
   ...
 },
 {
   "customer_id" : "123",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-24"
   ...
 },
 {
   "customer_id" : "345",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-25"
   ...
 }
]

I want to get the list of all documents from specific country e.g USA, between a give time range with at least 2 occurrences of same customer_id. With the above data, it should return

[
 {
   "customer_id" : "123",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-24"
   ...
 }
]

Now, I tried the below ES query

POST /index_name/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "creation_date": {
              "gte": "2021-06-23",
              "lte": "2021-08-23"
            }
          }
        },
        {
          "match": {
            "country": "USA"
          }
        }
      ]
    }
  },
  "aggs": {
    "customer_agg": {
      "terms": {
        "field": "customer_id",
        "min_doc_count": 2
      }
    }
  }
}

The above query returns following result

"hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 1.5587491,
    "hits" : [...]
    ]
  },
  "aggregations" : {
    "person_agg" : {
      "doc_count_error_upper_bound" : 1,
      "sum_other_doc_count" : 1,
      "buckets" : [
        {
          "key" : "customer_id",
          "doc_count" : 2
        }
      ]
    }
  }

I don't need the list of buckets in response, but only the list of documents satisfying the condition. How can I achieve it?

elasticsearch-5 elasticsearch
1 Answers

On a first glance I noticed that in the search query you are searching by a field called creation_timestamp but in the mapping of the document you say that the date field you want to range check is called creation_date.

I decided to test this locally on Elasticsearch 7.10 and here are the settings I used

PUT /test-index-v1

PUT /test-index-v1/_mapping
{
        "properties": {
            "customer_id": {
                "type": "keyword"
            },
            "country": {
                "type": "keyword"
            },
            "department": {
                "type": "keyword"
            },
            "creation-date": {
              "type": "date"
            }
        }
}

As you can see I'm using keyword on the fields so that we can use - sorting, aggregation and etc.

After I created the index I imported the documents you gave as an example

POST /test-index-v1/_doc
 {
   "customer_id" : "345",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-25"
}

POST /test-index-v1/_doc
 {
   "customer_id" : "123",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-25"
}

POST /test-index-v1/_doc
 {
   "customer_id" : "123",
   "country": "USA",
   "department": "IT",
   "creation_date" : "2021-06-24"
}

Then I executed this search query including a must match on the customer_id as well:

POST /test-index-v1/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "creation_date": {
              "gte": "2021-06-23",
              "lte": "2021-08-23"
            }
          }
        },
        {
          "match": {
            "country": "USA"
          }
        },
        {
          "match": {
            "customer_id": "123"
          }
        }
      ]
    }
  },
  "aggs": {
    "customer_agg": {
      "terms": {
        "field": "customer_id",
        "min_doc_count": 2
      }
    }
  }
}

This query will return you the search hits as well. Using only an aggregation the searchHits won't be returned.

Here is the response I received:


  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.6035349,
    "hits" : [
      {
        "_index" : "test-index-v1",
        "_type" : "_doc",
        "_id" : "vbVD9HsBRVWFAvvZTW-l",
        "_score" : 1.6035349,
        "_source" : {
          "customer_id" : "123",
          "country" : "USA",
          "department" : "IT",
          "creation_date" : "2021-06-25"
        }
      },
      {
        "_index" : "test-index-v1",
        "_type" : "_doc",
        "_id" : "vrVD9HsBRVWFAvvZU29q",
        "_score" : 1.6035349,
        "_source" : {
          "customer_id" : "123",
          "country" : "USA",
          "department" : "IT",
          "creation_date" : "2021-06-24"
        }
      }
    ]
  },
  "aggregations" : {
    "customer_agg" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "123",
          "doc_count" : 2
        }
      ]
    }
  }
}

Hope this helps with your issue. Feel free to leave a comment if you have other questions regarding Elastic! :)

EDIT:

Regarding the grouping by customer_id in a certain date range I used this query:

POST /test-index-v1/_search
{
  "aggs": {
    "group_by_customer_id": {
      "terms": {
        "field": "customer_id"
      },
      "aggs": {
        "dates_between": {
          "filter": {
            "range": {
              "creation_date": {
                "gte": "2020-06-23",
                "lte": "2021-06-24"
              }
            }
          }
        }
      }
    }
  }
}

And the response is:

"aggregations" : {
    "group_by_customer_id" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "123",
          "doc_count" : 2,
          "dates_between" : {
            "doc_count" : 1
          }
        },
        {
          "key" : "345",
          "doc_count" : 1,
          "dates_between" : {
            "doc_count" : 0
          }
        }
      ]
    }
  }
user_90642290
Related