CODEWITHSUNDEEP
mongodbaggregation-framework
Akshath Kumar
Akshath Kumar

Reputation: 519

Query to filter the information from a mongodb collection

I have to retrieve a list of users from list of documents which matches the condition. The document structure look like below

{
"_id" : ObjectId("660ff865d4f9075d40a1101c"),
"orderFormId" : "OF-rJw4elBYK",
"orderDetails" : [ 
    {
        "courseId" : "53fc31f443fa1fe885d3ad61",
        "userInfo" : [ 
            {
                "dob" : "2015-03-22T18:30:00.000Z",
                "lastName" : "M",
                "status" : "Pending Appproval",
                "eMail" : "[email protected]",
                "firstName" : "Arun"
            }, 
            {
                "status" : "requested",
                "firstName" : "asdasd",
                "dob" : "2015-03-23T18:30:00.000Z",
                "lastName" : "asdafasd",
                "userId" : "RQ-11xDPALgR",
                "eMail" : "[email protected]"
            }, 
            {
                "status" : "requested",
                "firstName" : "asdaf",
                "dob" : "2015-03-23T18:30:00.000Z",
                "lastName" : "fsdsdf",
                "userId" : "RQ-OdoXAOLrB",
                "eMail" : "[email protected]"
            }, 
            {
                "status" : "requested",
                "firstName" : "asdas",
                "dob" : "2015-03-23T18:30:00.000Z",
                "lastName" : "asdasd",
                "userId" : "RQ-Bw2Xokmda",
                "eMail" : "[email protected]"
            }
        ],
        "userCount" : 5,
        "Name" : "Compilers",
        "coursePrice" : 1000,
        "coursetype" : "offline"
    }, 
    {
        "courseId" : "53fc31f443fa1fe885d3ad62",
        "userInfo" : [ 
            {
                "dob" : "2015-03-22T18:30:00.000Z",
                "lastName" : "Raj",
                "status" : "requested",
                "eMail" : "[email protected]",
                "firstName" : "Nithul"
            }, 
            {
                "dob" : "2015-03-22T18:30:00.000Z",
                "lastName" : "P C",
                "status" : "requested",
                "eMail" : "[email protected]",
                "firstName" : "Kahyoom"
            }
        ],
        "userCount" : 1,
        "Name" : "Computer Science 101",
        "coursePrice" : 0,
        "coursetype" : "offline"
    }, 
    {
        "courseId" : "57fc31f443fa1fe885d3ad64",
        "userInfo" : [ 
            {
                "status" : "requested",
                "firstName" : "asdasd",
                "dob" : "2015-03-23T18:30:00.000Z",
                "lastName" : "aasdasd",
                "userId" : "RQ-WqEXBkjv5",
                "eMail" : "[email protected]"
            }
        ],
        "userCount" : 1,
        "coursePrice" : 0,
        "Name" : "Introduction to Haptics: Self-Paced",
        "coursetype" : "offline"
    }
],
"companyId" : ObjectId("54128cc57525614f6e3e710a"),
"createdDate" : ISODate("2015-03-23T11:26:29.027Z"),
"updatedDate" : ISODate("2015-03-24T15:00:33.248Z"),
"crmId" : ObjectId("660ab20bd4f9075d40a10d52"),
"urmId" : ObjectId("660ab20bd4f9075d40a10d52"),
"activeFlag" : 0,
"customCompanyCode" : "baa-106",
"status" : "Pending approval"}

From above document i have to get the users who have the status "Approved" in userInfo object which exists inside this document. I have created one query but this will output all the users who have different status.My query look like below,

db.clnTrainingRequest.find({companyId:ObjectId('54128cc57525614f6e3e710a'), "orderDetails.userInfo.status":{$in:['Approved']}}).toArray()

Please any one help me to sort out this issue

Upvotes: 0

Views: 52

Answers (1)

Sede
Sede

Reputation: 61225

You can use aggregation. You need to $unwind orderDetails and the userInfo arrays and then use the $match to get users with status Approved

db.clnTrainingRequest.aggregate(
    [   { "$match": { "companyId": ObjectId('54128cc57525614f6e3e710a') },
        { "$unwind": "$orderDetails" }, 
        { "$unwind": "$orderDetails.userInfo" }, 
        { "$match": { "orderDetails.userInfo.status": "Approved" }}
    ]
)

Upvotes: 1

Related Questions