How to sum up two different nested lists by date (Mongodb aggregation)-CodePudding

I have a list of objects each one, with two lists, as following :

[
  {
    "name": "market2",
    "materialCosts": [
      {
        "date": "2022-02-12",
        "amount": "1234.11"
      },
      {
        "date": "2022-02-15",
        "amount": "1234.11"
      }
    ],
    "laborCosts": [
      {
        "date": "2022-02-12",
        "amount": "12.11"
      },
      {
        "date": "2022-02-15",
        "amount": "45.11"
      }
    ]
  },
  {
    "name": "market1",
    "materialCosts": [
      {
        "date": "2022-02-12",
        "amount": "1234.11"
      },
      {
        "date": "2022-02-15",
        "amount": "1234.11"
      }
    ],
    "laborCosts": [
      {
        "date": "2022-02-12",
        "amount": "12.11"
      },
      {
        "date": "2022-02-15",
        "amount": "45.11"
      }
    ]
  }
]

is there any possibility to group all costs by date using mongodb aggregation framework, no matter if they are material or labor costs, to finally have the following result :

[
  {
    "date": "2022-02-12",
    "amount": "1234.11"
  },
  {
    "date": "2022-02-15",
    "amount": "1234.11"
  }
]

CodePudding user response：

Query

concat the two arrays
unwind
group by date and sum

Playmongo

aggregate(
[{"$project": 
   {"costs": {"$concatArrays": ["$materialCosts", "$laborCosts"]}}},
 {"$unwind": "$costs"},
 {"$group": 
   {"_id": "$costs.date", "sum": {"$sum": {"$toDouble": "$costs.amount"}}}}])