I want to get the total number of replies in MongoDB

There is a table hoge with columns id, reply
Reply contains the id of the reply destination, and if there is no reply destination, it is null.
Suppose you have the following data:

{
    "id": "1",
    "reply"—Null
},
{
    "id": "2",
    "reply": "1"
},
{
    "id": "3",
    "reply": "2"
},
{
    "id": "4",
    "reply": "1"
}

When I get this, how do I get the sum of my confidence replies as follows?

{
    "id": "1",
    "reply"—null,
    "replyCount"—2
},
{
    "id": "2",
    "reply": "1",
    "replyCount"—1
},
{
    "id": "3",
    "reply": "2",
    "replyCount": 0
},
{
    "id": "4",
    "reply": "1",
    "replyCount": 0
}

mongodb

2022-09-30 21:19

1 Answers

Sample data:
(Note) The MongoDB table always contains the i_id が field, but I do not want to display it.Accept the "id" field name of the question.

db.hoge.find()
{ "_id"—ObjectId("5841f3d27261c6e2fca154d4"), "id": "1", "reply": null}
{ "_id"—ObjectId("5841f3d27261c6e2fca154d5"), "id": "2", "reply": "1"}
{ "_id"—ObjectId("5841f3d27261c6e2fca154d6"), "id": "3", "reply": "2"}
{ "_id"—ObjectId("5841f3d27261c6e2fca154d7"), "id": "4", "reply": "1"}
>db.hoge.find({}, {_id:false})
{ "id": "1", "reply": null}
{ "id": "2", "reply": "1"}
{ "id": "3", "reply": "2"}
{ "id": "4", "reply": "1"}

Basically, the following two results will be combined.iid 」 and rereply の items:

>db.hoge.find({}, {_id:false, "id":true, "reply":true}).sort({"id":1})
{ "id": "1", "reply": null}
{ "id": "2", "reply": "1"}
{ "id": "3", "reply": "2"}
{ "id": "4", "reply": "1"}

Additionally, the results of this aggregation.
Note: The __id 」 field is the key to the aggregation result.It has the same meaning as the "id" field in the sample data you asked me about.

>db.hoge.aggregate([
    {$group:{_id:"$reply", "replyCount":{$sum:1}}}, 
    {$match:{"_id":{$ne:null}}, 
    {$sort:{"_id":1}}])
{ "_id": "1", "replyCount":2}
{ "_id": "2", "replyCount":1}

In other words, one method is to merge these two results on the client side (application side).

The other method is to apply the $lookup aggregation stage and the operator of the aggregation array $size and output it like this.

>db.hoge.aggregate([
    {$project:{"_id":false, "id":"$id", "reply":"$reply"}}, 
    {$lookup:{from:"hoge", localField:"id", foreignField:"reply", as:"replies"}}, 
    {$project:{"id":"$id", "reply":"$reply", "replyCount":{$size:"$replies"}}}}])
{ "id": "1", "reply": null, "replyCount":2}
{ "id": "2", "reply": "1", "replyCount":1}
{ "id": "3", "reply": "2", "replyCount":0}
{ "id": "4", "reply": "1", "replyCount":0}

2022-09-30 21:19

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656