Group by array element in Mongodb

Question

We have nested document and trying to group by array element. Our document structure looks like

/* 1 */
{
    "_id" : ObjectId("5a690a4287e0e50010af1432"),
    "slug" : [ 
        "true-crime-the-10-most-infamous-american-murder-mysteries", 
        "10-most-infamous-american-murder-mysteries"
    ],
    "tags" : [ 
        {
            "id" : "59244aa6b1be5055278e9b5b",
            "name" : "true crime",
            "_id" : "59244aa6b1be5055278e9b5b"
        }, 
        {
            "id" : "5924524db1be5055278ebd6e",
            "name" : "Occult Museum",
            "_id" : "5924524db1be5055278ebd6e"
        }, 
        {
            "id" : "5a690f0fc1a72100110c2656",
            "_id" : "5a690f0fc1a72100110c2656",
            "name" : "murder mysteries"
        }, 
        {
            "id" : "59244d71b1be5055278ea654",
            "name" : "unsolved murders",
            "_id" : "59244d71b1be5055278ea654"
        }
    ]
}

We want to find list of all slugs group by tag name. I am trying with following and it gets result but it isn't accurate. We have hundreds of records with each tag but i only get few with my query. I am not sure what i am doing wrong here. Thanks in advance.

// Requires official MongoShell 3.6+

db.getCollection("test").aggregate(
    [
        { 
            "$match" : {
                "item_type" : "Post", 
                "site_id" : NumberLong(2), 
                "status" : NumberLong(1)
            }
        },
		{$unwind: "$tags" },         
        { 
            "$group" : {
                "_id" : {
                    "tags᎐name" : "$tags.name",
                    "slug" : "$slug"
                }
            }
        }, 
        { 
            "$project" : {
                "tags.name" : "$_id.tags᎐name",
                "slug" : "$_id.slug",
                "_id" : NumberInt(0)
            }
        }
    ], 
    { 
        "allowDiskUse" : true
    }
);

Expected output is

TagName     Slug
----------
true crime "true-crime-the-10-most-infamous-american-murder-mysteries", 
           "10-most-infamous-american-murder-mysteries"
           "All records where tags true crime"

can you update the question with expected output of the query you want? — Alok Deshwal
– Alok Deshwal, Commented Apr 22, 2019 at 15:54

mickl · Accepted Answer · 2019-04-22 20:37:27Z

2

Instead of using slug as a part of _id you should use $push or $addToSet to accumulate them, try:

db.test.aggregate([
    {
        $unwind: "$tags"
    },
    {
        $unwind: "$slug"
    },
    {
        $group: {
            _id: "$tags.name",
            slugs: { $addToSet: "$slug" }
        }
    },
    {
        $project: {
            _id: 1,
            slugs: {
                $reduce: {
                    input: "$slugs",
                    initialValue: "",
                    in: {
                        $concat: [ "$$value", ",", "$$this" ]
                    }
                }
            }
        }
    }
])

EDIT: to get comma separated string for slugs you can use $reduce with $concat

Output:

{ "_id" : "murder mysteries", "slugs" : ",10-most-infamous-american-murder-mysteries,true-crime-the-10-most-infamous-american-murder-mysteries" }
{ "_id" : "Occult Museum", "slugs" : ",10-most-infamous-american-murder-mysteries,true-crime-the-10-most-infamous-american-murder-mysteries" }
{ "_id" : "unsolved murders", "slugs" : ",10-most-infamous-american-murder-mysteries,true-crime-the-10-most-infamous-american-murder-mysteries" }
{ "_id" : "true crime", "slugs" : ",10-most-infamous-american-murder- mysteries,true-crime-the-10-most-infamous-american-murder-mysteries" }

edited Apr 22, 2019 at 20:37

answered Apr 22, 2019 at 18:26

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jvm Over a year ago

This is almost what i need. Is there a way to have slug as comma separated string than Array ? I need to export this data into excel and it creates unnecessary empty columns

mickl Over a year ago

@jvm modified my answer

Collectives™ on Stack Overflow

Group by array element in Mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related