MongoDB Query to find the details of customers with 3 transaction in a day

Working with Data
1

Hi guys.
I have this ORACLE-SQL query which i find very hard to convert it to mongodb type language.

These are the tables i have

customer( cid cName )
account( ano , balance , cid )
Transactions( TID ,ano , tDate ,tAmount );

Question is to list the details of customers who have preferred 3 Transactions on a day.

SELECT * FROM Customer
WHERE CID IN (
SELECT DISTINCT CID
FROM ACCOUNT
WHERE ANO IN (
SELECT DISTINCT ANo
FROM Transactions
GROUP BY ANO,TDATE
Having Count(ANO)=3)
);

This Query works fine in Oracle SQL But i find difficulties while trying to implement in mongoDB !!

2

The simplest solution si to use the MongoDB BI Connector which will allow you to run that SQL query exactly as it is.

To do it without the BI connector you should use aggregation on Transactions to $group by ano and tdate (you may need to truncate the date to just the day in an expression) keeping a count of each. Once you have that use $match to filter out those with fewer then 3 transactions. THen use $group in the next stage to get the unique list of ano’s , after that $lookup to pull in the cid for each ano (I’m assuming this may be >1) then group by cid to get the unique list - finally $lookup on customer to get the details.