Get filtered products and price range (min and max price)

Question

I have a "products" collection that looks like this:

[
    { name: 'red mug', category: 'mug', price: 100 },
    { name: 'blue mug', category: 'mug', price: 150 },
    { name: 'large glass', category: 'glass', price: 200 },
    { name: 'brown carpet', category: 'carpet', price: 50 },
    { name: 'small glass', category: 'glass', price: 75 }
]

So after filtering (by chosen categories), I would like to get the filtered documents and the minimum and maximum price of those filtered documents.

For example: If a user were to filter by categories mug and glass, the expected output should be:

[
    { name: 'red mug', category: 'mug', price: 100 },
    { name: 'blue mug', category: 'mug', price: 150 },
    { name: 'large glass', category: 'glass', price: 200 },
    { name: 'small glass', category: 'glass', price: 75 },
    { minPrice: 75, maxPrice: 200 }
]

Is there any way to achieve something like this by just using MongoDB (without processing it with code)?

P.S. I would like them sorted by createdAt (from newest to oldest).

Answer 1

From my perspective, would be great if both results (filtered products and min/max of filtered products) could be separated (query) although both datasets have the same filter criteria.

The reason why should separate into different results as both are for different purposes and to make the JSON object(s) consistent (same field keys). You are required to implement additional logic to avoid showing the last item as the product.

Also, it may degrade the query performance as additional stages are required to combine, unwind, and format data (refer to the last query) in order to achieve the expected result.

db.collection.aggregate([
  {
    $match: {
      category: {
        $in: [
          "mug",
          "glass"
        ]
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  }
])

db.collection.aggregate([
  {
    $match: {
      category: {
        $in: [
          "mug",
          "glass"
        ]
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  },
  {
    $group: {
      _id: null,
      minPrice: {
        $min: "$price"
      },
      maxPrice: {
        $max: "$price"
      }
    }
  },
  {
    $unset: "_id"
  }
])

---

If you are still keen to combine both results as a single query/result, you need the $facet stage.

1. $match - Filter documents.

2. $sort - Sort by createdAt descending.

3. $facet - Process dataset with multiple pipelines for products and summary (Get the min and max of filtered products).

4. $project - Combine both products and summary arrays into one.

5. $unwind - Deconstruct the productsAndSummary array into multiple documents.

6. $replaceWith - Replace output document with productsAndSummary object.

db.collection.aggregate([
  {
    $match: {
      category: {
        $in: [
          "mug",
          "glass"
        ]
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  },
  {
    $facet: {
      products: [],
      summary: [
        {
          $group: {
            _id: null,
            minPrice: {
              $min: "$price"
            },
            maxPrice: {
              $max: "$price"
            }
          }
        },
        {
          $unset: "_id"
        }
      ]
    }
  },
  {
    $project: {
      productsAndSummary: {
        $concatArrays: [
          "$products",
          "$summary"
        ]
      }
    }
  },
  {
    $unwind: "$productsAndSummary"
  },
  {
    $replaceWith: "$productsAndSummary"
  }
])

Demo @ Mongo Playground