CODEWITHSUNDEEP
arraysmongodbmongodb-query
prashantgpt91
prashantgpt91

Reputation: 1795

querying nested document mongoDB

This is my sample MongoDB in text Mode

I want to retrieve the data for each user. I also want _id because in a complete document it has several _id with similar structure.

I tried using unwind operator because the object contains nested arrays like this:

db.getCollection('topic_stats_2').aggregate([{ $unwind : "$usages.type.users.text" }, { $unwind : "$usages.type.users.stats.xyz1" }, { $unwind : "$usages.type.users.stats.xyz2" }, { $unwind : "$usages.type.users.stats.xyz3" }, { $unwind : "$usages.type.users.xyz4" }, { $unwind : "$usages.type.users.xyz5" }])

but it gives zero results.

I want the results in table format like this. I know the table would contain a lot of redundant data. but this what i want.

_id  | count | xyz4 | xyz5 | xyz1 | xyz2 | xyz3 | text | type |
     |       |      |      |      |      |      |      |      |
     |       |      |      |      |      |      |      |      |



/* 1 */
{
    "_id" : “photo",
    "count" : 236,
    "usages" : [ 
        {
            "type" : 1,
            "users" : [ 
                {
                    "text" : “jkncfkjfdn",
                    "stats" : {
                        “xyz1" : 6,
                        “xyz2" : 1,
                        “xyz3" : 1194
                    },
                    “xyz4" : "julius babao",
                    “xyz5" : "juLiusbabao"
                }, 
                {
                    "text" : “fcnf",
                    "stats" : {
                        “xyz1" : 9,
                        “xyz2" : 6,
                        “xyz3" : 1199
                    },
                    “xyz4" : "Dman",
                    “xyz5" : "DmanTheDesigner"
                }, 
                {
                    "text" : “dckejsndc",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 0,
                        “xyz3" : 1200
                    },
                    “xyz4" : "EastmanHouse",
                    “xyz5" : "EastmanHouse"
                }
            ]
        }, 
        {
            "type" : 2,
            "users" : [ 
                {
                    "text" : “msdnc",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 1,
                        “xyz3" : 1168
                    },
                    “xyz4" : "Shayne",
                    “xyz5" : "RKTay"
                }, 
                {
                    "text" : “kfjnvfv",
                    "stats" : {
                        “xyz1" : 0,
                        “xyz2" : 0,
                        “xyz3" : 523
                    },
                    “xyz4" : "andy stitches",
                    “xyz5" : "myproudmendes"
                }, 
                {
                    "text" : “jkopoiuyt",
                    "stats" : null,
                    “xyz4" : "jm",
                    “xyz5" : "jihannelayosa"
                }
            ]
        }, 
        {
            "type" : 3,
            "users" : [ 
                {
                    "text" : “opted",
                    "stats" : {
                        “xyz1" : 58,
                        “xyz2" : 32,
                        “xyz3" : 1192
                    },
                    “xyz4" : "♪♫Lil Darryl♫♪",
                    “xyz5" : "LilDarryl301"
                }, 
                {
                    "text" : "Cloud 9",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 1,
                        “xyz3" : 1171
                    },
                    “xyz4" : "FGN",
                    “xyz5" : "pretty_brown66"
                }, 
                {
                    "text" : "Cloud 9",
                    "stats" : {
                        “xyz1" : 0,
                        “xyz2" : 0,
                        “xyz3" : 997
                    },
                    “xyz4" : "Travis Porter Jr .",
                    “xyz5" : "AyoTravo"
                }
            ]
        }, 
        {
            "type" : 4,
            "users" : [ 
                {
                    "text" : “while",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 1,
                        “xyz3" : 1200
                    },
                    “xyz4" : "LEGO Darth Vader",
                    “xyz5" : "LegoDarthVader"
                }, 
                {
                    "text" : “xjw",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 1,
                        “xyz3" : 1198
                    },
                    “xyz4" : "The Brothers Brick",
                    “xyz5" : "BrothersBrick"
                }, 
                {
                    "text" : “pol",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 1,
                        “xyz3" : 1197
                    },
                    “xyz4" : "BYTES & BRICKS",
                    “xyz5" : "lego_bb"
                }
            ]
        }, 
        {
            "type" : 5,
            "users" : [ 
                {
                    "text" : “qtwqyw",
                    "stats" : {
                        “xyz1" : 1,
                        “xyz2" : 1,
                        “xyz3" : 1155
                    },
                    “xyz4" : "Kell_1976",
                    “xyz5" : "LuvsMyMunchkie"
                }, 
                {
                    "text" : “ytyty",
                    "stats" : {
                        “xyz1" : 12,
                        “xyz2" : 4,
                        “xyz3" : 1200
                    },
                    “xyz4" : "carriewildes",
                    “xyz5" : "carriewildes"
                }, 
                {
                    "text" : "from the high.",
                    "stats" : {
                        “xyz1" : 0,
                        “xyz2" : 0,
                        “xyz3" : 1067
                    },
                    “xyz4" : "jake☄",
                    “xyz5" : "w0rshiptheking"
                }
            ]
        }
    ]
}

Could someone help me out in this query?

Upvotes: 1

Views: 70

Answers (1)

chridam
chridam

Reputation: 103365

The way you are applying the $unwind operator is wrong, since it only acts on array fields and you are applying it on non-array fields. You can run the following aggregation pipeline to denormalize your array fields and get the desired structure:

db.getCollection('topic_stats_2').aggregate([
    { "$unwind": "$usages" },
    { "$unwind": "$usages.users" },
    {
        "$project": {
            "count": 1,
            "xyz4": "$usages.users.xyz4",
            "xyz5": "$usages.users.xyz5",
            "xyz1": "$usages.users.stats.xyz1",
            "xyz2": "$usages.users.stats.xyz2",
            "xyz3": "$usages.users.stats.xyz3",
            "text": "$usages.users.text",
            "type": "$usages.type"
        }
    }
])

Upvotes: 1

Related Questions