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

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     |
 -------- ---------- ------- ------- ------- 

here is my data and query - here shopId is string and it's work good.

but when I use this query on my local mashine, I'm getting this error:

MongoError: PlanExecutor error during aggregation :: caused by :: $arrayToObject requires an object with keys 'k' and 'v', where the value of 'k' must be of type string. Found type: objectId

but when I change shopId to the ObjectId I'm getting error. ObjectId versoin

CodePudding user response：

Per your request in the comments (if I got it right):

    db.collection.aggregate([
  {
    "$match": {}// <-- Highly recommend you to use match due to the complexity of this query
  },
  {
    $group: {
      _id: 0,
      data: {
        $push: {
          shopId: "$shopId",
          shopItems: "$shopItems"
        }
      },
      shopIds: {
        "$push": {
          shopId: "$shopId",
          "count": 0
        }
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $unwind: "$data.shopItems"
  },
  {
    $group: {
      _id: {
        itemId: "$data.shopItems.itemId",
        colorId: "$data.shopItems.colorId"
      },
      data: {
        $push: {
          shopId: "$data.shopId",
          count: "$data.shopItems.itemCount"
        }
      },
      existing: {
        $push: {
          shopId: "$data.shopId",
          "count": 0
        }
      },
      shopIds: {
        $first: "$shopIds"
      }
    }
  },
  {
    "$addFields": {
      "missing": {
        "$setDifference": [
          "$shopIds",
          "$existing"
        ]
      }
    }
  },
  {
    $project: {
      data: {
        $concatArrays: [
          "$data",
          "$missing"
        ]
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $sort: {
      "data.shopId": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      counts: { // here you can change this key
        $push: "$data"
      },
      totalCount: {
        $sum: "$data.count" // if you want it
      }
    }
  }
])

After the first $match, we $group in order to get all shopIds in each document. Next we $unwind and $group by the group you wanted: by colorId and itemId. Then we are adding all the shops with count 0 and removing the ones that do have actual count. Last three steps are just for sorting, summing and formating. You can play with it here.