how to use $project return nest array after $lookup in mongodb

Question

I have two collections in MongoDB and I like to use $lookup to mapping two collections and return specific values.

Job collection

{
    "_id": ObjectId("5b0d2b2c7ac4792df69a9942"),
    "title": "software engineer",
    "categories" : [
        ObjectId("5b0d16ee7ac4792df69a9924"), 
        ObjectId("5b0d47667ac4792df69a9994")
    ],
    "deadline": 2021-05-03T06:29:54.634+00:00
}

job_categories collection：

{
    "_id": ObjectId(5b0d16ee7ac4792df69a9924),
    "name": "front-end"
}
{
    "_id": ObjectId(5b0d47667ac4792df69a9994),
    "name": "full-stack"
}

The objectidin job collection categories array matches the _id of job_categories. how to use $lookup and $project to return the following result.

Expected result:

{
    "_id": ObjectId("5b0d2b2c7ac4792df69a9942"),
    "title": "software engineer",
    "categories" : [
        ObjectId("5b0d16ee7ac4792df69a9924"), 
        ObjectId("5b0d47667ac4792df69a9994")
    ],
    "deadline": 2021-05-03T06:29:54.634+00:00,
    "categories_list": [
        "front-end",
        "full-stack"
    ]
}

The expected result adds a new filed categories list and the array value reference job_categories collection's namekey value.

ray · Accepted Answer · 2024-04-13 17:53:39Z

1

Just perform the $lookup directly. Then $project the field $categories_list.name as categories_list.

db.job.aggregate([
  {
    "$lookup": {
      "from": "job_categories",
      "localField": "categories",
      "foreignField": "_id",
      "as": "categories_list"
    }
  },
  {
    "$project": {
      "title": 1,
      "categories": 1,
      "deadline": 1,
      "categories_list": "$categories_list.name"
    }
  }
])

Here is the Mongo playground for your reference.

edited Apr 13, 2024 at 17:53

answered Jul 28, 2021 at 5:29

ray

15.6k12 gold badges97 silver badges105 bronze badges

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

Collectives™ on Stack Overflow

how to use $project return nest array after $lookup in mongodb

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related