[MongoDB]: Aggregation with date
When we need to count the number of subdocuments based on certain criteria, including a date field.
Looking for options on how best to handle the date field for this requirement.
Sample document:
{
“_id” : “FCa123”,
“txnUidSid” : “123”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : “2015-06-24T18:30:00.000Z”,
“transactionStatus” : “REJECTED”,
“regResponses” : [
{
“receivedFrom” : “dbversity”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
}
Requirement:
Count the number of “regResponses” for “tradeDate” as today, yesterday and older than yesterday where “status=ACTIVE” and group the counts by receivedFrom & rejectType
Sample data :
db.dbversity.insert({
“_id” : “1”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-25T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “Unavista”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “2”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-25T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “Unavista”,
“rejectType” :”FCA”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “3”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-24T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “Unavista”,
“rejectType” :”FCA”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “4”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-23T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “Unavista”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “5”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-23T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “XTR”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “6”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-24T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “XTR”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “7”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-25T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “XTR”,
“rejectType” :”FCA”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “8”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-24T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “XTR”,
“rejectType” :”FCA”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “9”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-24T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “XTR”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “10”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-24T18:30:00:40.000Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “Unavista”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
db.dbversity.insert({
“_id” : “11”,
“txnUidSid” : “201410011910661”,
“domainCode” : “FCA”,
“assetClassType” : “ETD”,
“tradeDate” : ISODate(“2015-06-25T12:10:10:40.047Z”),
“transactionStatus” : “REJECTED”,
“dbversity” : [
{
“receivedFrom” : “Unavista”,
“rejectType” :”ARM”,
“responseCode” : “Business”,
“description” : “The Underlying Security Code has an invalid format.”,
“status” : “ACTIVE”
}
]
})
==========================================================================================================================
Aggregation :-
today={“$match” : {“tradeDate” : { “$eq” : new Date() }}};
yesterday={“$match” : {“tradeDate” : { “$eq” : ISODate(“2015-06-24T18:30:00:40.000Z”) }}};
olderdays={“$match” : {“tradeDate” : { “$lt” : new Date() }}};
futuredays={“$match” : {“tradeDate” : { “$gt” : new Date() }}};
unwind={$unwind: “$dbversity”};
match1={$match : {“transactionStatus”:”REJECTED”,”dbversity.status”:”ACTIVE” }};
group={$group :{_id: { tradeDate : “$tradeDate”, receivedFrom : “$dbversity.receivedFrom”, rejectType:”$dbversity.rejectType”}, count:{$sum:1}}};
db.dbversity.aggregate( today, unwind, match1 , group)
db.dbversity.aggregate( olderdays, unwind, match1 , group)
db.dbversity.aggregate( yesterday, unwind, match1 , group)
db.dbversity.aggregate( futuredays, unwind, match1 , group)
==========================================================================================================================
O/p
> today={“$match” : {“tradeDate” : { “$eq” : new Date() }}};
{
“$match” : {
“tradeDate” : {
“$eq” : ISODate(“2015-06-25T12:49:12.511Z”)
}
}
}
> yesterday={“$match” : {“tradeDate” : { “$eq” : ISODate(“2015-06-24T18:30:00:40.000Z”) }}};
{
“$match” : {
“tradeDate” : {
“$eq” : ISODate(“2015-06-24T18:30:00Z”)
}
}
}
> olderdays={“$match” : {“tradeDate” : { “$lt” : new Date() }}};
{
“$match” : {
“tradeDate” : {
“$lt” : ISODate(“2015-06-25T12:49:12.514Z”)
}
}
}
> futuredays={“$match” : {“tradeDate” : { “$gt” : new Date() }}};
{
“$match” : {
“tradeDate” : {
“$gt” : ISODate(“2015-06-25T12:49:12.514Z”)
}
}
}
>
> unwind={$unwind: “$dbversity”};
{ “$unwind” : “$dbversity” }
> match1={$match : {“transactionStatus”:”REJECTED”,”dbversity.status”:”ACTIVE” }};
{
“$match” : {
“transactionStatus” : “REJECTED”,
“dbversity.status” : “ACTIVE”
}
}
> group={$group :{_id: { tradeDate : “$tradeDate”, receivedFrom : “$dbversity.receivedFrom”, rejectType:”$dbversity.rejectType”}, count:{$sum:1}}};
{
“$group” : {
“_id” : {
“tradeDate” : “$tradeDate”,
“receivedFrom” : “$dbversity.receivedFrom”,
“rejectType” : “$dbversity.rejectType”
},
“count” : {
“$sum” : 1
}
}
}
>
> db.dbversity.aggregate( today, unwind, match1 , group)
> db.dbversity.aggregate( olderdays, unwind, match1 , group)
{ “_id” : { “tradeDate” : ISODate(“2015-06-24T18:30:00Z”), “receivedFrom” : “XTR”, “rejectType” : “ARM” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-23T18:30:00Z”), “receivedFrom” : “XTR”, “rejectType” : “ARM” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-23T18:30:00Z”), “receivedFrom” : “Unavista”, “rejectType” : “ARM” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-24T18:30:00Z”), “receivedFrom” : “XTR”, “rejectType” : “FCA” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-24T18:30:00Z”), “receivedFrom” : “Unavista”, “rejectType” : “FCA” }, “count” : 1 }
> db.dbversity.aggregate( yesterday, unwind, match1 , group)
{ “_id” : { “tradeDate” : ISODate(“2015-06-24T18:30:00Z”), “receivedFrom” : “XTR”, “rejectType” : “ARM” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-24T18:30:00Z”), “receivedFrom” : “XTR”, “rejectType” : “FCA” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-24T18:30:00Z”), “receivedFrom” : “Unavista”, “rejectType” : “FCA” }, “count” : 1 }
> db.dbversity.aggregate( futuredays, unwind, match1 , group)
{ “_id” : { “tradeDate” : ISODate(“2015-06-25T18:30:00Z”), “receivedFrom” : “XTR”, “rejectType” : “FCA” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-25T18:30:00Z”), “receivedFrom” : “Unavista”, “rejectType” : “FCA” }, “count” : 1 }
{ “_id” : { “tradeDate” : ISODate(“2015-06-25T18:30:00Z”), “receivedFrom” : “Unavista”, “rejectType” : “ARM” }, “count” : 1 }