主页 / coding / 问题 / 79174230
Accepted
Prabhjot
Asked: 2024-11-10 14:00:04 +0800 CST

MongoDB 使用聚合创建嵌套的自上而下的层次结构

  • 772

我正在尝试使用 $graphLookup 在 MongoDB 中的单个集合上创建嵌套聚合，其键为dependentOn和usedBy，它们是对象数据类型，用于存储类别数组和_id字段。

输入收集样本（关系）：

// Doc 1:

{
  "_id": "p_commerce_metrics_dtst_v1",
  "category": "dataset",
  "dependentOn": {
    "metrics": [
      "net_booking_count_v1"
    ]
}

// Doc 2:

{
  "_id": "net_booking_count_v1",
  "category": "metric",
  "dependentOn": {
    "metrics": [
      "cancelled_booking_count_v1",
      "gross_booking_count_v1"
    ]
  }
}


// Doc 3:

{
  "_id": "cancelled_booking_count_v1",
  "category": "metric",
  "dependentOn": {
    "measures": [
      "hb_cancel_measure_v1"
    ]
  }
}

// Doc 4:

{
  "_id": "gross_booking_count_v1",
  "category": "metric",
  "dependentOn": {
    "measures": [
      "hb_booking_measure_v1"
    ]
  }
}

// Doc 5 (Not dependentOn any other document _id. Dead End):

{
  "_id": "hb_cancel_measure_v1",
  "category": "measure",
  "usedBy": {
    "metrics": [
      "cancelled_booking_count_v1",
      "more_metrics"
    ]
  }
}


// Doc 6 (Not dependentOn any other document _id. Dead End):

{
  "_id": "hb_booking_measure_v1",
  "category": "measure",
  "usedBy": {
    "metrics": [
      "gross_booking_count_v1",
      "more_metrics"
    ]
  }
}

预期输出：

// Fetch entire dependentOn hierarchy for p_commerce_metrics_dtst_v1

[
  {
    "name_version": "p_commerce_metrics_dtst_v1",
    "category": "dataset",
    "dependent_on": {
      "metrics": [
        "net_booking_count_v1",
        "cancelled_booking_count_v1",
        "gross_booking_count_v1"
      ],
      "measures": [
        "hb_cancel_measure_v1",
        "hb_booking_measure_v1"
      ]
    }
  }
]

我尝试使用以下聚合查询，但无法获得所需的输出。

[
    { $match: {
        _id: "p_commerce_metrics_dtst_v1"
    }},
    { $graphLookup: {
        from: "relations",
        startWith: "$dependentOn",
        connectFromField: "dependentOn",
        connectToField: "_id",
        depthField: "depth",
        as: "dependentOn"
    }}
]

如何使用聚合查询实现所需的输出？

mongodb

1 个回答

  1. Best Answer

    您$graphLookup覆盖了 的原始值dependentOn。只需为查找结果使用另一个名称，然后稍后取消设置即可。剩下的只是一些数据整理，以使查找结果扁平化。

    db.relations.aggregate([
  {
    $match: {
      _id: "p_commerce_metrics_dtst_v1"
    }
  },
  {
    $graphLookup: {
      from: "relations",
      startWith: "$dependentOn.metrics",
      connectFromField: "dependentOn.metrics",
      connectToField: "_id",
      depthField: "depth",
      as: "dependentOnMetrics"
    }
  },
  {
    "$set": {
      "dependentOn": {
        "metrics": {
          "$setUnion": [
            {
              "$ifNull": [
                "$dependentOn.metrics",
                []
              ]
            },
            {
              "$reduce": {
                "input": "$dependentOnMetrics.dependentOn.metrics",
                "initialValue": [],
                "in": {
                  "$setUnion": [
                    "$$value",
                    "$$this"
                  ]
                }
              }
            }
          ]
        },
        "measures": {
          "$setUnion": [
            {
              $ifNull: [
                "$dependentOn.measures",
                []
              ]
            },
            {
              "$reduce": {
                "input": "$dependentOnMetrics.dependentOn.measures",
                "initialValue": [],
                "in": {
                  "$setUnion": [
                    "$$value",
                    "$$this"
                  ]
                }
              }
            }
          ]
        }
      }
    }
  },
  {
    "$unset": "dependentOnMetrics"
  }
])

    蒙戈游乐场

    • 1

相关问题