#Dynamic `where` Filter for MongoDB Aggregation Query

1 messages · Page 1 of 1 (latest)

novel islandBOT
#

Hey guys, I'm trying to run a MongoDB aggregation operation. While the overall process works fine, I'm having trouble passing a where filter dynamically. Is there a function in db-mongodb or any other way to convert a where filter?

A little context: I want to get items that are available for filters in the frontend. And I need to pass the where query, so that I only get the avaliable options…

For example, if I want to filter by a relationship, I currently have to do something like this:

  const aggregatedItems = await payload.db.collections['products']?.aggregate(
    [
      {
        $match: {
          'technicalData.type': new ObjectId('68347c88395adc242ad720f3'),
        },
      },
      // ...
    ],
  );

In reality, the filters are more complex than just a simple relation. Any Ideas?

winged fiberBOT
#

Original message from @spice thistle - Moved from #general message

azure ruin
#

Yes, there is 🙂

Here some excerpt of some code I use combined with what you use.

const productModel = payload.db.collections['products'];

const matchConditions: FilterQuery<any>[] = [];

matchConditions.push(await productModel.buildQuery({ payload, where: { 'technicalData.type': { equals: '68347c88395adc242ad720f3' } } }));

const aggregatePipeline: PipelineStage[] = [
    {
        $match: {
            $and: matchConditions,
        },
    },
];

const aggregateResult = await productModel.aggregate(aggregatePipeline).exec();
spice thistle
#

@azure ruin Thanks for your message! But just to give a bit more context:

Like, for example, I want to know what color relations are available.
This is my current aggregatedItems result for that. The field.relationTo is the collection slug.

Everything is dynamically defined, so it needs to be flexible.

  const aggregatedItems = await payload.db.collections['products']?.aggregate(
    [
      {
        $lookup: {
          from: field.relationTo,
          localField: filterConfig.field,
          foreignField: '_id',
          as: 'items',
        },
      },
      {
        $unwind: '$items',
      },
      {
        $group: {
          _id: null,
          availableItems: { $addToSet: '$items._id' },
        },
      },
      {
        $project: {
          _id: 0,
          availableItems: 1,
        },
      },
    ],
  );

Now, I want to apply a custom where filter to my aggregatedItems results. This filter can have multiple depths, ANDs, ORs, etc.

Do you have any idea how I would have to structure that?

azure ruin
#

The productModel.buildQuery builds the $match part. So I guess you could use this further in your pipeline to fetch products with the id that you have and apply the matching.

But what is the purpose of this query, you just return items which have a certain condition set. Why could you not use a regular where ?

spice thistle
#

@azure ruin Yes, you're absolutely right. I have a filter page on the frontend where you can filter through a variety of properties, such as colors, lengths, types, etc.
I only want to display the colors, lengths, and types that are available based on the currently applied filters.

Currently I fetch all products with the where filter, check which colors are available, and return them. But this is a bit hacky, and it probably won't work with a larger number of products.

btw: Is there also a way to convert the payload sort string into a MongoDB $sort config?

azure ruin
#

@spice thistle Yes, this is the exact problem that I have used this for as well.

I splitted it into different queries to make it easier:

  1. one query to fetch the product based on current selection, this is just simple with where queries.
  2. a query for each filter to get the values based on the current selection.

So for the second query I basically do a $match on products with the same where as in 1, which is converted to proper mongoose syntax with the buildQuery.
Then I group on the attribute (and do a count so I can preview in the frontend how many products will be shown if you select this attribute).
And then I return that result. So I only show the filters applicable for the current selection.

So basically $match, $group and $project

I have one of these queries for each filter. Maybe you can combine multiple filters into one, but I found that too complex for my scenario. Also I run them in parrallell with tanstack query on the frontend.

Regarding sort I do not know of anything, but sorting should be simple as it's just the name of the fields.