# Applying a filter to the last x nested documents in the selected period, to find if one of them match a condition

**URL:** <https://discuss.elastic.co/t/applying-a-filter-to-the-last-x-nested-documents-in-the-selected-period-to-find-if-one-of-them-match-a-condition/279399>\
**Category:** Elasticsearch\
**Created:** [July 22, 2021, 2:48pm UTC](https://discuss.elastic.co/t/applying-a-filter-to-the-last-x-nested-documents-in-the-selected-period-to-find-if-one-of-them-match-a-condition/279399 "2021-07-22T14:48:40Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChqThomas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chqthomas/32/92004_2.png) [@ChqThomas](https://discuss.elastic.co/u/ChqThomas)\
**Post date:** [July 22, 2021, 2:48pm UTC](https://discuss.elastic.co/t/applying-a-filter-to-the-last-x-nested-documents-in-the-selected-period-to-find-if-one-of-them-match-a-condition/279399/1 "2021-07-22T14:48:40Z")

</div>

Hi,

I'm trying to retrieve the number of users who bought the product "123" at least once in their last three sales in the selected period.

With this data structure :

mapping :

```auto
{
  "properties": {
    "user_id": {
      "type": "keyword"
    },
    "sales": {
      "type": "nested",
      "properties": {
        "sale_id": {
          "type": "keyword"
        },
        "date": {
          "type": "date"
        },
        "sales": {
          "type": "nested",
          "properties": {
            "product_id": {
              "type": "keyword"
            },
            "quantity": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}

```

data example :

```auto
{
  "user_id": "1",
  "sales": [
    {
      "sale_id": "1",
      "date": "2021-03-01",
      "products": [
        {
          "product_id": "123",
          "quantity": 1
        },
        {
          "product_id": "456",
          "quantity": 2
        }
        ...
      ]
    },
    {
      "sale_id": "2",
      "date": "2020-02-01",
      "products": [
        {
          "product_id": "123",
          "quantity": 4
        },
        {
          "product_id": "789",
          "quantity": 3
        }
        ...
      ]
    }
  ]
}

```

I wrote a query that assigns a score of 1000000 per sale containing the product I'm looking for to filtre user by min\_score, but I can't limit the scoring to the last 3 sales.

I tried to add an inner\_hits in the nested filter but it is not taken into account in the score calculation.

This is my current query :

```auto
{
  "size": 0,
  "track_total_hits": true,
  "query": {
    "bool": {
      "must": [
        {
          "function_score": {
            "min_score": 1000000,
            "score_mode": "sum",
            "query": {
              "bool": {
                "must": [
                  {
                    "nested": {
                      "inner_hits": {
                        "size": 3,
                        "sort": {
                          "sales.date": "DESC"
                        }
                      },
                      "path": "sales",
                      "score_mode": "sum",
                      "query": {
                        "function_score": {
                          "min_score": 1,
                          "query": {
                            "bool": {
                              "must": [
                                {
                                  "constant_score": {
                                    "filter": {
                                      "range": {
                                        "sales.date": {
                                          "gte": "2019-01-01",
                                          "lte": "2020-01-01"
                                        }
                                      }
                                    },
                                    "boost": 1
                                  }
                                }
                              ],
                              "should": [
                                {
                                  "constant_score": {
                                    "filter": {
                                      "terms": {
                                        "sales.products.product_id": [
                                          "123"
                                        ]
                                      }
                                    },
                                    "boost": 1000000
                                  }
                                }
                              ]
                            }
                          }
                        }
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

```

Is it possible to isolate the last 3 sales of each user according to the applied date filter, then to apply on these 3 sales a filter to know if at least one of them contains the searched product?

Thanks for your help !

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 19, 2021, 2:49pm UTC](https://discuss.elastic.co/t/applying-a-filter-to-the-last-x-nested-documents-in-the-selected-period-to-find-if-one-of-them-match-a-condition/279399/2 "2021-08-19T14:49:17Z")

</div>

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