I try to convert a postgreSQL query (from the nextjs dashboard tutorial from vercel) to a mongodb query using aggregate
and $lookup
.
I know how to aggregate multiple collections and filter for one parameter. And how to query one collection for multiple parameters with $or
operator.
The problem I have is that the query searches multiple collections but with multiple search parameters. I can’t figure out a way to combine this into one aggregation pipeline.
I searched for existing questions but only found one where it says this is not possible.
So my question is, is it possible and if, how?
This is what the SQL looks like:
SELECT
invoices.customer_id,
invoices.amount,
invoices.status,
customers.name
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
WHERE
customers.id ILIKE ${`%${query}%`} OR
customers.name ILIKE ${`%${query}%`} OR
invoices.amount::text ILIKE ${`%${query}%`} OR
invoices.status ILIKE ${`%${query}%`}
ORDER BY invoices.amount DESC
this is what the collections look like:
db={
"invoices": [
{
"customer_id": "10",
"amount": 15795,
"status": "pending"
},
{
"customer_id": "20",
"amount": 20348,
"status": "paid"
}
],
"customers": [
{
"id": "10",
"name": "Delba de Oliveira"
},
{
"id": "20",
"name": "Lee Robinson"
}
]
}
2
Answers
I believe to achieve the equivalent of the PostgreSQL query using MongoDB’s aggregation framework, you will need to use the $lookup stage to join the invoices and customers collections, and then filter the results based on multiple search parameters using the $match stage with the $or operator. try something like this and see if it works,
Here the $lookup performs a left outer join with the customers collection based on the customer_id and id fields. The $match then Filters the documents based on the search parameters using the $or operator. $unwind deconstructs the customer array created by the $lookup stage. $project will reshape the documents to include only the required fields. $sort then sorts the documents based on the amount field in descending order.
Your
invoices.amount
is aNumber
so you cannot perform a regular expression on a field withNumber
data type. In PostgreSQL you can doinvoices.amount::text
whereas in MongoDB there is the$toString
operator.In this example using
aggregate
you need to join both collections using$lookup
using theinvoice.customer_id
as thelocalField
to matchcusotmers.id
as theforeignField
.I assume you want to preserve the
invoices.amount
as aNumber
data type so you can use$addFields
to add a temporary field which will hold the string version ofinvoices.amount
. That way the regex can use it to perform the query.Thereafter you use
$project
to select fields you want in the output:See HERE for a working example.
Note: in my experience it is always better to know which collection you want to perform the regex query on before a query is executed. This kind of catch-all approach is inefficient because you need to join two collections then do the
$match
stage. If you can programmatically know beforehand that you want to query either [id
,name
] or [amount
,status
] then you can dramatically improve query performance.