MongoDB aggregation lookup, push output of both collections into sub objects

user_3109248 0
Viewed 205

https://mongoplayground.net/p/xQp-y1iXUtZ

There are 2 collections:

  1. profile
  2. subs

After a lookup, the foreign collection is returned as an array. Since there can be only one element in that array (a sub can only have one profile), we take the first one.

Now, I would like to "push" the "subs" collection into an object as well. There are a lot of fields in "subs".

This is what I have:

[
  {
    "PROFILE0": {
      "_id": "1",
      "name": "gk"
    },
    "_id": "1",
    "f1": "f1",
    "f2": "f1",
    "f3": "f1",
    "f4": "f1",
    "username": "gk"
  },
  {
    "PROFILE0": {
      "_id": "1",
      "name": "gk"
    },
    "_id": "2",
    "f1": "f1",
    "f2": "f3",
    "f3": "f4",
    "f4": "f5",
    "username": "gk"
  }
]

This is what I am looking for:

[
  {
    "PROFILE0": {
      "_id": "1",
      "name": "gk"
    },
    "SUBS": {
      "_id": "1",
      "f1": "f1",
      "f2": "f1",
      "f3": "f1",
      "f4": "f1",
      "username": "gk"
    }
  },
  {
    "PROFILE0": {
      "_id": "1",
      "name": "gk"
    },
    "SUBS": {
      "_id": "2",
      "f1": "f1",
      "f2": "f3",
      "f3": "f4",
      "f4": "f5",
      "username": "gk"
    }

  }
]

Essentially the contents of the "local" collection also as an object.

aggregation-framework mongodb
1 Answers

You add projection before lookup,

  • $project to create a field SUBS and set $$ROOT as the value that is the root document
  • $lookup to join profile collection, pass SUBS.username as localField, and set PROFILE0 as as value, and we don't need $unset stage
  • $set same as you did
db.subs.aggregate([
  {
    $project: {
      _id: 0,
      SUBS: "$$ROOT"
    }
  },
  {
    $lookup: {
      from: "profile",
      localField: "SUBS.username",
      foreignField: "name",
      as: "PROFILE0"
    }
  },
  {
    $set: {
      "PROFILE0": { $arrayElemAt: ["$PROFILE0", 0] }
    }
  }
])

Playground

user_89871280
Related