Using $lookup & $Unwind without pipeline command

[rajesh_b]

Hi Team,
I’m new to Mongo DB and using Mongo DB API for Cosmos DB.
Cosmos DB does not fully support the $lookup & Pipeline command so I have an issue with using both in a single Aggregate. So trying to avoid but I need to get the total students to count based on Site id.
have 2 collections … Class, Students. need to get the total students to count based on site id without the pipeline.
need an alternative to use that.
here below my code:

db.getCollection(classrooms).aggregate(
  [
  {
    "$sort": {
      "_id": -1
    }
  },
  {
    "$match": {
      "$and": [
        {
          "is_deleted": 0,
          "site_id": "111"
        }
      ]
    }
  },
  { "$addFields": { "Class_Id": { "$toString": "$_id" }}},
  { "$lookup": {
    "from":'student_collection',
    "localField": "Class_Id",
    "foreignField": "Class_Id",
    "as": "student_data",
    "pipeline":[
        {"$match":{
            "classrooms.site_id" : "111"
        }}
    ],      
  }},
  { "$addFields": {"studentCount": { "$size": "$student_data"}}},
  {
    "$limit": 100
  },
  {
    "$skip": 0
  },
])

any help!!

Thank you

[Kushagra_Kesav]

Hi @rajesh_b,

Welcome to the MongoDB Community forums

To better understand your question:

Could you share the following

• Sample document of both the collection and Class, and Students,
• The MongoDB version you are using,
• And, your expected output (if possible).

Best,
Kushagra

[rajesh_b]

@Kushagra_Kesav : Kesav, Thank you for your response. my data is in Cosmos Db (mongo api)
Please see sample data as below.
and * The MongoDB version you are using : Cosmos Db Mongo 4.2 Version , Node js application.

• And, your expected output (if possible):
  Based on Site id, I need total count of students.
  Site name: " " , total students count: “”

– classroom
<{ "_id" : ObjectId("123A1"), "Class_Name" : "Elementary R1", "Org_Id" : "A123", "Org_Data" : { "label" : "Montgomery County School", "value" : "A123" }, "Site_Id" : "111", "Site_Data" : { "label" : "College Gardens Elementary", "value" : "111" }, "Periods" : false, "Created_By" : "100003", "Created_Date" : ISODate("2022-10-14T14:41:55.280+0000"), "is_active" : NumberInt(0), "Class_Id" : NumberInt(10000) }
/>
Student collection
<

    "_id" : ObjectId("01A"),
    "Student_id" : "8968",
    "Org_Id" : "A123",
    "Org_Data" : {
        "label" : "Montgomery County School",
        "value" : "A123"
    },
    "Site_Id" : "111",
    "Site_Data" : {
        "label" : "College Gardens Elementary",
        "value" : "111"
    },
    "Class_Id" : "123A1",
    "Class_Data" : {
        "label" : "Elementary R1",
        "value" : "123A1"
    },
    "First_Name" : "Eddie111",
    "Last_Name" : "Bauer111",
    "Gender" : "male",  
    "Student_Image_Id" : "",
    "Created_By" : NumberInt(100007),
    "Created_Date" : ("2022-10-27T17:50:46.065+0000"),
    "is_active" : NumberInt(0),
    "Student_Id" : NumberInt(10000)
},```

```{
    "_id" : ObjectId("01A"),
    "Student_id" : "8968",
    "Org_Id" : "A123",
    "Org_Data" : {
        "label" : "Montgomery County School",
        "value" : "A123"
    },
    "Site_Id" : "111",
    "Site_Data" : {
        "label" : "College Gardens Elementary",
        "value" : "111"
    },
    "Class_Id" : "123A1",
    "Class_Data" : {
        "label" : "Elementary R1",
        "value" : "123A1"
    },
    "First_Name" : "testname",
    "Last_Name" : "test lastname", 
    "Gender" : "male",  
    "Student_Image_Id" : "",
    "Created_By" : NumberInt(100007),
    "Created_Date" : ("2022-10-27T17:50:46.065+0000"),
    "is_active" : NumberInt(0),
    "Student_Id" : NumberInt(10000)
}```
/>
Let me know if need any!

[Kushagra_Kesav]

Hi @rajesh_b,

Thank you for providing the sample collection and version information along with your desired output. I think it’s clear that the workflow you desire is easier to do with $lookup. Unfortunately, as you have discovered, CosmosDB does not fully support this, so you don’t have many options on this front. It’s important to note that CosmosDB is not a MongoDB product so we cannot comment on what it can or cannot do, or its compatibility with a genuine MongoDB product.

An alternative approach would be to implement this function within the application code, e.g. by simulating the functionality of $lookup in code. Since this forum specializes in MongoDB, if you require additional assistance, I would suggest visiting the CosmosDB forums or reaching out to their support team.

Best,
Kushagra

[rajesh_b]

Thank you for your reply Kesav.
Could you help me with one solution based on provided information so that I will test that whether it’s working or not?
Regards,
Rajesh