Getting wrong data in aggs using script or field property

[webmastervishal]

I have setup kibana for elastic search and in dev tools, I have been querying data but getting incorrect results.

My Elastic search data is generated by a food ordering app, so basically, the structure of data is like I am fetching all those orders and inside each order, I have menu items and its count.

For example, I will be using a single menu item (i.e Boil Egg )here for elaboration.

So below is my query to get the items to count for "Boil Egg" menu item

Query

:

GET _search
{
  "size": 5000,
  "aggs": {
    "items_count": {
      "sum": {
        "field": "data.items.count"
      }
    },
    "requests": {
      "terms": {
        "field": "data.items.menuitemname.keyword",
        "size": 5000,
        "order": {
          "itemscount": "desc"
        }
      },
      "aggs": {
        "totalprice": {
          "sum": {
            "field": "data.items.totalprice"
          }
        },
        "itemscount": {
          "sum": {
            "field": "data.items.count"
          }
        }
        }
      }
    }
  },
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "term": {
            "appId.keyword": "5825d15cf009c4bc37fd7b9f"
          }
        },
        {
          "term": {
            "entityId.keyword": "5825d66df009c4bc37fd7bae"
          }
        },
        {
          "range": {
            "data.placedtime": {
              "gte": "2019-07-31T18:30:00.000Z",
              "lt": "2019-08-01T18:29:59.999Z"
            }
          }
        }
      ],
      "should": [
        [
          {
            "term": {
              "data.parentId.keyword": "5d0fb4173516ac51d7498f63"
            }
          }
        ]
      ],
      "must_not": [],
      "minimum_should_match": 1
    }
  }
}

Note: I tried with the script as well but getting the same result
script: "doc['data.items.menuitem.keyword'].value"
I know with this query there are two orders that will be pulled by an elastic search query.

Order 1 :

{
  "_index": "gokhanaprod",
  "_type": "entityrecord",
  "_id": "5d42d2e193cf3923fb1558a4",
  "_score": 3.6844535,
  "_source": {
    "appId": "5825d15cf009c4bc37fd7b9f",
    "createdAt": "2019-08-01T11:54:09.832Z",
    "createdby": "5cf4be2ab1a01b4c13554c9c",
    "data": {
      "checksum": "a432f092-1f3f-4b78-b852-854b4d7f9cbb",
      "createdplatform": "gokhanapartnerfm",
      "creditdiscount": null,
      "customercount": 0,
      "discountfromcredit": 0,
      "discountsplit": [],
      "foodcourt": "5cdbf8b2bca97402a052bbdf",
      "foodcourtname": "Marvel Edge",
      "grossamount": 20,
      "grossamount_p": 20,
      "itemprice_included_taxes": [],
      "items": [
        {
          "totalprice": 20,
          "restaurant": "5d0fb4173516ac51d7498f63",
          "count": 2,
          "price_includes_tax": false,
          "features": [],
          "menuitemname": "Boil Egg ",
          "restaurantcustomer": null,
          "customeitems": [],
          "restaurantmenuitem": "5d1b2cdf5bfcca209234bb10",
          "included_tax_items": [
            {
              "name": "CGST",
              "value": 2.5
            },
            {
              "name": "SGST",
              "value": 2.5
            }
          ],
          "itemprice": 10,
          "specialnotes": null,
          "acceptedtime": "2019-08-01T11:54:12.382Z",
          "id": "451fe67a-0ec0-4ab8-a8fd-1833163c6178",
          "taxAmount": 0,
          "status": "completed"
        }
      ],
      "itemscount": 2,
      "merchant_price": 20,
      "orderid": "ORD10273973",
      "orderstatus": "completed",
      "ordertype": "dine-in",
      "paid": true,
      "paidamount": 20,
      "paidtime": "2019-08-01T11:54:08.738Z",
      "parcelcharge": 0,
      "parentId": "5d0fb4173516ac51d7498f63",
      "paymentgateway": "onsite",
      "paymentstatus": "complete",
      "paymenttransactions": [
        {
          "paidamount": 20,
          "paymetcollectedby": "5cf4be2ab1a01b4c13554c9c",
          "paymentgateway": "gokhana",
          "paid": true,
          "paidtime": "2019-08-01T11:54:08.738Z",
          "paymenttype": "cash"
        }
      ],
      "paymenttype": "cash",
      "placedtime": "2019-08-01T11:54:08.768Z",
      "prepaid": true,
      "promotion": null,
      "refundamounttocustomer": 0,
      "restaurantcustomer": null,
      "restaurantname": "Super Cafe - Meal",
      "restauranttable": null,
      "taxamount": 0,
      "taxitems": [],
      "tokenno": "C216",
      "totalprice": 20,
      "transactions": [],
      "acceptedtime": "2019-08-01T11:54:12.382Z",
      "acceptanceWaitedDuration": "-PT3.614S",
      "closedtime": "2019-08-01T14:21:36.026Z"
    },
    "entityId": "5825d66df009c4bc37fd7bae",
    "lastupdatedby": "5d0fb92030928751d16d8e04",
    "updatedAt": "2019-08-01T14:21:37.404Z",
    "id": "5d42d2e193cf3923fb1558a4"
  }
}

Order 2:

{
  "_index": "gokhanaprod",
  "_type": "entityrecord",
  "_id": "5d429d4a6729d40a121cc29d",
  "_score": 3.673422,
  "_source": {
    "appId": "5825d15cf009c4bc37fd7b9f",
    "createdAt": "2019-08-01T08:05:30.281Z",
    "createdby": "5cff6064b67f1072d4e85b46",
    "data": {
      "appversion": "71",
      "checksum": "73cbf286-ceda-4506-abd9-5e6755e8e7a8",
      "createdplatform": "gokhanacustomer",
      "creditdiscount": null,
      "customername": "Dipayan",
      "customerno": "9609744454",
      "discountfromcredit": 0,
      "discountsplit": [],
      "externaltransactionid": "20190801111212800110168904380924952",
      "foodcourt": "5cdbf8b2bca97402a052bbdf",
      "foodcourtname": "Marvel Edge",
      "grossamount": 50,
      "grossamount_p": 50,
      "itemprice_included_taxes": [],
      "items": [
        {
          "restaurantname": "Super Cafe - Meal",
          "placedtime": "2019-08-01T08:05:29.18Z",
          "totalprice": 40,
          "restaurant": "5d0fb4173516ac51d7498f63",
          "count": 1,
          "price_includes_tax": false,
          "features": [],
          "baseprice": 40,
          "menuitemname": "Dal Rice",
          "restaurantcustomer": "5cff6064b67f1072d4e85b46",
          "customeitems": [],
          "restaurantmenuitem": "5d11c7eee3799451a54d2533",
          "itemprice": 40,
          "acceptedtime": "2019-08-01T08:05:33.059Z",
          "id": "5acc7848-cfb0-439a-9d50-4d2a944f6629",
          "taxAmount": 0,
          "status": "completed"
        },
        {
          "restaurantname": "Super Cafe - Meal",
          "placedtime": "2019-08-01T08:05:29.18Z",
          "totalprice": 10,
          "restaurant": "5d0fb4173516ac51d7498f63",
          "count": 1,
          "price_includes_tax": false,
          "features": [],
          "baseprice": 10,
          "menuitemname": "Boil Egg ",
          "restaurantcustomer": "5cff6064b67f1072d4e85b46",
          "customeitems": [],
          "restaurantmenuitem": "5d1b2cdf5bfcca209234bb10",
          "itemprice": 10,
          "acceptedtime": "2019-08-01T08:05:33.059Z",
          "id": "b68705d7-2456-48f2-9a65-2b95ef174373",
          "taxAmount": 0,
          "status": "completed"
        }
      ],
      "itemscount": 2,
      "mealvoucherapplicable": true,
      "merchant_price": 50,
      "orderid": "ORD10273334",
      "orderstatus": "completed",
      "ordertype": "dine-in",
      "paid": true,
      "paidamount": 50,
      "paidtime": "2019-08-01T08:05:29.154Z",
      "parentId": "5d0fb4173516ac51d7498f63",
      "paymentgateway": "paytm",
      "paymentstatus": "complete",
      "paymenttransactions": [
        {
          "paidamount": 50,
          "paymentgateway": "paytm",
          "paid": true,
          "externaltransactionid": "20190801111212800110168904380924952",
          "paidtime": "2019-08-01T08:05:29.155Z",
          "paymenttype": "paytm-cash",
          "transactionid": "5d429d421b45fa1ad8e2cf78"
        }
      ],
      "paymenttype": "paytm-cash",
      "placedtime": "2019-08-01T08:05:29.18Z",
      "prepaid": true,
      "promotion": null,
      "refundamounttocustomer": 0,
      "restaurantcustomer": "5cff6064b67f1072d4e85b46",
      "restaurantname": "Super Cafe - Meal",
      "taxamount": 0,
      "taxitems": [],
      "tokenno": "O018",
      "totalprice": 50,
      "transaction": "5d429d421b45fa1ad8e2cf78",
      "transactions": [],
      "acceptedtime": "2019-08-01T08:05:33.059Z",
      "acceptanceWaitedDuration": "-PT3.879S",
      "closedtime": "2019-08-01T08:15:55.505Z"
    },
    "entityId": "5825d66df009c4bc37fd7bae",
    "lastupdatedby": "5d0fb92030928751d16d8e04",
    "updatedAt": "2019-08-01T08:15:56.307Z",
    "id": "5d429d4a6729d40a121cc29d"
  }
}

In the first order, you can see data.items array has only one item i.e Boil Egg and the count is 2, whereas in second-order you can see data.items array has two items one is Boil Egg with count 1 and another is Dal Rice with count 1.

So I am querying elastic search to just return me the count & total price of Boil Egg irrespective of how many items we have in the array.

So the expected output is :

{
          "key" : "Boil Egg ",
          "doc_count" : 2,
          "totalprice" : {
            "value" : 30.0
          },
          "itemscount" : {
            "value" : 3.0
          }
        }

Actual Output is:

{
          "key" : "Boil Egg ",
          "doc_count" : 2,
          "totalprice" : {
            "value" : 70.0
          },
          "itemscount" : {
            "value" : 4.0
          }
        }

So what is happening, I am bucketing the menu item but while aggregating it is considering the count and total price of second item i.e Dal Rice in second-order details.

This should not be the case. I have tried many things searched on internet but was not able to get the proper solution for this.

I am not sure whether this is a bug or there is some feature in ES that I am missing over here.

[dliappis]

Thanks very much for your interest in Elasticsearch and spending the time writing a well formatted question.

This appears to be a user question, and we'd like to direct these kinds of things to the forums. If you can stop by there, we'd appreciate it. This allows us to use GitHub for verified bug reports, feature requests, and pull requests.

There's an active community in the forums that should be able to help get an answer to your question. As such, I hope you don't mind that I close this.