MongoDB Group by String Field inside of an Array

Question

I did a lot of research on MongoDB aggregation grouping, but couldn't find a solution.

I have the following document structure:

{
  "_id":"6053e0ef22b8e60015a23da8",
  "shoppinglist":[
     {
        "_id":"606ae2e34e4416001538104f",
        "items":[
           {
              "_id":"6071c5ed8f669f0015e6eebe",
              "product_id":"605852c28ea29f0015653d6f",
           },
           ...
        ]
     }
}

My goal is to group the items in each shopping list object using the product_id, so that my result looks like this:

{
  "_id":"6053e0ef22b8e60015a23da8",
  "shoppinglist":[
     {
        "_id":"606ae2e34e4416001538104f",
        "items":[
           {
              "_id":"6071c5ed8f669f0015e6eebe",
              "product_id":"605852c28ea29f0015653d6f",
              "count": 3 //3 items with the product_id = 605852c28ea29f0015653d6f
           },
           ...
        ]
     }
}

Can someone help me with this, I'm desperate.

Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask — turivishal
– turivishal, Commented May 6, 2021 at 10:29

turivishal · Accepted Answer · 2021-04-30 11:14:14Z

$unwind deconstruct shoppinglist array
$unwind deconstruct shoppinglist.items array
$group by _id and product_id, and get required fields using $first and get count using $sum
$group by _id and shoppinglist._id and reconstruct array of items
$group by _id and reconstruct array of shoppinglist

db.collection.aggregate([
  { $unwind: "$shoppinglist" },
  { $unwind: "$shoppinglist.items" },
  {
    $group: {
      _id: {
        _id: "$_id",
        product_id: "$shoppinglist.items.product_id"
      },
      shoppinglist_id: { $first: "$shoppinglist._id" },
      items_id: { $first: "$shoppinglist.items._id" },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: {
        _id: "$_id._id",
        shoppinglist_id: "$shoppinglist_id"
      },
      items: {
        $push: {
          items_id: "$items_id",
          product_id: "$_id.product_id",
          count: "$count"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      shoppinglist: {
        $push: {
          _id: "$_id.shoppinglist_id",
          items: "$items"
        }
      }
    }
  }
])

Playground

Collectives™ on Stack Overflow

MongoDB Group by String Field inside of an Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related