MongoDB聚合查询中实现$lookup无匹配数据时返回空数组的需求咨询

Hey there! Let's get this sorted out for you. The issue with your current aggregation is that after using $lookup, the $unwind stage drops any documents where the staticData array is empty (which happens when there's no matching static entry). Then the following $match filters out any remaining entries that don't have the static data type—so you end up losing all the original documents that don't have a matching static record.

Here's a cleaner, more efficient way to adjust your query to keep all original documents, returning an empty staticData array when there's no matching static entry:

db.getCollection('equityprice_input').aggregate([
  {
    '$match': {
      'mrsBusinessDate': '2022-05-05',
      'instrument': 'other',
      'sourceSystem': 'bloomberg',
      'mrsTime': '17:00:00',
      'dataType': 'price'
    }
  },
  {
    '$lookup': {
      'from': 'equityprice_input',
      'localField': 'data.securities',
      'foreignField': 'data.securities',
      'as': 'staticData'
    }
  },
  // Filter the staticData array to only keep entries with dataType = 'static'
  {
    '$addFields': {
      'staticData': {
        '$filter': {
          'input': '$staticData',
          'cond': { '$eq': ['$$this.dataType', 'static'] }
        }
      }
    }
  },
  {
    '$project': {
      '_id': 1,
      'mrsBusinessDate': 1,
      'mrsTime': 1,
      'category': 1,
      'instrument': 1,
      'label': 1,
      'sourceSystem': 1,
      'mrsDescription': 1,
      'data': 1,
      'staticData.data': 1
    }
  }
])

What changed? #

• We replaced the $unwind + $match combo with a $addFields stage using $filter. This directly narrows down the staticData array to only include entries where dataType is static. If there are no matches, the array stays empty instead of getting dropped.
• The $project stage is simplified now because we've already filtered out unwanted staticData entries, so we don't need to include staticData.dataType anymore.

If you're curious about another approach (though less efficient), you could use $unwind with the preserveNullAndEmptyArrays option and then $group to re-assemble the documents—but the first method is better because it avoids the overhead of unwinding and re-grouping large datasets.

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