mongodb - Mongo合并结果

Question

大家好，我有 3 个具有 dbref 的集合，我想在其中安装类似于下图所示的结果，但我无法使用任何聚合获得满足感，你能指出更好的方法吗？

冷却：

• data1 - 主要 data_reference1
• 在 data1 data_reference2 中引用
• 在 data_reference2 中引用

/* EXAMPLE RECORD data1 */
{
    "_id" : ObjectId("604fafd443487df824aeca61"),
    "name" : "doc1",
    "reference_data1_list" : [ 
        {
            "data_reference" : ObjectId("604fb00643487df824aeca91"),
            "user_add" : "USER01"
        }, 
        {
            "data_reference" : ObjectId("604fb00d43487df824aeca9b"),
            "user_add" : "USER01"
        }, 
        {
            "data_reference" : ObjectId("604fb01743487df824aecaa7"),
            "user_add" : "USER02"
        }
    ]
}

/* EXAMPLE RECORDS data_reference1 */
/* 1 */
{
    "_id" : ObjectId("604fb00643487df824aeca91"),
    "reference_desc" : "Test Referenced Field 01",
    "reference_data2" : ObjectId("604fb24743487df824aecd14")
}

/* 2 */
{
    "_id" : ObjectId("604fb00d43487df824aeca9b"),
    "reference_desc" : "Test Referenced Field 02",
    "reference_data2" : ObjectId("604fb24743487df824aecd14")
}

/* 3 */
{
    "_id" : ObjectId("604fb01743487df824aecaa7"),
    "reference_desc" : "Test Referenced Field 03",
    "reference_data2" : ObjectId("604fb25743487df824aecd27")
}

/* EXAMPLE RECORDS data_reference2 */
/* 1 */
{
    "_id" : ObjectId("604fb24743487df824aecd14"),
    "name" : "Student"
}

/* 2 */
{
    "_id" : ObjectId("604fb25743487df824aecd27"),
    "name" : "Theacher"
}

期望的结果：

{
  "_id": ObjectId("604fafd443487df824aeca61"),
  "name": "doc1",
  "reference_data1_list": [
    {
      "_id": ObjectId("604fb00643487df824aeca91"),
      "reference_desc": "Test Referenced Field 01",
      "reference_data2": {
        "_id": ObjectId("604fb24743487df824aecd14"),
        "name": "Student"
      }
    },
    {
      "_id": ObjectId("604fb00d43487df824aeca9b"),
      "reference_desc": "Test Referenced Field 02",
      "reference_data2": {
        "_id": ObjectId("604fb24743487df824aecd14"),
        "name": "Student"
      }
    },
    {
      "_id": "604fb01743487df824aecaa7",
      "reference_desc": "Test Referenced Field 03",
      "reference_data2": {
        "_id": ObjectId("604fb25743487df824aecd27"),
        "name": "Theacher"
      }
    }
  ]
}

我对 mongo 没有太多的技能，也没有找到可以在示例中使用的任何类似示例。

标签： mongodb

Answer 1

试试这个查询：

db.data1.aggregate([
    { $unwind: "$reference_data1_list" },
    {
        $lookup: {
            from: "data_reference1",
            let: {
                data_ref: "$reference_data1_list.data_reference",
                user_add: "$reference_data1_list.user_add"
            },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$data_ref"] }
                    }
                },
                {
                    $addFields: { user_add: "$$user_add" }
                }
            ],
            as: "reference_data1_list"
        }
    },
    { $unwind: "$reference_data1_list" },
    {
        $lookup: {
            from: "data_reference2",
            localField: "reference_data1_list.reference_data2",
            foreignField: "_id",
            as: "reference_data1_list.reference_data2"
        }
    },
    { $unwind: "$reference_data1_list.reference_data2" },
    {
        $group: {
            _id: "$_id",
            name: { $first: "$name" },
            reference_data1_list: { $push: "$reference_data1_list" }
        }
    }
]);

输出

{
    "_id" : ObjectId("604fafd443487df824aeca61"),
    "name" : "doc1",
    "reference_data1_list" : [
        {
            "_id" : ObjectId("604fb00643487df824aeca91"),
            "reference_desc" : "Test Referenced Field 01",
            "reference_data2" : {
                "_id" : ObjectId("604fb24743487df824aecd14"),
                "name" : "Student"
            },
            "user_add" : "USER01"
        },
        {
            "_id" : ObjectId("604fb00d43487df824aeca9b"),
            "reference_desc" : "Test Referenced Field 02",
            "reference_data2" : {
                "_id" : ObjectId("604fb24743487df824aecd14"),
                "name" : "Student"
            },
            "user_add" : "USER01"
        },
        {
            "_id" : ObjectId("604fb01743487df824aecaa7"),
            "reference_desc" : "Test Referenced Field 03",
            "reference_data2" : {
                "_id" : ObjectId("604fb25743487df824aecd27"),
                "name" : "Theacher"
            },
            "user_add" : "USER02"
        }
    ]
}