json - 在 Mongodb 中使用聚合、查找和管道进行查询

Question

我必须管理带有评论的墙，每面墙都有很多父评论，每个父评论都有子评论。

我的收藏墙是这样的

 groupId : {type: Schema.Types.ObjectId, ref: 'groups', unique: true},
    comments : [{
        commentId : {type: Schema.Types.ObjectId, ref: 'comments'},
        user : {type: Schema.Types.ObjectId, ref: 'users'},
    }],

而收藏评论是这样的

text : String,
parentCommentId : {type: Schema.Types.ObjectId, ref: 'comments', default : null},

我想通过父评论显示我的墙，每个子评论在其父评论下。

我试过这个查询，但它没有返回任何结果

db.getCollection('walls').aggregate([
        {$match: {groupId: ObjectId("5e8c5caa75b1cd342a1175eb")}},
        {
            "$lookup": {
                from: "comments",
                let: { item: "$comments.commentId" },
                pipeline: [
                    { $match:
                        { $expr: { $eq: [ "$parentCommentId",  "$$item" ] }


                        }
                    },
                    { $project: {
                        "_id": 1,
                        "parentCommentId": 1,
                        "text": 1                       
                    } }
                ],
                as: "comments"
            }
        },
        {
            $project: {
                groupId: 1,
                "comments":1,
                date: 1
            }
        }
    ])

墙上的数据

{
    "_id" : ObjectId("5e95b4b49d3e303d667a8b71"),
    "groupId" : ObjectId("5e8c5caa75b1cd342a1175eb"),
    "comments" : [ 
        {
            "_id" : ObjectId("5e95b4b49d3e303d667a8b72"),
            "commentId" : ObjectId("5e95b4b49d3e303d667a8b70")
        }, 
        {
            "_id" : ObjectId("5e95b4ef80ae1244693aa857"),
            "commentId" : ObjectId("5e95b4ef80ae1244693aa856")
        }, 
        {
            "_id" : ObjectId("5e95b51080ae1244693aa859"),
            "commentId" : ObjectId("5e95b51080ae1244693aa858")
        }, 
        {
            "_id" : ObjectId("5e95b51d80ae1244693aa85b"),
            "commentId" : ObjectId("5e95b51d80ae1244693aa85a")
        },
        {
           "_id" : ObjectId("5e95b53580ae1244693aa85e"),
           "commentId" : ObjectId("5e95b53580ae1244693aa85c")
        }
    ],

}

评论中的数据

{
    "_id" : ObjectId("5e95b4b49d3e303d667a8b70"),
    "parentCommentId" : null,
    "text" : "Hello parent 1"
}

{
    "_id" : ObjectId("5e95b4ef80ae1244693aa856"),
    "parentCommentId" : null,
    "text" : "Hello parent 2",
    "date" : ISODate("2020-04-14T13:04:47.860Z")
}

{
    "_id" : ObjectId("5e95b51080ae1244693aa858"),
    "parentCommentId" : ObjectId("5e95b4b49d3e303d667a8b70"),
    "text" : "Hello child 1 parent 1"
}

{
    "_id" : ObjectId("5e95b51d80ae1244693aa85a"),
    "parentCommentId" : ObjectId("5e95b4b49d3e303d667a8b70"),
    "text" : "Hello child 2 parent 1"

}

{
    "_id": "5e95b53580ae1244693aa85c",
    "parentCommentId": "5e95b4ef80ae1244693aa856",
    "text": "Hello child 1 parent 2",
}

期望的结果

{
    "success": true,
    "data": [
        {
            "_id": "5e95b4b49d3e303d667a8b71",
            "groupId": "5e8c5caa75b1cd342a1175eb",
            "comments": [
                {
                    "_id": "5e95b4b49d3e303d667a8b70",
                    "parentCommentId": null,
                    "text": "Hello parent 1",
                    "childs": {
                                 {
                                   "_id": "5e95b51080ae1244693aa858",
                                   "parentCommentId": "5e95b4b49d3e303d667a8b70",
                                   "text": "Hello child 1 parent 1",
                                 },
                                 {
                                   "_id": "5e95b51d80ae1244693aa85a",
                                   "parentCommentId": "5e95b4b49d3e303d667a8b70",
                                   "text": "Hello child 2 parent 1",
                                 },
                              }
                },
                {
                    "_id": "5e95b4ef80ae1244693aa856",
                    "parentCommentId": null,
                    "text": "Hello parent 2",
                    "childs": {
                                  {
                                    "_id": "5e95b53580ae1244693aa85c",
                                    "parentCommentId": "5e95b4ef80ae1244693aa856",
                                    "text": "Hello child 1 parent 2",
                                 }
                         }
                },


            ],
         }
    ]
}

如何修改我的查询？谢谢你。

标签： jsonmongodbmongodb-queryaggregation-frameworklookup

Answer 1

您可以使用以下聚合

db.walls.aggregate([
  { "$lookup": {
    "from": "comments",
    "let": { "commentIds": "$comments.commentId" },
    "pipeline": [
      { "$match": {
        "$expr": { "$in": ["$_id", "$$commentIds"] },
        "parentCommentId": null
      }},
      { "$sort": { "text": -1 }},
      { "$graphLookup": {
        "from": "comments",
        "startWith": "$_id",
        "connectFromField": "parentCommentId",
        "connectToField": "parentCommentId",
        "as": "childs"
      }}
    ],
    "as": "comments"
  }}
])

Mongo游乐场