How to optimize query MongoDB Query for nested condition

Question

I'm trying to query my collection to display list of : reports & report section for some specific user level allowed to view. The collection looks like this :

{ 
    "ReportName" : "Report A", 
    "ReportID" : "1", 
    "ReportSection" : [
        {
            "SectionName" : "ReportSection A", 
            "AllowedLevel" : [
                "1", 
                "2"
            ]
        }, 
        {
            "SectionName" : "ReportSection B", 
            "AllowedLevel" : [
                "1", 
                "2",
                "3"
            ]
        }, 
        {
            "SectionName" : "ReportSection C", 
            "AllowedLevel" : [

            ]
        }
    ]
}

The parameter of the query is user level. For example the user level "2" will be allowed to view Report & Section :

Report A & Report Section A
Report A & Report Section B

For now I have to Query like this :

db.Report.find({
    "ReportSection.AllowedLevel":"2" 
});

I get list of report and then in the application, I have to check every report section for allowed level. I believe there is a better solution for this.

I expect to have the result like this (assuming the user level : "2")

{ 
    "ReportName" : "Report A", 
    "ReportID" : "1", 
    "SectionName" : "ReportSection A", 
},
{
    "ReportName" : "Report A",  
    "ReportID" : "1", 
    "SectionName" : "ReportSection B", 
}

score 0 · Answer 1 · answered Apr 24 '19 at 04:07

0

Maybe unwind the report section in the aggregation and then find the matching section and indexing the allowed level, will probably be faster? You have to test it to see how it does for your schema.

db.Report.aggregate([{
   $unwind:ReportSection
},{
   $match:{
      .....
   }
}])

answered Apr 24 '19 at 04:07

Ninth Autumn

461
1
5
10

is it ok if I make it 3 stage of : match, unwind, match. To reduce the number of document processed on using aggregate – eddy wijaya Apr 24 '19 at 06:07

score 0 · Answer 2 · answered Apr 24 '19 at 05:14

Try as below:

db.collection.aggregate([
    {
        $unwind: "$ReportSection"    
    },
    {
        $unwind: "$ReportSection.AllowedLevel"
    },
    {
        $match: {
            "ReportSection.AllowedLevel": "2"
        }
    },
    {
        $project: {
            "ReportName":1,
            "ReportID":1,
            "SectionName": "$ReportSection.SectionName"
        }
    }
])

The resulted response will be as below:

/* 1 createdAt:24/04/2019, 10:35:24*/
{
    "_id" : ObjectId("5cbfee945d13d8232aff6e97"),
    "ReportName" : "Report A",
    "ReportID" : "1",
    "SectionName" : "ReportSection A"
},

/* 2 createdAt:24/04/2019, 10:35:24*/
{
    "_id" : ObjectId("5cbfee945d13d8232aff6e97"),
    "ReportName" : "Report A",
    "ReportID" : "1",
    "SectionName" : "ReportSection B"
}

How to optimize query MongoDB Query for nested condition

2 Answers2