经过一些聚合阶段后，我希望其他集合字段的值为空白/空的展开结果

After some aggregation stages, I want the unwind result with other collections fields with their values as blank/empty

提问人：Hemant Joshi 提问时间：11/16/2023 最后编辑：Hemant Joshi 更新时间：11/16/2023 访问量：18

问：

我是 usign mongo 版本为 v5.0.19

我有 2 个集合，其中包含以下数据：

系列 1 - external_S_P_FLAT_main_api：

[
    {
        "_id": {
            "$oid": "654c6a594d0867aef588674d"
        },
        "data.pricing.material": "TG11",
        "data.pricing.controlling_area": "AJ00",
        "data.pricing.cost_center": "DE0000012"
    },
    {
        "_id": {
            "$oid": "654c6a594d0867aef588674e"
        },
        "data.pricing.material": "TG12",
        "data.pricing.controlling_area": "AJ00",
        "data.pricing.cost_center": "DE000002"
    },
    {
        "_id": {
            "$oid": "654c6a594d0867aef588674f"
        },
        "data.pricing.material": "TG14",
        "data.pricing.controlling_area": "AJ00",
        "data.pricing.cost_center": "DE000003"
    },
    {
        "_id": {
            "$oid": "654c6a594d0867aef5886750"
        },
        "data.pricing.material": "TG2341",
        "data.pricing.controlling_area": "AJ00",
        "data.pricing.cost_center": "DE000004"
    }
]

系列 2 - external_S_C_FLAT_main_api：

[
    {
        "_id": {
            "$oid": "654c6a594d0867aef5886751"
        },
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000001",
        "data.costcenter.valid_from_date": "2023-09-12",
        "data.costcenter.long_description": "CC DE000001 - 3rd"
    },
    {
        "_id": {
            "$oid": "654c6a594d0867aef5886752"
        },
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.valid_from_date": "2022-03-02",
        "data.costcenter.long_description": "CC DE000002 - 1st"
    },
    {
        "_id": {
            "$oid": "654c6a594d0867aef5886753"
        },
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.valid_from_date": "2023-10-25",
        "data.costcenter.long_description": "CC DE000003 - 1st"
    },
    {
        "_id": {
            "$oid": "654c6a594d0867aef5886754"
        },
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.valid_from_date": "2023-10-25",
        "data.costcenter.long_description": "CC DE000004 - 2nd"
    }
]

我正在执行以下查询：

db.external_S_P_FLAT_main_api.aggregate([
    {
      "$addFields": {
        "external_S_P_FLAT_main_api_data.pricing.controlling_area": "$data.pricing.controlling_area"
      }
    },
    {
      "$addFields": {
        "external_S_P_FLAT_main_api_data.pricing.cost_center": "$data.pricing.cost_center"
      }
    },
    {
      "$addFields": {
        "external_S_P_FLAT_main_api_data.pricing.cost_center": "$data.pricing.cost_center"
      }
    },
    {
      "$lookup": {
        from: "external_S_C_FLAT_main_api",
        let: {
          let_data__pricing__cost_center: {
            $getField: "data.pricing.cost_center"
          }
        },
        pipeline: [
          {
            "$match": {
              "$expr": {
                "$and": [
                  {
                    "$eq": [
                      {
                        $getField: "data.costcenter.cost_center"
                      },
                      "$$let_data__pricing__cost_center"
                    ]
                  }
                ]
              }
            }
          }
        ],
        as: "from_external_S_C_FLAT_main_api"
      }
    },
    {
      "$unwind": {
        path: "$from_external_S_C_FLAT_main_api",
        preserveNullAndEmptyArrays: true,
        // includeArrayIndex: ""
        
      }
    },
    {
      "$replaceRoot": {
        newRoot: {
          "$mergeObjects": [
            "$from_external_S_C_FLAT_main_api",
            "$$ROOT"
          ]
        }
      }
    },
    {
      "$project": {
        _id: 0,
        "external_S_P_FLAT_main_api_data.pricing.controlling_area": 0,
        "external_S_P_FLAT_main_api_data.pricing.cost_center": 0,
        // from_external_S_C_FLAT_main_api: 0
      }
    }
  ])

结果如下：

[
    {
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE0000012",
      "data.pricing.material": "TG11",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      }
    },
    {
      "data.costcenter.controlling_area": "AJ00",
      "data.costcenter.cost_center": "DE000002",
      "data.costcenter.long_description": "CC DE000002 - 1st",
      "data.costcenter.valid_from_date": "2022-03-02",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE000002",
      "data.pricing.material": "TG12",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      }
    },
    {
      "data.costcenter.controlling_area": "AJ00",
      "data.costcenter.cost_center": "DE000003",
      "data.costcenter.long_description": "CC DE000003 - 1st",
      "data.costcenter.valid_from_date": "2023-10-25",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE000003",
      "data.pricing.material": "TG14",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    },
    {
      "data.costcenter.controlling_area": "AJ00",
      "data.costcenter.cost_center": "DE000004",
      "data.costcenter.long_description": "CC DE000004 - 2nd",
      "data.costcenter.valid_from_date": "2023-10-25",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE000004",
      "data.pricing.material": "TG2341",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    }
  ]

结果数组的第一个对象没有任何用于集合 2 的数据。

我希望它填充具有空白/空值的键。

预期结果如下：

[
    {
      "data.costcenter.controlling_area": "",
      "data.costcenter.cost_center": "",
      "data.costcenter.long_description": "",
      "data.costcenter.valid_from_date": "",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE0000012",
      "data.pricing.material": "TG11",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": [
        {
            "_id": "",
            "data.costcenter.controlling_area": "",
            "data.costcenter.cost_center": "",
            "data.costcenter.long_description": "",
            "data.costcenter.valid_from_date": ""
        }
      ]
    },
    {
      "data.costcenter.controlling_area": "AJ00",
      "data.costcenter.cost_center": "DE000002",
      "data.costcenter.long_description": "CC DE000002 - 1st",
      "data.costcenter.valid_from_date": "2022-03-02",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE000002",
      "data.pricing.material": "TG12",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": {
        "_id": ObjectId("654c6a594d0867aef5886752"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000002",
        "data.costcenter.long_description": "CC DE000002 - 1st",
        "data.costcenter.valid_from_date": "2022-03-02"
      }
    },
    {
      "data.costcenter.controlling_area": "AJ00",
      "data.costcenter.cost_center": "DE000003",
      "data.costcenter.long_description": "CC DE000003 - 1st",
      "data.costcenter.valid_from_date": "2023-10-25",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE000003",
      "data.pricing.material": "TG14",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": {
        "_id": ObjectId("654c6a594d0867aef5886753"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000003",
        "data.costcenter.long_description": "CC DE000003 - 1st",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    },
    {
      "data.costcenter.controlling_area": "AJ00",
      "data.costcenter.cost_center": "DE000004",
      "data.costcenter.long_description": "CC DE000004 - 2nd",
      "data.costcenter.valid_from_date": "2023-10-25",
      "data.pricing.controlling_area": "AJ00",
      "data.pricing.cost_center": "DE000004",
      "data.pricing.material": "TG2341",
      "external_S_P_FLAT_main_api_data": {
        "pricing": {}
      },
      "from_external_S_C_FLAT_main_api": {
        "_id": ObjectId("654c6a594d0867aef5886754"),
        "data.costcenter.controlling_area": "AJ00",
        "data.costcenter.cost_center": "DE000004",
        "data.costcenter.long_description": "CC DE000004 - 2nd",
        "data.costcenter.valid_from_date": "2023-10-25"
      }
    }
  ]

我已经通过添加includeArrayIndex来尝试打击部分

{
      "$unwind": {
        path: "$from_external_S_C_FLAT_main_api",
        preserveNullAndEmptyArrays: true,
        // includeArrayIndex: ""
        
      }
    },

下面是同一 https://mongoplayground.net/p/whJzRZLf_bl 的游乐场

mongodb-query 聚合框架

评论

0赞 ray 11/16/2023
字段路径中的点会产生很多问题。请考虑使用字段路径中不包含点的名称约定

答： 暂无答案

上一个：MongoServerError：无效的$set ：： 由 ：： 引起，表达式$in正好接受 2 个参数。1 个被传入

下一个：MongoDB查询转换文档