How to lookup inside array in mongodb aggregate?

Question

I have a problem with how to make lookup inside array with mongodb aggregation.

For example, i have a user data json like this:

{
   "_id": "1234",
   "name": "Tony",
   "language": [
       {
           "_id": "111",
           "language_id": "919",
           "level": "Expert"
       }
       {
           "_id": "111",
           "language_id": "920",
           "level": "Basic"
       }
   ]
}

And my language collection:

{
   "_id": "919",
   "name": "English"
},
{
   "_id": "920",
   "name": "Chinese"
}

And i want make mongodb aggregation which is result like this:

{
   "_id": "1234",
   "name": "Tony",
   "language": [
       {
           "name": "English",
           "level": "Expert"
       }
       {
           "name": "Chinese",
           "level": "Basic"
       }
   ]
}

Thanks before.

turivishal · Accepted Answer · 2021-01-31T07:35:28.967

1

$unwind deconstruct language array
$lookup with language collection
$arrayElemAt to select specific index element from return result from language
$group by _id and reconstruct language array

db.user.aggregate([
  { $unwind: "$language" },
  {
    $lookup: {
      from: "language",
      localField: "language.language_id",
      foreignField: "_id",
      as: "language.name"
    }
  },
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      language: {
        $push: {
          name: { $arrayElemAt: ["$language.name.name", 0] },
          level: "$language.level"
        }
      }
    }
  }
])

Playground

edited Jan 31 '21 at 07:35

answered Jan 31 '21 at 07:15

turivishal

34,368
7
36
59

Thanks its work, but the $group is doesnt work, because i have a $project to show field under – ASHafizullah Jan 31 '21 at 07:51
when i add $group, then my result return null [ ] – ASHafizullah Jan 31 '21 at 07:53
what and where you have added $project stage? – turivishal Jan 31 '21 at 08:05
What about if i have another lookup, for example verify data. Check: https://mongoplayground.net/p/nqZtqQ_BgLb and i want to show verify.status: 1 – ASHafizullah Jan 31 '21 at 08:12
i can see third collection, but what the bases you want to lookup a don't know purpose, if its new question then you can ask new question and explain this in detail. – turivishal Jan 31 '21 at 08:17
Okay i will ask the new question – ASHafizullah Jan 31 '21 at 08:29
look at this if am not wrong https://mongoplayground.net/p/RV5tBqSueKB – turivishal Jan 31 '21 at 08:33
yes thats correct, so i cant use $project for that? – ASHafizullah Jan 31 '21 at 08:37
you need to pass it from $group stage, no need to use $project for that – turivishal Jan 31 '21 at 08:40
can you help me with this question? https://stackoverflow.com/questions/66008416/how-to-mongoose-aggregate-nested-lookup-array – ASHafizullah Feb 02 '21 at 12:55

How to lookup inside array in mongodb aggregate?

1 Answers1