Question 401402 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongo DB Aggregate => Lookup => combine lookup data property into root property array

Lot’s a questions on Stack Overflow regarding Mongo DB, Aggregate, and lookup. However, I could not find direct answers to the issue I am struggling with.

Suppose we have two collections: Support and Images. The Support document has a property called instructions that is an array of objects, which each contain an image property that is the string value of the images collection id. During the aggregation, the support document is found during match (returning one support document), then I need the instructions array objects to each get their "image" property replaced with the found images from the lookup, and ultimately return the root support object with only the instrucitons.image property changed.

No new properties added, only use the instructions.image tag from each object in the instructions array to get the correct images, then replace those strings with the actual image value from the images array.

  1. How is this done?
  2. Is there any way to get access to the "localfield" in the lookup without using "let" if I was to use a lookup pipeline?
  3. Could this all be done inside of a lookup pipeline?
  4. What is the most efficient way of doing this?
    DB.support = [
    {
        _id: ObjectId("12345"),
        title: "Support",
        instructions: [
            {
                image: "image-id-string-one"
            },
            {
                image: "image-id-string-two"
            },
            {
                image: "image-id-string-three"
            },
        ]
    }]

    DB.images = [
    {
        _id: ObjectId("11111111"),
        id:"image-id-string-one",
        image: "actual-image-one"
    },
    {
        _id: ObjectId("222222222"),
        id:"image-id-string-two",
        image: "actual-image-two"
    },
    {
        _id: ObjectId("333333333"),
        id:"image-id-string-three",
        image: "actual-image-three"
    }]

    db.collection('support').aggregate([
    {
        "$match": {
            "title": "Support"
        }
    },
    // Let's say we match on title and expect only one record from above match
    {
        "$unwind": "$instructions"
    },
    {
        "$lookup": {
            "from": "images",
            "localField": "instructions.image",
            "foreignField": "id",
            "as": "images"
        }
    },
    {
        "$unwind": "$images"
    },
    {
        "$set": {
            "instructions.image": "$images.image"
        }
    },
    // Remove images array artifact from lookup from object.
    {
        "$unset": ["images"]
    }])

    // So that the return object would look like this

    {
    _id: ObjectId("12345"),
    title: "Support",
    instructions: [
        {
            image: "actual-image-one"
        },
        {
            image: "actual-image-two"
        },
        {
            image: "actual-image-three"
        },
    ]}


// NOTE: I have tried so many ways to try to get to the above object but nothing.
Tags:

2

Answers


  1. 0

    You were almost there.

    The pipeline you show would result in a separate support document for each instruction, with the image field already replaced with the desired value.

    From that point you would need to:

    • group the documents, saving a copy of the overall document, and pushing the instructions back into an array
    • replace each document with a the copy of the original, but with the instructions field overwritten with the newly assembled array.

    This might look like:

    {$group: {
      _id: "$_id",
      doc: {$first: "$$ROOT"},
      instructions: {$push: "$instructions"}
}},
{$replaceRoot: {
      newRoot: {"$mergeObjects": ["$doc",{instructions: "$instructions"}]}
}}

    Playground

    Result from adding those 2 stages:

    [
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "instructions": [
      {"image": "actual-image-one"      },
      {"image": "actual-image-two"},
      {"image": "actual-image-three"}
    ],
    "title": "Support"
  }
]
    Login or Signup to reply.
  2. 0

    A lookup and a project would be sufficient

    1. Approach – assuming that there are many fields in a single image document and you just want to extract the required fields use $map so that you don’t have to manually specify all the fields that are not needed.
    db.support.aggregate([
  {
    $match: { title: "Support" }
  },
  {
    $lookup: {
      from: "images",
      localField: "instructions.image",
      foreignField: "id",
      as: "instructions"
    }
  },
  {
    $addFields: {
      instructions: {
        $map: {
          input: "$instructions",
          as: "instruction",
          in: { image: "$$instruction.image" }
        }
      }
    }
  }
])

    playground

    1. Approach – if you don’t mind manually defining all the fields that you want to exclude/include
    db.support.aggregate([
  {
    $match: { title: "Support" }
  },
  {
    $lookup: {
      from: "images",
      localField: "instructions.image",
      foreignField: "id",
      as: "instructions"
    }
  },
  {
    $project: {
      title: 1,
      "instructions.image": 1
    }
  }
])

    playground

    or using $unset

    db.support.aggregate([
  {
    $match: { title: "Support" }
  },
  {
    $lookup: {
      from: "images",
      localField: "instructions.image",
      foreignField: "id",
      as: "instructions"
    }
  },
  {
    $unset: [ "instructions.id", "instructions._id" ]
  }
])

    playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search