Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to aggregate from multiple collections with multiple OR parameters

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"
    }
  ]
}
Tags:

2

Answers


  1. 0

    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,

    db.invoices.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "id",
      as: "customer"
    }
  },
  {
    $match: {
      $or: [
        { "customer.id": { $regex: query, $options: "i" } },
        { "customer.name": { $regex: query, $options: "i" } },
        { amount: { $regex: query, $options: "i" } },
        { status: { $regex: query, $options: "i" } }
      ]
    }
  },
  {
    $unwind: "$customer"
  },
  {
    $project: {
      "customer_id": 1,
      "amount": 1,
      "status": 1,
      "customer.name": 1
    }
  },
  {
    $sort: { "amount": -1 }
  }
])

    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.

    Login or Signup to reply.
  2. 0

    Your invoices.amount is a Number so you cannot perform a regular expression on a field with Number data type. In PostgreSQL you can do invoices.amount::text whereas in MongoDB there is the $toString operator.

    In this example using aggregate you need to join both collections using $lookup using the invoice.customer_id as the localField to match cusotmers.id as the foreignField.

    I assume you want to preserve the invoices.amount as a Number data type so you can use $addFields to add a temporary field which will hold the string version of invoices.amount. That way the regex can use it to perform the query.

    Thereafter you use $project to select fields you want in the output:

    db.invoices.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "id",
      as: "customer"
    }
  },
  {
    $addFields: {
      amountAsString: {
        $toString: "$amount"
      }
    }
  },
  {
    $match: {
      $or: [
        {
          "customer.id": {
            $regex: query,
            $options: "i"
          }
        },
        {
          "customer.name": {
            $regex: query,
            $options: "i"
          }
        },
        {
          amountAsString: {
            $regex: query,
            $options: "i"
          }
        },
        {
          status: {
            $regex: query,
            $options: "i"
          }
        }
      ]
    }
  },
  {
    $unwind: "$customer"
  },
  {
    $project: {
      customer_id: 1,
      amount: 1,
      status: 1,
      name: "$customer.name"
    }
  },
  {
    $sort: { amount: -1 }
  }
])

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search