Get documents where sum of values in filtered nested documents match a specified conditions

[radix_s (Radoslaw Kurowski)]

I am tracking orders made by the shop visitors and for each order I put a document that goes into an index like this:

DELETE visitor_carts

PUT visitor_carts
{
  "mappings" : {
    "_doc": {
      "properties" : {
        "order_id": {
          "type": "text"
        },
        "products": {
          "type": "nested",
          "properties": {
            "event_quantity": {
              "type": "long"
            },
            "event_value": {
              "type": "float"
            },
            "product_id": {
              "type": "text"
            },
            "product_lists": {
              "type": "text"
            },
            "product_name": {
              "type": "text"
            }
          }
        },
        "visitor_id": {
          "type": "text"
        }
      }
    }
  }
}

PUT visitor_carts/_doc/1
{
  "visitor_id" : "VisitorUid_1",
  "order_id" : "1000",
  "products" : [
    {
      "product_id" : "10_6",
      "product_name" : "product_10_6",
      "event_value" : 100.56,
      "event_quantity" : 4,
      "product_lists" : [
        "1"
      ]
    },
    {
      "product_id" : "10_6",
      "product_name" : "product_10_6",
      "event_value" : 11,
      "event_quantity" : 2,
      "product_lists" : [
        "42"
      ]
    }
  ]
}

Out of the many purchases that the visitor had made I would like to get the count of documents where the products meet certain criteria:

• Evaluate only products that have "42" in the product_lists
• Sum the value of those products by event_value and it has to be between for example 100.00 and 200.00 to meet the condition
• Sum the quantity of those products by event_quantity and it has to be between 2 and 4 to meet the condition

My instinct tells me that once I make an initial query where I select a specific visitor_id then I have to apply a filter on the query results, perhaps with a painless script that would evaluate each product and compare the result with a set of parameters (the product_lists value, range for event_value and range for event_quantity ) but I have been sitting on this for a few days now and cannot get it to work because this operates on the nested documents. All I have managed to do so far is to get counts for all nested documents for a given visitor but that is not even close to what I need.

Please help, perhaps there is a better way of doing that. So far I have reached something like this, but it is a loooong way from home:

POST visitor_carts/_search
{
  "query" : {
    "bool": {
      "must": [
        {
          "term": {
            "visitor_id": "VisitorUid_1"
          }
        },
        {
         "nested": {
           "path": "products",
           "query": {
             "bool": {
               "must": [
                 {
                   "term": {
                     "products.product_lists": "42"
                   }
                 }
               ]
             }
           }
         }
        }
      ]
    }
  }
}

I am using version 6.5