Sum (aggregation) of elements in arrays of objects

[Alfredo_Sone_Scassi (Alfredo Sone Scassi-Buffa)]

Hi,

I am starting to use ElasticSearch + Kibana to get analytics and store transactions of a company.

I store transaction details on one array of objects, each object contains a line: the total information about every SKU of that purchase (See above for details).

For example, if I buy 3 water bottles and one coffe. I will get an array of two objects, one object of water bottles (containing SKU id, quantity of bottles, unit price of the bottle, and total price of all three bottles).

I wanted to get information about purchases details, for example:

1. Supposing I do not have transactionTotalAmount on my object "headers", how can I sum all of the lines of transactions to get the total amount of the purchase?.

2. How could I retrieve how many SKU of type "154" I have sold?

Kibana is not working because "objects of arrays are not well supported", and every sum or aggregation returns 0.

My data stucture looks like this:

{

"transactionId": "uuid",
"siteId": "site9129",
"transactionUnixDate": 1580934533,
"transactionTotalAmount": 1000,
"transactionDetails": [

{"line": 1,
"sku": "154",
"quantity": 3,
"unitPrice": 300,
"linePrice": 900},

{"line": 2,
"sku": "235",
"quantity": 1,
"unitPrice": 100,
"linePrice": 100}

]
}

And my map like this:

"mappings" : {
"properties" : {

    "transactionId" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },

    "siteId" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },

"transactionUnixDate" : {
"type" : "long"
},

"transactionDetails":{
"type" : "nested",

          "properties" : {
            "line" : {
              "type" : "integer"
            },
            "quantity" : {
              "type" : "float"
            },
          "unitPrice": {
            "line" : {
              "type" : "integer"
            }

}.