คำสั่ง MongoDB เลือกค่าที่ไม่ซ้ำ (Distinct) และนับจำนวน

มาสร้างคอลเล็กชันพร้อมเอกสารกันเถอะ

> db.demo586.insertOne(
    {"details": [
        { "Name":"Chris", "Marks":71 },
        { "Name":"Chris", "Marks":61 },
        { "Name":"David", "Marks":81 }
    ]}
);
{ "acknowledged" : true, "insertedId" : ObjectId("5e9200fefd2d90c177b5bcc7") }
> db.demo586.insertOne(
    {"details": [
        { "Name":"Chris", "Marks":71 },
        { "Name":"Carol", "Marks":61 },
        { "Name":"David", "Marks":81 }
    ]}
);
{ "acknowledged" : true, "insertedId" : ObjectId("5e9200fefd2d90c177b5bcc8") }

แสดงเอกสารทั้งหมดด้วยเมธอด find()

> db.demo586.find();

ผลลัพธ์ที่ได้

{ "_id" : ObjectId("5e9200fefd2d90c177b5bcc7"), "details" : [ { "Name" : "Chris", "Marks" : 71 }, { "Name" : "Chris", "Marks" : 61 }, { "Name" : "David", "Marks" : 81 } ] }
{ "_id" : ObjectId("5e9200fefd2d90c177b5bcc8"), "details" : [ { "Name" : "Chris", "Marks" : 71 }, { "Name" : "Carol", "Marks" : 61 }, { "Name" : "David", "Marks" : 81 } ] }

คำสั่งเลือก distinct และนับจำนวน

> var q= [
    { "$unwind": "$details" },
    {
        "$group": {
            "_id": {
                "Name": "$details.Name",
                "Marks": "$details.Marks"
            },
            "count": { "$sum": 1 }
        }
    },
    {
        "$group": {
            "_id": "$_id.Name",
            "distinctV": {
                "$addToSet": {
                    "value": "$_id.Marks",
                    "numberOfValues": "$count"
                }
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "Name": "$_id",
            "distinctV": 1
        }
    }
];
> db.demo586.aggregate(q);

ผลลัพธ์ที่ได้

{ "distinctV" : [ { "value" : 61, "numberOfValues" : 1 } ], "Name" : "Carol" }
{ "distinctV" : [ { "value" : 71, "numberOfValues" : 2 }, { "value" : 61, "numberOfValues" : 1 } ], "Name" : "Chris" }
{ "distinctV" : [ { "value" : 81, "numberOfValues" : 2 } ], "Name" : "David" }