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

[ChqThomas (Thomas)]

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 :

{
  "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 :

{
  "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 :

{
  "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 !