mongoDB groupBy Id, color

user_16133360 0
Viewed 104

I have tree records in mongodb but there could be many more, I'm getting shops by an ID coming from frontend

{
    "_id" : ObjectId("6072c2d7ea13fb0338f6cf05"),
    "shopId" : "shop1", <- this is mongodb id
    "shopItems" : [
        {
            _id: ...,
            itemId: 1, // mongodb id
            itemCount: 5,
            colorId: colorId1
        }
        {
            _id: ...,
            itemId: 2, // mongodb id
            itemCount: 3,
            colorId: colorId2
        }
    ]
}

{
    "_id" : ObjectId("6072c2d7ea13fb0338f6cf05"),
    "shopId" : "shop2", <- this is mongodb id
    "shopItems" : [
        {
            _id: ...,
            itemId: 2, // mongodb id
            itemCount: 5,
            colorId: colorId1
        }
        {
            _id: ...,
            itemId: 3, // mongodb id
            itemCount: 3,
            colorId: colorId2
        }
    ]
}

{
    "_id" : ObjectId("6072c2d7ea13fb0338f6cf05"),
    "shopId" : "shop3", <- this is mongodb id
    "shopItems" : [
        {
            _id: ...,
            itemId: 3, // mongodb id
            itemCount: 5,
            colorId: colorId1
        }
        {
            _id: ...,
            itemId: 1, // mongodb id
            itemCount: 3,
            colorId: colorId1
        }
    ]
}

I need to get 20 records and group them by itemId and colorId, and get counts for every shop. the count of shops can be 1,2,3,....10etc..

this is output I need:

+--------+----------+-------+-------+-------+
| itemId | colorId  | shop1 | shop2 | shop3 |
+========+==========+=======+=======+=======+
| 1      | colorId1 | 5     | 0     | 3     |
+--------+----------+-------+-------+-------+
| 2      | colorId2 | 3     | 0     | 0     |
+--------+----------+-------+-------+-------+
| 3      | colorId2 | 0     | 3     | 0     |
+--------+----------+-------+-------+-------+
| 2      | colorId1 | 0     | 5     | 0     |
+--------+----------+-------+-------+-------+
| 3      | colorId1 | 0     | 0     | 5     |
+--------+----------+-------+-------+-------+

my code is:

const stores  = await Store.aggregate([
{ $match: query },

{ $project: { shopId: 1, tt: { $slice: [ "$shopItems", 3 ] } } },
])

I need value 0 if item with itemId and colorId don't exist in shop.

Thank you very much!

mongoose mongodb
1 Answers

You can use the below Aggregation Query.

db.collection.aggregate([
  {
    "$match": {}  // <-- Highly recommend you to use match due to the complexity of this query
  },
  {
    "$unwind": {
      "path": "$shopItems",
    }
  }, 
  {
    "$facet": {
      "shopIds": [
        {
          "$group": {
            "_id": null,
            "shopIds": {
              "$addToSet": "$shopId"
            }
          }
        },
        {
          "$unwind": {
            "path": "$shopIds",
          }
        },
        {
          "$sort": {
            "shopIds": 1
          }
        },
        {
          "$group": {
            "_id": null,
            "shopIds": {"$push": "$shopIds"}
          }
        },
      ],
      "docRoot": [
        {
          "$group": {
            "_id": {
              "itemId": "$shopItems.itemId",
              "colorId": "$shopItems.colorId",
              "shopIds": "$shopId",
            },
            "count": {"$sum": 1}
          }
        },
        {
          "$group": {
            "_id": {
              "itemId": "$_id.itemId",
              "colorId": "$_id.colorId",
            },
            "shopCount": {
              "$push": {
                "shopId": "$_id.shopIds",
                "count": "$count",
              }
            }
          },
        },
      ],
    }
  }, 
  {
    "$unwind": {
      "path": "$docRoot",
    }
  }, 
  {
    "$project": {
      "itemId": "$docRoot._id.itemId",
      "colorId": "$docRoot._id.colorId",
      "shopId": "$complete.shopId",
      "count": "$complete.count",
      "keySwap": {
        "$reduce": {
          "input": {
            "$map": {
              "input": {
                "$map": {
                  "input": {
                    "$concatArrays": [
                      {
                          "$map": {
                          "input": {
                            "$setDifference": [
                              {"$arrayElemAt": ["$shopIds.shopIds", 0]},
                              {
                                "$map": {
                                  "input": "$docRoot.shopCount",
                                  "as": "elem",
                                  "in": "$$elem.shopId"
                                }
                              },
                            ],
                          },
                          "as": "elem",
                          "in": {
                            "shopId": "$$elem",
                            "count": 0
                          },
                        },
                      },
                      "$docRoot.shopCount",
                    ]
                  },
                  "as": "elem",
                  "in": {
                    "$objectToArray": "$$elem"
                  }
                },
              },
              "as": "elem1",
              "in": {
                "$arrayToObject": [
                  [{
                    "k": {"$arrayElemAt": ["$$elem1.v", 0]},
                    "v": {"$arrayElemAt": ["$$elem1.v", 1]},
                  }]
                ],
              },
            },
          },
          "initialValue": {},
          "in": {
            "$mergeObjects": ["$$value", "$$this"]
          }
        },
      },
    }
  }, 
  {
    "$replaceRoot": {
      "newRoot": {
          "$mergeObjects": [
            {"itemId": "$itemId", "colorId": "$colorId"},
            "$keySwap",
          ]
      }
    }
  }
], {
  "allowDiskUse": true
})

Let me know if you want an explanation of each stage.

Mongo Playground Sample Execution

user_82132770
Related