CODEWITHSUNDEEP
mongodbaggregation-framework
shadab.tughlaq
shadab.tughlaq

Reputation: 454

Mongo Aggregate find element in array based on match/sort/slice/group query

I am trying to write a pipeline query on the following

{
    "_id" : 1,
    "createdDate" : "2018-01-01 00:00:00",
    "visits" : [ 
        {
            "date" : "2018-02-01 00:00:00",
            "type" : "A",
        }, 
        {
            "date" : "2018-03-01 00:00:00",
            "type" : "B",
        }]
    "user" : "Alpha"
},

{
    "_id" : 1,
    "createdDate" : "2018-01-15 00:00:00",
    "visits" : [ 
        {
            "date" : "2018-02-01 00:00:00",
            "type" : "B",
        }, 
        {
            "date" : "2018-04-08 00:00:00",
            "type" : "A",
        }]
    "user" : "Alpha"
}

I want to

Example:

date between 2018-01-01 and 2018-02-02

Desired Output

{_id:"Alpha",
type: {"A":1, "B":1},
count : 2}

date between 2018-01-01 and 2018-03-02

Desired Output

{_id:"Alpha",
type: {"A":0, "B":2},
count : 2}

Here is where i have got so far

{$match:{ "createdDate":{   "$gte":"2018-01-01 00:00:00",
                            "$lte":"2018-02-02 23:59:59"}     
        }
},
{$unwind:"$visits"},
{$match:{   "visits.date":{ "$gte":"2018-01-01 00:00:00",
                            "$lte":"2018-02-02 23:59:59"}
          }
},
{$project:{_id:1, "visit_type":"$visits.type", "visit_date":"$visits.date"}},
{ $group : {  _id         : "$_id",
              "visits"    :    { "$push": { 
                                    "date": "$visit_date",
                                    "type": "$visit_type"                                        
                                }
                                }
            }
}

Upvotes: 0

Views: 341

Answers (1)

s7vr
s7vr

Reputation: 75924

You can try below aggregation.

Added two groups one group each for count by type and other for count by user.

[
  {"$match":{"createdDate":{"$gte":"2018-01-01 00:00:00","$lte":"2018-03-02 23:59:59"}}},
  {"$unwind":"$visits"},
  {"$match":{"visits.date":{"$gte":"2018-01-01 00:00:00","$lte":"2018-03-02 23:59:59"}}},
  {"$sort":{"visits.date":-1}},
  {"$group":{"_id":{"_id":"$_id","user":"$user"},"latestvisit":{"$first":"$visits"}}},
  {"$group":{"_id":{"user":"$_id.user","type":"$latestvisit.type"},"visits":{"$sum":1}}},
  {"$group":{
    "_id":"$_id.user",
    "type":{"$push":{"type":"$_id.type","visits":"$visits"}},
    "count":{"$sum":"$visits"}
  }}
]

Upvotes: 1

Related Questions