How to post filter aggregation?

Elastic Stack Elasticsearch
1

Hello,

I am trying to do a simple filter for two days now and I guess I am missing something obvious.

CONTEXT:
We have a commerce data in ES index where every purchased order item is a document. I am trying to find all users who bought a specific SKU only once EVER and then find out the users who did that this month.

MAPPING:

{
	"mappings": {
		"properties": {
			"orderItem.sku": {
				"type": "keyword"
			},
			"user.id": {
				"type": "keyword"
			}
		}
	}
}

SAMPLE DOCUMENT:

{
    "user": {
        "id": "dXJfdEyNA"
    },
    "orderItem": {
        "sku": "BOX34-MSUI",
        "quantity": 1
    },
    "timestamp": "2019-04-04T05:57:33.7300000+00:00"
}

QUERY:

{
	"aggs": {
		"FindUsersWhoPurchasedSkuAsync": {
			"aggs": {
				"this_month": {
					"date_range": {
						"field": "timestamp",
						"ranges": [{
								"from": "2020-03-01T00:00:00-00:00",
								"to": "2020-03-31T00:00:00-00:00",
								"key": "march"
							}
						],
                		"keyed": true
					}
				},
				"total_quantity": {
					"sum": {
						"field": "orderItem.quantity"
					}
				},
				"sales_bucket_filter": {
					"bucket_selector": {
						"script": {
							"source": "params.totalQuantity == 1 && params.asd.doc_count > 0"
						},
						"buckets_path": {
							"totalQuantity": "total_quantity",
							"asd": "this_month[march]"
						}
					}
				}
			},
			"composite": {
				"size": 4000,
				"sources": [{
					"sku": {
						"terms": {
							"field": "orderItem.sku"
						}
					}
				}, {
					"user": {
						"terms": {
							"field": "user.id"
						}
					}
				}]
			}
		}
	},
	"query": {
		"bool": {
			"filter": [{
				"bool": {
					"should": [{
						"match_phrase": {
							"orderItem.sku": {
								"query": "BOX34-MSUI"
							}
						}
					}]
				}
			}]
		}
	},
	"size": 0
}

So far I have success with the single purchase rule but I am failing with date range filter.

I have tried many other options and I am posting my latest try here. Here is the error which I get:

    {
    "error": {
        "root_cause": [],
        "type": "search_phase_execution_exception",
        "reason": "",
        "phase": "fetch",
        "grouped": true,
        "failed_shards": [],
        "caused_by": {
            "type": "script_exception",
            "reason": "runtime error",
            "script_stack": [
                "params.totalQuantity == 1 && params.asd.doc_count > 0",
                "                                       ^---- HERE"
            ],
            "script": "params.totalQuantity == 1 && params.asd.doc_count > 0",
            "lang": "painless",
            "caused_by": {
                "type": "null_pointer_exception",
                "reason": null
            }
        }
    },
    "status": 400
}

Here is a response without the date range attempt. The first result fulfills the quantity rule but it is not inside the defined range. The doc_count is 0 and I want to exclude that document from the result set.

{
    "took": 353,    "timed_out": false,
    "_shards": {...},
    "hits": {...},
    "aggregations": {
        "FindUsersWhoPurchasedSkuAsync": {
            "after_key": {
                "sku": "BOX34-MSUI",
                "user": "dXJuOnByddfjEwMTMfdh"
            },
            "buckets": [
                {
                    "key": {
                        "sku": "BOX34-MSUI",
                        "user": "dXdfhdDpwcm9madfh0d"
                    },
                    "doc_count": 1,
                    "this_month": {
                        "buckets": {
                            "march": {
                                "from": 1.5830208E12,
                                "from_as_string": "2020-03-01T00:00:00.000Z",
                                "to": 1.5856128E12,
                                "to_as_string": "2020-03-31T00:00:00.000Z",
                                "doc_count": 0
                            }
                        }
                    },
                    "total_quantity": {
                        "value": 1.0
                    }
                },
...

Could you please point me to the right direction?

Thank you!

2

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