Agregate two items via $lookup on the same collection via Pymongo-CodePudding

I have the data below under the same collection "schema", here's a sample of my status:

{
    "_id": "c19592b6-8110-4684-9c96-f133e08d973e",
    "display": "WAITING",
    "name": "waiting",
    "color": "#ccf0d5",
    "type": "status"
},

{
    "_id": "69e39c0c-b722-433a-aa6e-471c24211a4d",
    "display": "IN PROGRESS",
    "name": "in_progress",
    "color": "#4ddb7f",
    "type": "status"
},

{
    "_id": "59336051-776e-4f02-b988-46b3260f097c",
    "display": "APPROVED",
    "name": "approved",
    "color": "#738ace",
    "type": "status"
}

And here is an example of my states:

{
    "_id": "e351ed21-0db3-44aa-ae0c-d7b0b92b4dce",
    "index": 1,
    "display": "In Progress",
    "name": "in_progress",
    "type": "state",
    "members": ["c19592b6-8110-4684-9c96-f133e08d973e", "69e39c0c-b722-433a-aa6e-471c24211a4d"]
},

{
    "_id": "2b57768b-919e-4cba-a861-a682eeac0cd2",
    "index": 2,
    "display": "In Review",
    "name": "in_review",
    "type": "state",
    "members": ["59336051-776e-4f02-b988-46b3260f097c"]
}

And here is what I would like to get at the end:

{
        "_id": "e351ed21-0db3-44aa-ae0c-d7b0b92b4dce",
        "index": 1,
        "display": "In Progress",
        "name": "in_progress",
        "type": "state",
        "members": [{
            "_id": "c19592b6-8110-4684-9c96-f133e08d973e",
            "display": "WAITING",
            "name": "waiting",
            "color": "#ccf0d5",
            "type": "status"
        },{
            "_id": "69e39c0c-b722-433a-aa6e-471c24211a4d",
            "display": "IN PROGRESS",
            "name": "in_progress",
            "color": "#4ddb7f",
            "type": "status"
        }]
},

I've looked at other questions like these:

$MongoDB $lookup foreach element in array on the same collection

$lookup with two fields with same collection

But each one is a slightly different case, I can't honestly wrap my head around $lookup. Thanks for the help.

CodePudding user response：

db.c2.aggregate([
  {
    $match: {
      _id: "e351ed21-0db3-44aa-ae0c-d7b0b92b4dce"
    }
  },
  {
    $unwind: "$members"
  },
  {
    $lookup: {
      from: "c1",
      localField: "members",
      foreignField: "_id",
      as: "members"
    }
  },
  {
    $group: {
      _id: "$_id",
      index: { $first: "$index" },
      display: { $first: "$display" },
      name: { $first: "$name" },
      type: { $first: "$type" },
      members: { $push: "$members" }
    }
  }
])

mongoplayground

db.schema.aggregate([
  {
    $match: { type: "state" }
  },
  {
    $unwind: "$members"
  },
  {
    $lookup: {
      from: "schema",
      localField: "members",
      foreignField: "_id",
      as: "members"
    }
  },
  {
    $group: {
      _id: "$_id",
      index: { $first: "$index" },
      name: { $first: "$name" },
      display: { $first: "$display" },
      type: { $first: "$type" },
      members: { $push: { $first: "$members" } }
    }
  }
])

mongoplayground