MongoDB 将嵌套的祖先元素嵌套到父元素中

huangapple go评论54阅读模式
英文:

MongoDB nesting ancestor elements into parent

问题

我有一些在帖子上发表的评论。

[id:10] 根评论
|
|-- [id:11] 回复根评论
| |
| |-- [id:12] 回复回复根评论
| |
| | |
| | |-- [id:13] 此链中的底部评论
|
|-- [id:19] 回复根评论

在回复链中的任何一条评论都包含了回到根评论的完整路径。

"此链中的底部评论" 将具有以下父数组:

{
...
parents:[10, 11, 12, 13]
...
}

我想要获取包含嵌套回复的父评论消息,如下所示。

{
"_id": "10",
"postId": "333"
"parents": ["11","12","13"],
...
}

我有以下的 aggregate 查询,生成不正确的结果,其中包括了父评论ID,即 10。

我正在使用 match 子句获取父元素,并使用 lookup 嵌套回复评论。

db.comments.aggregate([
{
$match: { $and: [
{ "postId": "333" },
{ "parents": { $size: 1 } }
]}
},
{
$lookup: {
from: "comments",
localField: "_id",
foreignField: "parents",
pipeline: [{ $sort: { "createdAt": 1 }}],
as: "replies"
}
},
{
$sort: { "createdAt": -1 }
}
]);
英文:

I have a number of comments made on a post.

[id: 10] Root comment
|
|-- [id: 11] Reply to root
|   |
|   |-- [id: 12] Reply to reply to root
|       |
|       |-- [id: 13] Bottom comment in this chain
|
|-- [id: 19] Another reply to root

Any one comment in a chain of replies contains the full path back to the root comment.

The "bottom comment in this chain" will have its parents array looking like this:

{
  ...
  parents: [10, 11, 12, 13]
  ...
}

I'd like to get the parent comment message with replies nested into it as shown below.

{
    "_id": "10",
	"postId": "333"
    "parents": ["11","12","13"],
	...
}

I have the following aggregate query which produces incorrect results where the parent comment id i.e. 10 is included in the results.

I'm getting the parent element using the match clause and nest the reply comments using lookup.

db.comments.aggregate([
		{
			$match: { $and: [
				{ "postId": "333" },
				{ "parents": { $size: 1 } }
			]}
		},
		{
			$lookup: {
				from: "comments",
				localField: "_id",
				foreignField: "parents",
				pipeline: [{ $sort: { "createdAt": 1 }}],
				as: "replies"
			}
		},
    {
        $sort: { "createdAt": -1 }
    }
]);

答案1

得分: 1

你需要过滤父级评论,像这样:

db.collection.aggregate([
  {
    "$match": {
      postId: 333,
      parents: {
        "$size": 1
      }
    }
  },
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$parents",
      "connectFromField": "parents",
      "connectToField": "parents",
      "as": "replies"
    }
  },
  {
    "$addFields": {
      "replies": {
        "$filter": {
          "input": "$replies",
          "as": "item",
          "cond": {
            "$ne": [
              {
                "$size": "$$item.parents"
              },
              {
                "$size": "$parents"
              }
            ]
          }
        }
      }
    }
  }
])

Playground 链接

英文:

You will have to filter the parent comment, in another stage, like this:

db.collection.aggregate([
  {
    "$match": {
      postId: 333,
      parents: {
        "$size": 1
      }
    }
  },
  {
    "$graphLookup": {
      "from": "collection",
      "startWith": "$parents",
      "connectFromField": "parents",
      "connectToField": "parents",
      "as": "replies",
      
    }
  },
  {
    "$addFields": {
      "replies": {
        "$filter": {
          "input": "$replies",
          "as": "item",
          "cond": {
            "$ne": [
              {
                "$size": "$$item.parents"
              },
              {
                "$size": "$parents"
              }
            ]
          }
        }
      }
    }
  }
])

Playground link

huangapple
  • 本文由 发表于 2023年3月31日 04:21:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75892675.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定