mongodb - Mongo Query - 过滤嵌套数组并返回不包含特定字段的文档

Question

我有以下架构:

{
  "_id": 0,
  "games": {
     "gamesList": [
        {
          "franchiseName": "Tekken",
          "genre": "Fighting",
          "gamesInFranchise": [
              {
                 "name": "Tekken 7",
                 "releaseDate": "03/18/2015",
                 "co-op": true,
                 "platforms": [ 
                     "playstation 3",
                     "xbox 360"
                 ]
              },
              {
                 "name": "Tekken 6",
                 "releaseDate": "11/26/2007",
                 "co-op": true
              },
              {
                 "name": "Tekken 5",
                 "releaseDate": "01/01/2004",
                 "co-op": true
              },
           ]
        },
        .................
     ]
  }
}

我想根据不具有“平台”属性的特定“_id”过滤文档。所以本质上，理想的结果应该是这样的:

{
  "_id": 0,
  "games": {
     "gamesList": [
        {
          "franchiseName": "Tekken",
          "genre": "Fighting",
          "gamesInFranchise": [
              {
                 "name": "Tekken 6",
                 "releaseDate": "11/26/2007",
                 "co-op": true
              },
              {
                 "name": "Tekken 5",
                 "releaseDate": "01/01/2004",
                 "co-op": true
              }
           ]
        }
     ]
  }
}

我尝试将聚合专门用于投影/过滤器查询，但我似乎无法到达“平台”来检查它是否存在。

Answer 1

问题是在到达平台之前您有多个嵌入式数组。你必须使用聚合框架来处理它们。

这是查询:

db.collection.aggregate([
  {
    $match: {
      _id: 0
    }
  },
  {
    $addFields: {
      "games.gamesList": {
        $map: {
          input: "$games.gamesList",
          as: "franchise",
          in: {
            "franchiseName": "$$franchise.franchiseName",
            "genre": "$$franchise.genre",
            "gamesInFranchise": {
              $filter: {
                input: "$$franchise.gamesInFranchise",
                as: "gameInFranchise",
                cond: {
                  $eq: [
                    null,
                    {
                      $ifNull: [
                        "$$gameInFranchise.platforms",
                        null
                      ]
                    }
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

我使用 $addFields 来保证其他字段的安全。

Note that you have to describe whole element in th 'in' field of '$map' operator.

第一个$map运算符是相对于第一级数组而言的，$filter到第二级数组。

$ifNull是检查元素是否存在或返回某些内容(此处设置为 null)的技巧。通过检查是否与 null 相等(或不相等)，可以检查元素是否存在

You can test the query here