MongoDB筛选serviceCountry为US的gfcidGlobals相关记录

要实现仅返回serviceCountry等于US的gfcidGlobals记录及其对应对象，可以使用MongoDB的聚合管道处理嵌套的键值对结构，具体查询如下：

db.collection.aggregate([
  {
    $project: {
      gfcidGlobals: { $objectToArray: "$gfcidGlobals" }
    }
  },
  { $unwind: "$gfcidGlobals" },
  { $unwind: "$gfcidGlobals.v" },
  {
    $match: {
      "gfcidGlobals.v.serviceCountry": "US"
    }
  },
  {
    $group: {
      _id: "$_id",
      gfcidGlobals: {
        $push: {
          k: "$gfcidGlobals.k",
          v: "$gfcidGlobals.v"
        }
      }
    }
  },
  {
    $project: {
      gfcidGlobals: { $arrayToObject: "$gfcidGlobals" }
    }
  }
])

代码说明 #

• $objectToArray: 将gfcidGlobals的键值对转为[{k: "gfcid值", v: 对应数组}, ...]格式，方便后续遍历
• $unwind: 两次展开，分别拆解gfcid对应的数组和数组内的每个对象
• $match: 精准筛选出serviceCountry为US的对象
• $group: 按文档_id分组，把符合条件的对象重新按gfcid聚合
• $arrayToObject: 将分组后的数组转回原键值对结构

预期结果 #

执行上述聚合后，返回结果如下：

[
  {
    "_id": {
      "$oid": "648c0875a25a3a13f1e3e7cf"
    },
    "gfcidGlobals": {
      "789456489": {
        "className": "test123",
        "gfcid": "789456489",
        "companyName": "ABC BRANCH",
        "serviceCountry": "US",
        "kwtPolicyDueDate": {
          "$date": {
            "$numberLong": "1699833600000"
          }
        },
        "kycLobOwner": "BSU",
        "clientType": "BANK"
      }
    }
  }
]

如果需要保留gfcid对应的数组格式（即使仅一个元素），可调整$group阶段的$push逻辑，将v包装为数组：

// 调整后的$group阶段
{
  $group: {
    _id: "$_id",
    gfcidGlobals: {
      $push: {
        k: "$gfcidGlobals.k",
        v: ["$gfcidGlobals.v"]
      }
    }
  }
}

调整后结果会保持原数组结构：

[
  {
    "_id": {
      "$oid": "648c0875a25a3a13f1e3e7cf"
    },
    "gfcidGlobals": {
      "789456489": [
        {
          "className": "test123",
          "gfcid": "789456489",
          "companyName": "ABC BRANCH",
          "serviceCountry": "US",
          "kwtPolicyDueDate": {
            "$date": {
              "$numberLong": "1699833600000"
            }
          },
          "kycLobOwner": "BSU",
          "clientType": "BANK"
        }
      ]
    }
  }
]

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