MongoDb aggregation with arrays inside an array possible

Question

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"

}

Jiří Pospíšil · Accepted Answer · 2020-06-08 13:18:31Z

4

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

answered Jun 8, 2020 at 13:18

Jiří Pospíšil

14.4k2 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CSharp Over a year ago

Thankyou so much This is such a helpful answer. I think I can use this example as a starting point for other queries I need too. The $unwind seems a really good solution and the aggregate example is much more concise than I'd hoped. Many thanks.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 8, 2020 at 14:37

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

1 Comment

CSharp Over a year ago

Thanks for this alternative. This is really helpful for helping me see a different approcah to and I'll be able to use these ideas and approach too in building further queries. MAny thanks

Collectives™ on Stack Overflow

MongoDb aggregation with arrays inside an array possible

2 Answers 2

1 Comment

Alternative solution...

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Alternative solution...

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related