MongoDB MQL跨集合查询问题：无法在NowPlayingInfo子数组显示数据

问题背景 #

我有两个MongoDB集合：PastLocation和NowPlaying，示例数据如下：

PastLocation示例数据 #

{"UUID": "c19c7dd1c7a4f2ca","timestamp":"2023-02-01T22:15:02.000+00:00","location": {"coordinates": [00.000,00.953512],"type": "Point"}}

NowPlaying示例数据 #

{"artist": "Carrie Underwood","song": "Garden","type": "S","timeplay":"2023-02-01T22:15:32.000+00:00","history": ["c19c7dd1c7a4f2ca"]}

遇到的问题 #

执行下面的PastLocation.aggregate聚合查询时，NowPlayingInfo子数组始终是空的，没法把NowPlaying的数据关联进来：

[
  {
    $match: {
      UUID: "c19c7dd1c7a4f2ca",
      timestamp: { $gte: ISODate("2023-02-01T22:15:02.000+00:00") },
    },
  },
  {
    $lookup: {
      from: "NowPlaying",
      let: {
        uuid: "$UUID",
        timestamp: {
          $toDate: { $multiply: [ { $toLong: "$timestamp" }, 1 ] },
        },
      },
      pipeline: [
        {
          $match: {
            history: { $in: ["$$uuid"] },
            $expr: {
              $and: [
                {
                  $gte: [
                    "$timeplay",
                    { $subtract: [ { $toLong: "$$timestamp" }, 180000 ] },
                  ],
                },
                {
                  $lte: [
                    "$timeplay",
                    { $add: [ { $toLong: "$$timestamp" }, 180000 ] },
                  ],
                },
              ],
            },
          },
        },
        {
          $project: {
            _id: 1,
            song: 1,
            artist: 1,
            type: 1,
          },
        },
      ],
      as: "NowPlayingInfo",
    },
  },
  {
    $addFields: {
      NowPlayingInfo: "$NowPlayingInfo",
    },
  },
]

问题原因及修复方案 #

核心问题点 #

1. 时间字段类型转换错误：PastLocation里的timestamp是字符串，直接用$toLong转换会失败，导致后续时间范围计算完全错误，匹配不到任何NowPlaying数据。
2. 冗余的$addFields阶段：这个阶段完全没用，$lookup已经把结果写到NowPlayingInfo字段里了，属于多此一举。
3. history匹配逻辑冗余：用history: "$$uuid"就能匹配数组中包含该UUID的文档，不需要$in: ["$$uuid"]。

修复后的查询代码 #

[
  {
    $match: {
      UUID: "c19c7dd1c7a4f2ca",
      timestamp: { $gte: ISODate("2023-02-01T22:15:02.000+00:00") },
    },
  },
  {
    $lookup: {
      from: "NowPlaying",
      let: {
        uuid: "$UUID",
        // 先把字符串转成Date，再转成时间戳，确保类型转换正确
        locationTs: { $toLong: { $toDate: "$timestamp" } }
      },
      pipeline: [
        {
          $match: {
            history: "$$uuid",
            $expr: {
              $and: [
                { $gte: [ { $toLong: "$timeplay" }, { $subtract: [ "$$locationTs", 180000 ] } ] },
                { $lte: [ { $toLong: "$timeplay" }, { $add: [ "$$locationTs", 180000 ] } ] }
              ]
            }
          }
        },
        {
          $project: {
            _id: 1,
            song: 1,
            artist: 1,
            type: 1
          }
        }
      ],
      as: "NowPlayingInfo"
    }
  }
]

调整说明 #

• 修正时间转换顺序：先将字符串timestamp转为Date类型，再转成时间戳，避免类型转换错误导致的时间范围计算失效。
• 简化history匹配逻辑，代码更简洁高效。
• 删除无用的$addFields阶段，减少查询开销。

额外优化建议 #

如果这个查询会频繁执行，建议给以下字段创建索引：

• PastLocation集合：UUID、timestamp
• NowPlaying集合：history、timeplay
  能大幅提升查询速度。

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