Get result from three collection in mongodb

Question

I have three collection contain data as like bellow,

Clients Collection

{ 
    "_id" : ObjectId("5a058e316803fafd127b23c9"), 
    "client_name" : "client A", 
    "client_status" : "A"
}
{ 
    "_id" : ObjectId("5a058e316803fafd127b23cb"), 
    "client_name" : "client B", 
    "client_status" : "A"
}

Pools Collection

{ 
    "_id" : ObjectId("5a0e76f66803fa530a7b23d4"), 
    "pool_name" : "pool A", 
    "pool_status" : "A", 
    "client_id" : ObjectId("5a058e316803fafd127b23c9"), 
    "schools" : [
        ObjectId("5a0e742b6803faa6097b2462"), 
        ObjectId("5a0e742b6803faa6097b2464")
    ]
}
{ 
    "_id" : ObjectId("5a0e76f76803fa530a7b2402"), 
    "pool_name" : "pool B", 
    "pool_status" : "A", 
    "client_id" : ObjectId("5a058e316803fafd127b23c9"), 
    "schools" : [
        ObjectId("5a0e742b6803faa6097b2463")
    ]
}
{ 
    "_id" : ObjectId("5a0e76f76803fa530a7b23f6"), 
    "pool_name" : "pool C", 
    "pool_status" : "A", 
    "client_id" : ObjectId("5a058e316803fafd127b23cb"), 
    "schools" : [
        ObjectId("5a7aa1476803fa1f117b23d1")
    ]
}

Schools Collection

{ 
    "_id" : ObjectId("5a0e742b6803faa6097b2462"), 
    "school_name" : "School A", 
    "school_status" : "A"
}
{ 
    "_id" : ObjectId("5a0e742b6803faa6097b2463"), 
    "school_name" : "School B", 
    "school_status" : "A"
}
{ 
    "_id" : ObjectId("5a0e742b6803faa6097b2464"), 
    "school_name" : "School C", 
    "school_status" : "A"
}
{ 
    "_id" : ObjectId("5a7aa1476803fa1f117b23d1"), 
    "school_name" : "School D", 
    "school_status" : "A"
}

From this collections,i need to get the list of schools of pools for the clients like bellow .

**Output**

{
    "_id" : ObjectId("5a058e316803fafd127b23c9"),
    "client_name" : "client A", 
    "client_status" : "A"
    "pools" : [
        {
            "_id" : ObjectId("5a0e76f66803fa530a7b23d4"), 
            "pool_name" : "pool A", 
            "pool_status" : "A",
            'schools' => [
                { 
                    "_id" : ObjectId("5a0e742b6803faa6097b2462"), 
                    "school_name" : "School A", 
                    "school_status" : "A"
                },
                { 
                    "_id" : ObjectId("5a0e742b6803faa6097b2464"), 
                    "school_name" : "School C", 
                    "school_status" : "A"
                },
            ]
        },
        {
            "_id" : ObjectId("5a0e76f76803fa530a7b2402"), 
            "pool_name" : "pool B", 
            "pool_status" : "A",
            "schools" : [
                { 
                    "_id" : ObjectId("5a0e742b6803faa6097b2463"), 
                    "school_name" : "School B", 
                    "school_status" : "A"
                }   
            ]
        }
    ]
},
{
    "_id" : ObjectId("5a058e316803fafd127b23cb"), 
    "client_name" : "client B",
    "client_status" : "A"
    "pools" : [
        {
            "_id" : ObjectId("5a0e76f76803fa530a7b23f6"), 
            "pool_name" : "pool C", 
            "pool_status" : "A",
            "schools" : [
                { 
                    "_id" : ObjectId("5a7aa1476803fa1f117b23d1"), 
                    "school_name" : "School D", 
                    "school_status" : "A"
                }
            ]
        }
    ]
}

Ashh · Accepted Answer · 2018-06-20T12:02:25.727

1

You can try below aggregation

Mongodb 3.6 has introduced nested $lookup pipeline... So you don't need to use another $lookup stage as you are doing above... You can use nested $lookup pipeline here

Client.aggregate([
  { "$match": { 'client_status': 'A' } },
  { "$lookup": {
    "from": Pool.collection.name,
    "let": { "client_id": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$client_id", "$$client_id" ] } } },
      { "$addFields": {
        "schools": {
          "$ifNull": ["$schools", []]
        }
      }}
      { "$lookup": {
        "from": Schools.collection.name,
        "let": { "schools": "$schools" },
        "pipeline": [
          { "$match": { "$expr": { "$in": [ "$_id", "$$schools" ] } } }
        ],
        "as": "schools"
      }}
    ],
    "as": "pools"
  }}
])

For long-winded explanation you can go through $lookup multiple levels without $unwind?

edited Jun 20 '18 at 12:02

answered Jun 20 '18 at 11:08

Ashh

44,693
14
105
132

This returns an error - Mongo Server error (MongoCommandException): Command failed with error 40081: '$in requires an array as a second argument, found: missing' on server localhost:27017. The full response is: { "ok" : 0.0, "errmsg" : "$in requires an array as a second argument, found: missing", "code" : NumberInt(40081), "codeName" : "Location40081" } – Shijin TR Jun 20 '18 at 11:36
It is because somewhere in your **schools** collection your `schools` key is missing... Try to update your collection with `schools: []` – Ashh Jun 20 '18 at 11:39
@Shijin Check the updated answer... I have added `$ifNull` condition over there... Now it will definitely work – Ashh Jun 20 '18 at 11:50
how to get each clients number of schools – Shijin TR Jun 20 '18 at 13:23
@Shijin Hiii, Please ask a new question if you have new doubt... BTW you can check the length of the student array using `$size` operator – Ashh Jun 20 '18 at 13:49

Get result from three collection in mongodb

1 Answers1