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.

CodePudding user response：

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