Amazon DocumentDB query with multiple distinct field not working

32 views Asked by At

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.

0

There are 0 answers