how to show 0 for week when no record is matching that week in $week mongodb query

Question

My collection looks like below with details

    /* 1 createdAt:6/13/2018, 5:17:07 PM*/
{   "_id" : ObjectId("5b21043b18f3bc7c0be3414c"),   
    "Number" : 242,
    "State" : "2",
    "City" : "3",
    "Website" : "",
    "Contact_Person_Name" : "Ajithmullassery",  
    "CreatedById" : "Admin",
    "UpdatedById" : "Admin",    
    "IsActive" : true,
    "UpdatedOn" : ISODate("2018-06-13T17:17:07.313+05:30"),
    "CreatedOn" : ISODate("2018-06-13T17:17:07.313+05:30")  
},

/* 2 createdAt:6/13/2018, 6:45:42 PM*/
{
    "_id" : ObjectId("5b2118fe18f3bc7c0be3415b"),   
    "Number" : 243,
    "State" : "1",
    "City" : "143",
    "Website" : "",
    "Contact_Person_Name" : "sachitkumar",  
    "CreatedById" : "vinoth",
    "UpdatedById" : "Admin",    
    "IsActive" : true,
    "UpdatedOn" : ISODate("2018-06-13T18:45:42.590+05:30"),
    "CreatedOn" : ISODate("2018-06-13T18:45:42.590+05:30")

},

/* 3 createdAt:6/18/2018, 5:34:33 PM*/
{
    "_id" : ObjectId("5b279fd118f3bc7c0be34166"),   
    "Number" : 244,
    "State" : "0",
    "City" : "8",
    "Website" : "",
    "Contact_Person_Name" : "Akshay",   
    "CreatedById" : "vinoth",
    "UpdatedById" : "Admin",    
    "IsActive" : true,
    "UpdatedOn" : ISODate("2018-06-18T17:34:33.270+05:30"),
    "CreatedOn" : ISODate("2018-06-18T17:34:33.270+05:30")

},

/* 4 createdAt:6/20/2018, 1:02:21 PM*/
{
    "_id" : ObjectId("5b2a030518f3bc7c0be3416d"),   
    "Number" : 245,
    "State" : "5",
    "City" : "6",
    "Website" : "",
    "Contact_Person_Name" : "Dr DS Mithra", 
    "CreatedById" : "vinoth",
    "UpdatedById" : "Admin",
    "FacilityID" : "594387f5e2de7be83be5d5f1",
    "IsActive" : true,
    "UpdatedOn" : ISODate("2018-06-20T13:02:21.887+05:30"),
    "CreatedOn" : ISODate("2018-06-20T13:02:21.887+05:30")
},

/* 5 createdAt:6/20/2018, 1:08:58 PM*/
{
    "_id" : ObjectId("5b2a049218f3bc7c0be3416e"),   
    "Number" : 245,
    "State" : "5",
    "City" : "6",
    "Website" : "",
    "Contact_Person_Name" : "Ramaswamy Manickam",   
    "CreatedById" : "vinoth",
    "UpdatedById" : "Admin",    
    "IsActive" : true,
    "UpdatedOn" : ISODate("2018-06-20T13:08:58.040+05:30"),
    "CreatedOn" : ISODate("2018-06-20T13:08:58.040+05:30")

}

I have the query like below

db.collectionName.aggregate([

    //where query
    { "$match": {  $and:[{CreatedOn:{$lte:ISODate("2018-07-14T13:59:08.266+05:30")}},{CreatedOn:{$gte:ISODate("2018-06-10T13:59:08.266+05:30")}}] } },
    //distinct column 
    {
        "$group": {
            _id: {$week: '$CreatedOn'},
            documentCount: {$sum: 1}
        }
    }
])

The query will return the weeknumber and number of documents created as below

/* 1 */
{
    "_id" : 26,
    "documentCount" : 1
},

/* 2 */
{
    "_id" : 25,
    "documentCount" : 1
},

/* 3 */
{
    "_id" : 24,
    "documentCount" : 9
},

/* 4 */
{
    "_id" : 23,
    "documentCount" : 2
}

In above _id is the weeknumber. If in case in above results weekNumber : 23 no records are created then the query gives only 3 records removing the "_id":23.

How to get the records with documentcount as zero when there is no records created.

Like in above example when no records for _id: 23 should get like below

/* 4 */
    {
        "_id" : 23,
        "documentCount" : 0
    }

mickl · Accepted Answer · 2018-07-23T16:23:38.103

1

As $week can return a value between 0 and 53 I assume you expect 54 documents as a result with 0 or non-zero values for documentCount. To achieve that you should collect all your documents into one ($group-ing by null) and then generate the output.

To generate a range of numbers you can use $range operator and then you can generate the output using $map. To transform an array of documents into multiple docs you can use $unwind.

db.collectionName.aggregate([
    //where query
    { "$match": {  $and:[{CreatedOn:{$lte:ISODate("2018-07-14T13:59:08.266+05:30")}},{CreatedOn:{$gte:ISODate("2018-06-10T13:59:08.266+05:30")}}] } },
    //distinct column 
    {
        "$group": {
            _id: {$week: '$CreatedOn'},
            documentCount: {$sum: 1}
        }
    },
    {
        $group: {
            _id: null,
            docs: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            docs: {
                $map: {
                    input: { $range: [ {$week:ISODate("2018-06-10T13:59:08.266+05:30")}, {$week:ISODate("2018-07-14T13:59:08.266+05:30")}]},
                    as: "weekNumber",
                    in: {
                        $let: {
                            vars: { index: { $indexOfArray: [ "$docs._id", "$$weekNumber" ] } },
                            in: {
                                $cond: {
                                    if: { $eq: [ "$$index", -1 ] },
                                    then: { _id: "$$weekNumber", documentCount: 0 },
                                    else: { $arrayElemAt: [ "$docs", "$$index" ] }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $unwind: "$docs"
    },
    {
        $replaceRoot: {
            newRoot: "$docs"
        }
    }
])

Using $indexOfArray to check if array of current docs contains the document (-1 otherwise) and $arrayElemAt to get existing document from docs. Last step ($replaceRoot) is just to get rid of one level of nesting (docs). Outputs:

{ "_id" : 0, "documentCount" : 0 }
{ "_id" : 1, "documentCount" : 0 }
{ "_id" : 2, "documentCount" : 0 }
...
{ "_id" : 22, "documentCount" : 0 }
{ "_id" : 23, "documentCount" : 2 }
{ "_id" : 24, "documentCount" : 9 }
{ "_id" : 25, "documentCount" : 1 }
{ "_id" : 26, "documentCount" : 1 }
{ "_id" : 27, "documentCount" : 0 }
...
{ "_id" : 52, "documentCount" : 0 }
{ "_id" : 53, "documentCount" : 0 }

You can easily customize returned results modifying the input of $map stage. For instance you can pass an array of consts like input: [21, 22, 23, 24] as well.

EDIT: To get the weeks between specified dates you can use $week for start and end date to get the numbers.

edited Jul 23 '18 at 16:23

answered Jul 23 '18 at 10:50

mickl

48,568
9
60
89

where can we pass match or any condition as like i have mention in question – charan tej Jul 23 '18 at 13:51
@charantej modified my answer – mickl Jul 23 '18 at 14:08
It is not giving any results just 19 records with all documentcount as 0. – charan tej Jul 23 '18 at 14:13
please check the updated question where i have given sample of my collection – charan tej Jul 23 '18 at 14:18
I assumed that 4 documents you pasted are the output of your current aggregation and you need to append something to include default values. Check it here: https://mongoplayground.net/p/qjM4Yz6FuQg – mickl Jul 23 '18 at 14:20
you have shown the results but i dont need all the weeks from 1 to 53. I will only pass two dates as like in match condition. lets say a date in may and a date in july. Now if i run my query it will give result all the week number present in between the dates i passed which have documentcount greater than 0. So now i asked in question like i need to get all the week number between the input dates when the documentcount zero also. – charan tej Jul 23 '18 at 14:54
I need to get only the weeknumbers from the match condition i send – charan tej Jul 23 '18 at 15:00
1

You can restrict the range to only look weeks from input dates. Something like `{ $range: [ {$week:ISODate("2018-06-10T13:59:08.266+05:30")}, {$week:ISODate("2018-07-14T13:59:08.266+05:30")}]}`. – s7vr Jul 23 '18 at 15:56
@mickl Thanks for the help. The query was correctly working for some limited months. According to this query if no records means weeknumber with document count 0 will come. but now I tried with selecting Jan 1st to present date then it is just resulting only 20 records. please can you check. – charan tej Jul 26 '18 at 13:03
@Veeram Thanks for the help. This query is returning only 20 records. Please check my previous comment.I have tried fixing but couldnt make it – charan tej Jul 26 '18 at 13:04
@charantej you mean you're getting 20 results in your application code or in Mongo Shell ? – mickl Jul 27 '18 at 04:35
@mickl Thanks for this help. I really forgot, I got it in mongo shell. Now everything fine. I have doubt it is working only for each year. how to find records created between last year 25-dec-2017 to 25-jan-2018. – charan tej Jul 27 '18 at 10:54
1

@charantej I've seen similar question some time ago. You have to specify that range as ($week)*$year - ($week)*$year and then use $divide and modulo to get week and year back. Let me know if need more help or just create another question so that I'll be able to explain that – mickl Jul 27 '18 at 11:01

how to show 0 for week when no record is matching that week in $week mongodb query

1 Answers1

Linked