1

The code example is in Mongo Playground

https://mongoplayground.net/p/W4Qt4oX0ZRP

Assume the following documents

[
  {
    _id: "5df1e6f75de2b22f8e6c30e8",
    user: {
      name: "Tom",
      sex: 1,
      age: 23
    },
    dream: [
      {
        label: "engineer",
        industry: "5e06b16fb0670d7538222909",
        type: "5e06b16fb0670d7538222951",

      },
      {
        label: "programmer",
        industry: "5e06b16fb0670d7538222909",
        type: "5e06b16fb0670d7538222951",

      }
    ],
    works: [
      {
        name: "any engineer",
        company: "5dd7fd51b0ae1837a08d00c8",
        skill: [
          "5dc3998e2cf66bad16efd61b",
          "5dc3998e2cf66bad16efd61e"
        ],

      },
      {
        name: "any programmer",
        company: "5dd7fd9db0ae1837a08d00e2",
        skill: [
          "5dd509e05de2b22f8e67e1b7",
          "5dd509e05de2b22f8e67e1bb"
        ],

      }
    ]
  }
]

I tried to use aggregate $lookup $unwind

db.coll.aggregate([
  {
    $unwind: {
      path: "$dream",

    }
  },
  {
    $lookup: {
      from: "industry",
      localField: "dream.industry",
      foreignField: "_id",
      as: "dream.industry"
    },

  },
  {
    $unwind: {
      path: "$dream.industry",

    }
  },
  {
    $lookup: {
      from: "type",
      localField: "dream.type",
      foreignField: "_id",
      as: "dream.type"
    },

  },
  {
    $unwind: {
      path: "$dream.type",

    }
  },
  {
    $unwind: {
      path: "$works",

    }
  },
  {
    $lookup: {
      from: "company",
      localField: "works.company",
      foreignField: "_id",
      as: "works.company"
    },

  },
  {
    $unwind: {
      path: "$works.company",

    }
  },
  {
    $lookup: {
      from: "skill",
      localField: "works.skill",
      foreignField: "_id",
      as: "works.skill"
    },

  },

])

Executing the above code did not get the desired result!

This is what i expect

{
  _id: "5df1e6f75de2b22f8e6c30e8",
  user: {
    name: 'Tom',
    sex: 1,
    age: 23
  },
  dream: [
    {
      label: 'engineer',
      industry: {
        _id: "5e06b16fb0670d7538222909",           // Industry doc _id
        name: 'IT',
        createdAt: "2019-12-28T01:35:44.070Z",
        updatedAt: "2019-12-28T01:35:44.070Z"
      },
      type: {
        _id: "5e06b16fb0670d7538222951",           // Type doc _id
        name: 'job',
        createdAt: "2019-12-28T01:35:44.070Z",
        updatedAt: "2019-12-28T01:35:44.070Z"
      },
    },
    {
      label: 'programmer',
      industry: {
        _id: "5e06b16fb0670d7538222909",           // Industry doc _id
        name: 'IT',
        createdAt: "2019-12-28T01:35:44.070Z",
        updatedAt: "2019-12-28T01:35:44.070Z"
      },
      type: {
        _id: "5e06b16fb0670d7538222951",           // Type doc _id
        name: 'job',
        createdAt: "2019-12-28T01:35:44.070Z",
        updatedAt: "2019-12-28T01:35:44.070Z"
      }
    }
  ],
  works: [
    {
      name: 'any engineer',
      company: {
        _id: "5dd7fd51b0ae1837a08d00c8",          // Company doc _id
        name: 'alibaba',
        area: 'CN',
      },
      skill: [
        { 
          _id: "5dc3998e2cf66bad16efd61b",        // Skill doc _id
          name: 'Java' 
        }, 
        { 
          _id: "5dc3998e2cf66bad16efd61e",        // Skill doc _id
          name: 'Php' 
        }, 
      ]
    },
    {
      name: 'any programmer',
      company: {
        _id: "5dd7fd9db0ae1837a08d00e2",           // Company doc _id
        name: 'microsoft',
        area: 'EN',
      },
      skill: [
        { 
          _id: "5dd509e05de2b22f8e67e1b7",           // Skill doc _id
          name: 'Golang' 
        },
        { 
          _id: "5dd509e05de2b22f8e67e1bb",         // Skill doc _id
          name: 'Node.js'
        }
      ]
    },
  ]
}

The expected result is dream is an array, works is an array, and dream.industry changed from ObjectId to document, dream.type changed from ObjectId to document, works.company changed from ObjectId to document

When I use populate, I can do it easily

Model.find()
 .populate('dream.industry')
 .populate('dream.type')
 .populate('works.company')
 .populate('works.skill')
 .lean()

I refer to the following questions

  1. mongoose aggregate lookup array (Almost the same as my question, But not resolved)
  2. $lookup on ObjectId's in an array

hope to get everyone's help, thank you!

nline BGO
  • 59
  • 6
  • You need to give real ObjectId values in the sample document to be easier for us. Even better it would be nice to create a sample in https://mongoplayground.net – SuleymanSah Dec 28 '19 at 17:53
  • what is the result of your current code? Notice that MongoDB object ids must save as ObjectId, for example: industry: ObjectId("5e06b16fb0670d7538222909"), if you save mongo ids as string, the lookup stage will not work. – Amin Shojaei Dec 29 '19 at 07:40
  • @Amin Shojaei, Thank you! The result of the current code is in https://mongoplayground.net/p/W4Qt4oX0ZRP – nline BGO Dec 29 '19 at 07:45
  • Honestly, i tried to understand how the current result and your expected result are different, but i couldn't figure it out. – Amin Shojaei Dec 29 '19 at 08:13
  • The expected result is ```dream``` is an array, ```works``` is an array, and ```dream.industry``` changed from ObjectId to document, ```dream.type``` changed from ObjectId to document, ```works.company``` changed from ObjectId to document – nline BGO Dec 29 '19 at 08:28

1 Answers1

0

To make it easier i would not change the current pipeline but just add a $group stage to end of it in order to re-structure the data.

{
    $group: {
        _id: "$_id",
        user: {$first: "$user"},
        dream: {$addToSet: "$dream"},
        works: {$addToSet: "$works"}
    }
}

With that said if you are using Mongo version 3.6+ i do recommend you use the "newer" version of $lookup to re-write your pipeline to be a bit more efficient by avoiding all these $unwind's.

Tom Slabbaert
  • 21,288
  • 10
  • 30
  • 43
  • Thank you very much for your answer @tom slabbaert, Yes, it's version 3.6+, how to use the "newer" version of ```$lookup``` to re-write my pipeline ? – nline BGO Dec 29 '19 at 10:50