CODEWITHSUNDEEP
mongodbaggregation-framework
potato
potato

Reputation: 223

How to group the same fields and project as array in MongoDB aggregation

Mongodb Playground

I joined with other two collections and want to group this by category.

And I want to project like this:

[
  {
    "_id": "101",
    "cars": [
      {
        "carName": "CyberTruck",
        "driverName": "Tom",
        "views": 50
      },
      {
        "carName": "Model 3",
        "driverName": "Tom",
        "views": 40
      },
      {
        "carName": "Model X",
        "driverName": "Tom",
        "views": 30
      }
    ],
    "categoryName": "Tesla"
  },
  {
    "_id": "102",
    "cars": [
      {
        "carName": "X1",
        "driverName": "Mary",
        "views": 20
      }
    ],
    "categoryName": "BMW"
  }
]

In addition, I want to sort by views from biggest to smallest and project the only first 3 elements for each category.

and this is what I tried before: https://mongoplayground.net/p/FPCU4aV9a9X

However, I don't know how to join with driver collection to match driver name with driverId in it.

Upvotes: 1

Views: 613

Answers (1)

varman
varman

Reputation: 8894

With your script, we can do another lookup like following.

{
    "$unwind": "$cars"
  },
  {
    $lookup: {
      from: "driver",
      localField: "cars.driverId",
      foreignField: "_id",
      as: "drivers"
    }
  },
  {
    $addFields: {
      drivers: "$$REMOVE",
      "cars.drivers": "$drivers"
    }
  },
  {
    "$group": {
      _id: "$_id",
      categoryName: {
        $first: "$categoryName"
      },
      cars: {
        $push: "$cars"
      }
    }
  }

Working Mongo playground

But This might me the easiest way. Since you understand the Standard lookup, there is another type Join Conditions and Uncorrelated Sub-queries. Here you can parallelly use stages in the joining collection.

The script is

db.category.aggregate([
  {
    $lookup: {
      from: "car",
      let: {
        carId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$carId",
                "$categoryId"
              ]
            }
          }
        },
        {
          $sort: {
            "views": -1
          }
        },
        {
          $limit: 3
        },
        {
          "$lookup": {
            "from": "driver",
            "localField": "driverId",
            "foreignField": "_id",
            "as": "drivers"
          }
        }
      ],
      as: "join"
    }
  }
])

Working Mongo playground

Upvotes: 1

Related Questions