Queries with Lookup and Aggregation

Working with Data
1

I am looking for examples where in aggregate function, we doing MATCH first, and then Doing a Lookup.

E.g. I have 2 collections. Person and Payments. I want to list down all payments of a person having name as “Gaurav”, and payment date between “X Date range”

So, we will do an aggregate Query on Payments with lookup on Payments, and the output to have data from Payments with fields from Person too.

A. Filter on Payments before Lookup to Person.
b. Pipeline on matching name of Person
c. Filter on joined query.

Can someone share a scenario similar to this along with the sample aggregation query. Its bit urgent.

2

What have you tried so far?

3

I have tried aggregation on payments, and then lookup with pipeline and filter.

I am looking for samples where “match” is done before lookup and “match” is also done on lookup collection, and then a combined match after that.

4

You can just $match then $lookup, $unwind (or use arrayelementat) to get the lookup field not as an array and then a $match after as needed.

What does your data and current pipeline look like? Put up a sample document here or create a mongo playground sketch with the details in it.

Has lots of examples of it’s use.

5

Here is the pipeline.

[{$match: {
Status: ‘Paid’
}}, {$lookup: {
from: ‘crmTenant’,
localField: ‘TenantId’,
foreignField: ‘_id’,
as: ‘TenantLookup’
}}, {$match: {
‘TenantLookup.PropertyNumber’: ‘MH12014’
}}]

What I want is to actually have the match on “crmTenant” i.e. PropertyNumber:MH12014 before doing the lookup stage too.

Is it possible with mongodb?

6

Please read Formatting code and log snippets in posts and format your accordingly.

Also provide sample documents from both collections. Sample resulting documents is also needed.

1 Like