3

I have two collections:

user:

{
    "_id" : "9efb42e5-514d-44bd-a4b8-6f74e6313ec2",
    "name" : "Haralt",
    "age" : 21,
    "bloodlineId" : "c59a2d02-f304-49a8-a52a-44018fc15fe6",
    "villageId" : "foovillage"
}

bloodlines:

{
    "_id" : "c59a2d02-f304-49a8-a52a-44018fc15fe6",
    "name" : "Tevla",
    "legacy" : 0
}

Now I'd like to do an aggregate to replace user.bloodlineId with the whole bloodline document. This is what I tried to far:

db.getCollection('character').aggregate([
{
    "$match": { _id: "9efb42e5-514d-44bd-a4b8-6f74e6313ec2" }
},
{
    "$lookup": {
        from: "bloodline",
        localField: "bloodlineId",
        foreignField: "_id",
        as: "bloodline"
    }
}])

The result is almost where I want it:

{
    "_id" : "9efb42e5-514d-44bd-a4b8-6f74e6313ec2",
    "name" : "Haralt",
    "age" : 21,
    "bloodlineId" : "c59a2d02-f304-49a8-a52a-44018fc15fe6",
    "villageId" : "foovillage",
    "bloodline" : [ 
        {
            "_id" : "c59a2d02-f304-49a8-a52a-44018fc15fe6",
            "name" : "Tevla",
            "legacy" : 0
        }
    ]
}

Only two issues here. The first is that bloodlineId is still there and bloodline was just added to the result. I'd like to have bloodline replace the bloodlineId attribute.

The second problem is that bloodline is an array. I'd love to have it a single object.

halfer
  • 19,824
  • 17
  • 99
  • 186
xetra11
  • 7,671
  • 14
  • 84
  • 159

1 Answers1

2

I think this pipeline might do the trick:

[
  {
    "$match": {
      _id: "9efb42e5-514d-44bd-a4b8-6f74e6313ec2"
    }
  },
  {
    "$lookup": {
      from: "bloodlines",
      localField: "bloodlineId",
      foreignField: "_id",
      as: "bloodline"
    }
  },
  {
    $project: {
      "age": 1,
      "bloodlineId": {
        $arrayElemAt: [
          "$bloodline",
          0
        ]
      },
      "name": 1,
      "villageId": 1
    }
  }
]

Mongo Playground

If there's anything I'm missing, please let me know!

Rfroes87
  • 668
  • 1
  • 5
  • 15