I am trying to convert an Oracle query to equivalent documentDB query, however I am getting empty results.
Oracle Query :
select distinct pairingClass, pairingNumber, pairingStartDate from tableAAA
where pairingStartDate > (sysdate-7) and agentCode = "abcd";
Document DB query :
db.getCollection("tableAAA").aggregate([
{
$match: {
"agentCode": "abcd",
$and: [{ "pairingStartDate": { $lte: new Date("2023-11-21T00:00:00.000+0000") } },
{ "pairingStartDate": { $gte: new Date("2023-11-19T00:00:00.000+0000") } }
]
},
},
{ $project: { "someKey":1, "pairingClass": 1, "pairingNumber": 1, "pairingStartDate": 1, "_id": 0 } }
]
)
The above query I am getting results. But I need to get distinct results, hence I am using "group". But using group for distinct not working :
db.getCollection("tableAAA").aggregate([
{
$match: {
"agentCode": "abcd",
$and: [{ "pairingStartDate": { $lte: new Date("2023-11-21T00:00:00.000+0000") } },
{ "pairingStartDate": { $gte: new Date("2023-11-19T00:00:00.000+0000") } }
]
},
},
{
$group: {
_id : {
pairingClass : "$pairingClass",
pairingNumber : "$pairingNumber",
pairingStartDate : "$pairingStartDate"
}
}
}
{ $project: { "someKey":1, "pairingClass": 1, "pairingNumber": 1, "pairingStartDate": 1, "_id": 0 } }
]
)
Can please someone help to convert to correct format.