无需$sort聚合指定字段含$max值的文档及MongoDB商品最新价格查询

Let's break down your two questions with practical, efficient aggregation pipelines that fit your requirements.

1. Retrieve All Documents with the Maximum Value of a Specified Field (Without Using $sort) #

If you need to fetch every document where a field holds the global maximum value, and you want to avoid $sort entirely, here's a reliable approach using $group and $lookup:

// Replace "your_collection" with your actual collection name, "target_field" with your field
db.your_collection.aggregate([
  // Step 1: Calculate the global maximum value of the target field
  {
    $group: {
      _id: null,
      max_value: { $max: "$target_field" }
    }
  },
  // Step 2: Join back to the original collection to find all matching documents
  {
    $lookup: {
      from: "your_collection",
      let: { max_val: "$max_value" },
      pipeline: [
        { $match: { $expr: { $eq: [ "$target_field", "$$max_val" ] } } }
      ],
      as: "max_documents"
    }
  },
  // Step 3: Unwind the array and set the matching documents as the root
  { $unwind: "$max_documents" },
  { $replaceRoot: { newRoot: "$max_documents" } }
])

How this works: #

• First, we use $group with _id: null to compute the global maximum value of your target field across all documents.
• Next, $lookup lets us query the original collection again, filtering only documents where the target field equals that maximum value.
• Finally, we unwind the resulting array and replace the root to get a clean list of all documents with the maximum value. No $sort required!

2. Get the Latest Price for Each Item (Handling Missing Date Entries) #

For your daily price collection, where some items don't have recent date records, we can build a pipeline that groups by item, finds the most recent date for each, and fetches the corresponding price. Here are two solid options:

Option 1: Efficient Lookup Approach #

This method is great for performance since it minimizes data processing by first finding the latest date per item, then fetching only the relevant price document:

db.prices.aggregate([
  // Step 1: Find the latest date for each item
  {
    $group: {
      _id: "$_id.item",
      latest_date: { $max: "$_id.date" }
    }
  },
  // Step 2: Join back to get the price for that item and date
  {
    $lookup: {
      from: "prices",
      let: {
        item_name: "$_id",
        latest_date: "$latest_date"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: [ "$_id.item", "$$item_name" ] },
                { $eq: [ "$_id.date", "$$latest_date" ] }
              ]
            }
          }
        },
        { $project: { _id: 0, price: "$price" } }
      ],
      as: "latest_price_info"
    }
  },
  // Step 3: Clean up the output format
  { $unwind: "$latest_price_info" },
  {
    $project: {
      _id: 0,
      item: "$_id",
      latest_date: "$latest_date",
      latest_price: "$latest_price_info.price"
    }
  }
])

Option 2: In-Line Sorting (For When You Need History) #

If you want to retain the full price history for each item alongside the latest price, use $sortArray to sort the history and pick the first entry:

db.prices.aggregate([
  // Step 1: Group items and collect all price-date entries
  {
    $group: {
      _id: "$_id.item",
      price_history: {
        $push: {
          date: "$_id.date",
          price: "$price"
        }
      }
    }
  },
  // Step 2: Sort history by date descending and pick the latest entry
  {
    $set: {
      latest_price_entry: {
        $first: {
          $sortArray: {
            input: "$price_history",
            sortBy: { date: -1 }
          }
        }
      }
    }
  },
  // Step 3: Format the output
  {
    $project: {
      _id: 0,
      item: "$_id",
      latest_date: "$latest_price_entry.date",
      latest_price: "$latest_price_entry.price",
      price_history: "$price_history" // Optional: keep full history if needed
    }
  }
])

Key Notes: #

• Both methods handle missing date entries automatically—since we're using $max on the date field, we'll always get the most recent date available for each item, even if there are gaps.
• Choose Option 1 for faster performance if you don't need the full price history; Option 2 is better if you want to keep historical data handy.

内容的提问来源于stack exchange，提问作者Oleg