MongoDB $lookup in an array of object with different value

Question

I've got two MongoDB collections and i want to replace all the ID by image object.

Datset collection :

{ 
  _id: 5d888b3a29cf9a5e93d80756,
  date: 2019-09-23T11:33:00.000Z,
  robot: { _id: 5d9c7caf0ad3ea493210527b, name: 'erx01' },
  images:[ 
    { 
      rgb: 5da57db7cacd6e00c2a0f677,
      ir: 5da57db7cacd6e00c2a0f678,
      lbl: 5da57db7cacd6e00c2a0f676 
    },
    { 
      rgb: 5da5808f65f3440032edee78,
      ir: 5da5808f65f3440032edee79,
      lbl: 5da5808f65f3440032edee77 
    } 
  ] 
}

Image collection :

{ 
  _id: 5da57db7cacd6e00c2a0f677,
  filename: 'iI7gVXyf31b1wedzBXD.png',
  metadata: [Object],
  type: 'RGB' 
}

That's what i tried and got as a result :

{
  $unwind: "$images"
},
{
  $lookup: {
    from: "image",
    localField: "images.rgb",
    foreignField: "_id",
    as: "images.rgb"
  }
},
{
  $lookup: {
    from: "image",
    localField: "images.lbl",
    foreignField: "_id",
    as: "images.lbl"
  }
},
{
  $lookup: {
    from: "image",
    localField: "images.ir",
    foreignField: "_id",
    as: "images.ir"
  }
},

Result :

images: { 
         rgb: [ [Object] ], 
         ir: [ [Object] ], 
         lbl: [ [Object] ] 
        }

What i want :

images : [
           {
             rgb: [Object],
             ir:  [Object] , 
             lbl: [Object] 
           }
           { ... }
         ]

It's half working because i got the right infos but not as a an array. i don't want an array of RGB / IR and LBL image but an array of object containing one single RGB/IR/LBL image.

i tried to use unwind too but i got nothing revelant.

Badri · Accepted Answer · 2019-10-22T14:35:28.143

Well you did half the things correct you just need to add group and project to change the output to your desired format

{
    $unwind: "$images"
  },
  {
    $lookup: {
      from: "image",
      localField: "images.rgb",
      foreignField: "_id",
      as: "images.rgb"
    }
  },
  {
    $unwind: {
        "path": "$images.rgb",
        "preserveNullAndEmptyArrays": true
     }
  },
  {
    $lookup: {
      from: "image",
      localField: "images.lbl",
      foreignField: "_id",
      as: "images.lbl"
    }
  },
  {
   $unwind: {
        "path": "$images.lbl",
        "preserveNullAndEmptyArrays": true
     }
  },
  {
    $lookup: {
      from: "image",
      localField: "images.ir",
      foreignField: "_id",
      as: "images.ir"
    }
  },
  {
    $unwind: {
        "path": "$images.ir",
        "preserveNullAndEmptyArrays": true
     }
  },
  {
    $group: {
      _id: {
        id: '$_id',
      },
      date: { $last: '$date' },
      robot: { $last: '$robot' },
      images: { $push: '$images' }
    }
  },
  {
    $project: {
      _id: '$_id.id',
      date: 1,
      robot: 1,
      images: 1
    }
  }

Notice the $unwind after every lookup it is needed so that the output of lookup is not in array.

Oh man you are my hero , it works ! , not sure what does $project do ? id makes me an additional ID that i doesn't really need. removed it and working fine ! — EyRaG _, Oct 15 '19 at 10:07
$project is used to change the object structure as you need and select keys. Some need it and some don't its your choice. — Badri, Oct 16 '19 at 09:39
I don't know if i can do that here but. if one of my 3 images (rgb/ir/lbl) is null, it doesn't return me anything. How can i fix this ? — EyRaG _, Oct 22 '19 at 12:44
in every unwind add "preserveNullAndEmptyArrays": true to maintain the value even it is null. for ex { $unwind: "$images.rgb", "preserveNullAndEmptyArrays": true, }, — Badri, Oct 22 '19 at 14:30

MongoDB $lookup in an array of object with different value

1 Answers1