How to use mongoose lookup child match?

Question

Sorry my English is bad...

My query is

categorys.aggregate([{
            "$sort": {
                "order": 1,
                "id": 1
            }
        },

        {
            "$lookup": {
                "localField": "id",
                "from": "categorys",
                "foreignField": "parentId",
                "as": "child"
            }
        },
        {
            "$match": {
                see: true,
                depth: 1
            }
        }
    ]).exec(function(err, Categorys)

is result

{
    "_id": "596e237c414f2137b0c4e9c2",
    "id": 3,
    "name": "DB",
    "address": "DB",
    "parentId": 0,
    "depth": 1,
    "see": true,
    "__v": 0,
    "child": [{
            "_id": "596e24701e1bd30dc415b894",
            "id": 5,
            "name": "Mongodb",
            "address": "Mongodb",
            "parentId": 3,
            "depth": 2,
            "see": true,
            "__v": 0
        },
        {
            "_id": "596e24821e1bd30dc415b895",
            "id": 6,
            "name": "mssql",
            "address": "mssql",
            "parentId": 3,
            "depth": 2,
            "see": false,
            "__v": 0
        }
    ]
}]

I don't look result in mssql (see:false) how about match? Help me!

You appear to be looking for a "self-join", and whilst you can use `$lookup` it is probably not the best thing to do. You actually appear to be asking something similar to [Aggregation filter after $lookup](https://stackoverflow.com/q/36459983/2313887), which again despite the title is actually about a "self-join" itself. And the different approach is explained in the answer. — Neil Lunn, Jul 23 '17 at 10:55

Shaishab Roy · Accepted Answer · 2017-07-23T11:26:34.653

2

You can use $filter in $project stage if you want to match in child array. like bellow.

categorys.aggregate([
  {
    "$sort": {
      "order": 1,
      "id": 1
    }
  },
  {
    "$lookup": {
      "localField": "id",
      "from": "categorys",
      "foreignField": "parentId",
      "as": "child"
    }
  },
  {
    $project: {
      name: 1,
      address: 1,
      // ... as you need 
      child: {
        $filter: {
          input: "$child",
          as: "item",
          cond: {
            $and: [
              {$eq: ["$$item.see", true]},
              {$eq: ["$$item.depth", 2]}
            ]
          }
        }
      }
    }
  }
])

N.B: If you want to use match condition for parent field like "depth":1,"see":true, then you should use that $match stage before $lookup

categorys.aggregate([
  {$match:{//parent field match condition first}},
  {$sort:{}},
  {$lookup:{}},
  {$project:{//for child}}
]);

edited Jul 23 '17 at 11:26

answered Jul 23 '17 at 11:19

Shaishab Roy

16,335
7
50
68

ok i see very very thx – young Jul 23 '17 at 11:24
Actually that's exactly what I'm trying to help the OP by telling them **NOT** to do that. You do not need to "join" the the same collection. It's really inefficient and unecessary – Neil Lunn Jul 23 '17 at 11:26
ok! Thank you for your advice in studying. – young Jul 23 '17 at 11:32
how can we get the `child` array without join?. Could you please post your answer or any reference then we can learn. @NeilLunn – Shaishab Roy Jul 23 '17 at 11:35
[I already have](https://stackoverflow.com/q/36459983/2313887). But Only did not close as a duplicate because it was unclear what the OP was asking. But now it's clear that it is in fact the same thing. – Neil Lunn Jul 23 '17 at 11:38

How to use mongoose lookup child match?

1 Answers1