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

Question

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.

varman · Accepted Answer · 2020-12-04 14:44:57Z

1

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

edited Dec 4, 2020 at 14:44

answered Dec 4, 2020 at 14:37

varman

8,9226 gold badges25 silver badges56 bronze badges

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

2 Comments

potato Over a year ago

thanks! Can I remove the array of "driver" and just show the driverName like my question? Because it is one to one relationship between driver and car.

potato Over a year ago

ok, I solved : mongoplayground.net/p/LOActtDh4Bg

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related