MongoDb aggregation with arrays inside an array possible

user_1623720 0
Viewed 794

I am struggling to find some examples of using the mongo aggregation framework to process documents which has an array of items where each item also has an array of other obejects (array containing an array)

In the example document below what I would really like is an example that sums the itemValue in the results array of all cases in the document and accross the collection where the result.decision was 'accepted'and group by the document locationCode

However, even an example that found all documents where the result.decision was 'accepted' to show or that summmed the itemValue for the same would help

Many thanks

{
"_id": "333212",
"data": {
    "locationCode": "UK-555-5566",
    "mode": "retail",
    "caseHandler": "A N Other",
    "cases": [{
            "caseId": "CSE525666",
            "items": [{
                    "id": "333212-CSE525666-1",
                    "type": "hardware",
                    "subType": "print cartridge",
                    "targetDate": "2020-06-15",
                    "itemDetail": {
                        "description": "acme print cartridge",
                        "quantity": 2,
                        "weight": "1.5"
                    },
                    "result": {
                        "decision": "rejected",
                        "decisionDate": "2019-02-02"
                    },
                    "isPriority": true
                },
                {
                    "id": "333212-CSE525666-2",
                    "type": "Stationery",
                    "subType": "other",
                    "targetDate": "2020-06-15",
                    "itemDetail": {
                        "description": "staples box",
                        "quantity": 3,
                        "weight": "1.66"
                    },
                    "result": {
                        "decision": "accepted",
                        "decisionDate": "2020-03-03",
                        "itemValue": "23.01"
                    },
                    "isPriority": true
                }
            ]
        },
        {
            "caseId": "CSE885655",
            "items": [{
                    "id": "333212-CSE885655-1",
                    "type": "marine goods",
                    "subType": "fish food",
                    "targetDate": "2020-06-04",
                    "itemDetail": {
                        "description": "fish bait",
                        "quantity": 5,
                        "weight": "0.65"
                    },
                    "result": {
                        "decision": "accepted",
                        "decisionDate": "2020-03-02"
                    },
                    "isPriority": false
                },
                {
                    "id": "333212-CSE885655-4",
                    "type": "tobacco products",
                    "subType": "cigarettes",
                    "deadlineDate": "2020-06-15",
                    "itemDetail": {
                        "description": "rolling tobbaco",
                        "quantity": 42,
                        "weight": "2.25"
                    },
                    "result": {
                        "decision": "accepted",
                        "decisionDate": "2020-02-02",
                        "itemValue": "48.15"
                    },
                    "isPriority": true
                }
            ]
        }
    ]
},
"state": "open"

}

aggregation-framework mongodb
2 Answers

You're probably looking for $unwind. It takes an array within a document and creates a separate document for each array member.

{ foos: [1, 2] } -> { foos: 1 }, { foos: 2}

With that you can create a flat document structure and match & group as normal.

db.collection.aggregate([
  {
    $unwind: "$data.cases"
  },
  {
    $unwind: "$data.cases.items"
  },
  {
    $match: {
      "data.cases.items.result.decision": "accepted"
    }
  },
  {
    $group: {
      _id: "$data.locationCode",
      value: {
        $sum: {
          $toDecimal: "$data.cases.items.result.itemValue"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      locationCode: "$_id",
      value: "$value"
    }
  }
])

https://mongoplayground.net/p/Xr2WfFyPZS3

Ji237_Posp237il_190930

Alternative solution...

We group by data.locationCode and sum all items with this condition:
cases[*].items[*].result.decision" == "accepted"

db.collection.aggregate([
  {
    $group: {
      _id: "$data.locationCode",
      itemValue: {
        $sum: {
          $reduce: {
            input: "$data.cases",
            initialValue: 0,
            in: {
              $sum: {
                $concatArrays: [
                  [ "$$value" ],
                  {
                    $map: {
                      input: {
                        $filter: {
                          input: "$$this.items",
                          as: "f",
                          cond: {
                            $eq: [ "$$f.result.decision", "accepted" ]
                          }
                        }
                      },
                      as: "item",
                      in: {
                        $toDouble: {
                          $ifNull: [ "$$item.result.itemValue", 0 ]
                        }
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground

user_37104900
Related