How to group a JSON dataset by two properties and sum the value in dataweave?

Question

I have a data set which I am trying to group by two separate keys, but nested. I have tried to use the groupBy method but I am then having a hard time with the sum within the keys.

I have also tries to use the pluck method but that also seems to become more challenging with nested groupBy. Any suggestions would be welcomed. Input:

var sales =  [{
    "price": "500" as Number,
    "store": "123",
    "category": "1"
  },
  {
    "price": "400" as Number,
    "store": "123",
    "category": "2"
  },
  {
    "price": "700" as Number,
    "store": "123",
    "category": "2"
  },
  {
    "price": "800" as Number,
    "store": "456",
    "category": "6"
  },
  {
    "price": "900" as Number,
    "store": "456",
    "category": "7"
  },
  {
    "price": "400" as Number,
    "store": "456",
    "category": "6"
  }
  ]

Output:

 [
      {
        "123": {
            "1": {"price": "500"},
            "2": {"price": "1100"}
        }
        
      },
      {
        "456": {
            "6": {"price": "800"},
            "7": {"price": "1300"}
        }
        
      }
  ]

Answer 1

There may be an easiest way. I don't understand why the prices are stored as strings instead of numbers in both input and output. It would simplify the output at least to use numbers.

%dw 2.0
output application/json
var sales =  [{
    "price": "500" as Number,
    "store": "123",
    "category": "1"
  },
  {
    "price": "400" as Number,
    "store": "123",
    "category": "2"
  },
  {
    "price": "700" as Number,
    "store": "123",
    "category": "2"
  },
  {
    "price": "800" as Number,
    "store": "456",
    "category": "6"
  },
  {
    "price": "900" as Number,
    "store": "456",
    "category": "7"
  },
  {
    "price": "400" as Number,
    "store": "456",
    "category": "6"
  }
]
fun sumCategories(a)=
    a groupBy ($.category)
    mapObject ((value, key, index) -> {
        (key): 
            value reduce ((item, accumulator={ price: 0}) -> {price: item.price + accumulator.price} ) 
            mapObject {price: $ as String} // assumes single attribute
    })
---
sales 
    groupBy ($.store)
    mapObject ((value, key, index) -> (key): sumCategories(value))
    pluck {($$):$}

Output:

[
  {
    "123": {
      "1": {
        "price": "500"
      },
      "2": {
        "price": "1100"
      }
    }
  },
  {
    "456": {
      "6": {
        "price": "1200"
      },
      "7": {
        "price": "900"
      }
    }
  }
]