Search code examples
mongodbmatchprojectsubdocument

match subdocument within another subdocument same document

I have the following documents in my collection :

{
"_id": ObjectId('555a33d69487b45401ec1149'),
"nombre": "demo",
"roles": [
    {
        "id": ObjectId('556ca1999487009c1fac125f'),
        "nombre": "rol1"
    },
    {
        "id": ObjectId('556ca1a09487009c1fac1260'),
        "nombre": "rol2"
    },
    {
        "id": ObjectId('556ca1a69487009c1fac1261'),
        "nombre": "rol3"
    }
],
"usuarios": [
    {
        "id": ObjectId('556ca1de9487009c1fac1262'),
        "activo": true,
        "roles": [
            {
                "id": ObjectId('556ca1999487009c1fac125f')
            }
        ]
    },
    {
        "id": ObjectId('556ca2659487009c1fac1263'),
        "activo": true,
        "roles": [
            {
                "id": ObjectId('556ca1a09487009c1fac1260')
            }
        ]
    },
    {
        "id": ObjectId('556ca27a9487009c1fac1264'),
        "activo": true,
        "roles": [
            {
                "id": ObjectId('556ca1a69487009c1fac1261')
            }
        ]
    }
]
}


{
    "_id": ObjectId('556f457594877b4502b2e85d'),
    "nombre": "app3",
    "roles": [
        {
            "id": ObjectId('556f45fa94877b4502b2e864'),
            "nombre": "rol1_app3"
        },
        {
            "id": ObjectId('556f460194877b4502b2e865'),
            "nombre": "rol3_app3"
        },
        {
            "id": ObjectId('556f460694877b4502b2e866'),
            "nombre": "rol5_app3"
        },
        {
            "id": ObjectId('556f460b94877b4502b2e867'),
            "nombre": "rol7_app3"
        }
    ],
    "usuarios": [
        {
            "id": ObjectId('556ca1de9487009c1fac1262'),
            "activo": true,
            "roles": [
                {
                    "id": ObjectId('556f460b94877b4502b2e867')
                },
                {
                    "id": ObjectId('556f460194877b4502b2e865')
                }
            ]
        }
    ]
}

What i want is to specify the 'usuarios.id' and get the all the "roles.id" , "roles.nombre" by each document where the 'usuarios.id' exists.

I have tried this :

-- first declare a var with the 'usuarios.id' i'm looking for

var uid = new ObjectId("556ca1de9487009c1fac1262")

then i execute this :

db.aplicacion.aggregate([
{ $match: { "usuarios.id" : uid, "usuarios.activo" : true } },
{ $unwind: "$usuarios"},
{ $unwind: "$usuarios.roles"},
{ $match: { "usuarios.id" : uid } },
{ $unwind: "$roles"},
{ $project: { "nombre":1, "usuarios.roles.id":1 , "usuarios.id":1,      "roles.id":1, "roles.nombre":1 }}
]).pretty();

But the result is this :

    {
        "_id" : ObjectId("555a33d69487b45401ec1149"),
        "nombre" : "demo",
        "roles" : {
                "id" : ObjectId("556ca1999487009c1fac125f"),
                "nombre" : "rol1"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556ca1999487009c1fac125f")
                }
        }
}
{
        "_id" : ObjectId("555a33d69487b45401ec1149"),
        "nombre" : "demo",
        "roles" : {
                "id" : ObjectId("556ca1a09487009c1fac1260"),
                "nombre" : "rol2"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556ca1999487009c1fac125f")
                }
        }
}
{
        "_id" : ObjectId("555a33d69487b45401ec1149"),
        "nombre" : "demo",
        "roles" : {
                "id" : ObjectId("556ca1a69487009c1fac1261"),
                "nombre" : "rol3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556ca1999487009c1fac125f")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f45fa94877b4502b2e864"),
                "nombre" : "rol1_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460b94877b4502b2e867")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460194877b4502b2e865"),
                "nombre" : "rol3_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460b94877b4502b2e867")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460694877b4502b2e866"),
                "nombre" : "rol5_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460b94877b4502b2e867")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460b94877b4502b2e867"),
                "nombre" : "rol7_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460b94877b4502b2e867")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f45fa94877b4502b2e864"),
                "nombre" : "rol1_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460194877b4502b2e865")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460194877b4502b2e865"),
                "nombre" : "rol3_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460194877b4502b2e865")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460694877b4502b2e866"),
                "nombre" : "rol5_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460194877b4502b2e865")
                }
        }
}
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460b94877b4502b2e867"),
                "nombre" : "rol7_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460194877b4502b2e865")
                }
        }
}

This is a product of each rol.id, what i expect to get is something like this ( only get those where roles.id = usuarios.roles.id :

    {
        "_id" : ObjectId("555a33d69487b45401ec1149"),
        "nombre" : "demo",
        "roles" : {
                "id" : ObjectId("556ca1999487009c1fac125f"),
                "nombre" : "rol1"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556ca1999487009c1fac125f")
                }
        }
},
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460b94877b4502b2e867"),
                "nombre" : "rol7_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460b94877b4502b2e867")
                }
        }
},
{
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : {
                "id" : ObjectId("556f460194877b4502b2e865"),
                "nombre" : "rol3_app3"
        },
        "usuarios" : {
                "id" : ObjectId("556ca1de9487009c1fac1262"),
                "roles" : {
                        "id" : ObjectId("556f460194877b4502b2e865")
                }
        }
}

Any ideas??

This is my current script:

var uid = new ObjectId("556ca1de9487009c1fac1262")

db.aplicacion.aggregate([
    { $match: { "usuarios.id" : uid, "usuarios.activo" : true } },
    { $unwind: "$usuarios"},
    { $unwind: "$usuarios.roles"},
    { $match: { "usuarios.id" : uid } },
    { $unwind: "$roles"},
    { $project: { "nombre":1, "usuarios.roles.id":1 , "usuarios.id":1,      "roles.id":1, "roles.nombre":1 }}
    ]).pretty();

UPDATE:

After using @yogesh solution, i did a little improvement to the script :

    db.coll.aggregate({
    "$unwind": "$usuarios"
}, {
    "$match": {
        "usuarios.id": ObjectId("556ca1de9487009c1fac1262"),
        "usuarios.activo":true,
    }
}, {
    "$unwind": "$usuarios.roles"
}, {
    "$unwind": "$roles"
}, {
    "$project": {
        "nombre": 1,
        "roles.id": 1,
        "roles.nombre": 1,        
        "roles.activo": 1,
        "matched": {
            "$eq": ["$roles.id", "$usuarios.roles.id"], //macthed roles.id == usuarios.roles.id            
        }
    }
}, {
    "$match": {
        "matched": true,
        "roles.activo" : true
    }
},
{
  $group : {
        _id : "$_id",
        nombre: { $first: "$nombre" },
        roles : {
                $push : "$roles"
        }

  }
}).pretty();

Added a $group and the result has a better structure i think :

    {
        "_id" : ObjectId("556f457594877b4502b2e85d"),
        "nombre" : "app3",
        "roles" : [
                {
                        "id" : ObjectId("556f460b94877b4502b2e867"),
                        "nombre" : "rol7_app3",
                        "activo" : true
                },
                {
                        "id" : ObjectId("556f460194877b4502b2e865"),
                        "nombre" : "rol3_app3",
                        "activo" : true
                }
        ]
}
{
        "_id" : ObjectId("555a33d69487b45401ec1149"),
        "nombre" : "demo",
        "roles" : [
                {
                        "id" : ObjectId("556ca1999487009c1fac125f"),
                        "nombre" : "rol1",
                        "activo" : true
                }
        ]
}

Thanks again @yogesh

Solution

  • Your aggregation missed some thing you should matched in project to macthed roles.id == usuarios.roles.id check below aggregation :

    db.collectionName.aggregate({
    "$unwind": "$usuarios"
}, {
    "$match": {
        "usuarios.id": ObjectId("556ca1de9487009c1fac1262")
    }
}, {
    "$unwind": "$usuarios.roles"
}, {
    "$unwind": "$roles"
}, {
    "$project": {
        "nombre": 1,
        "roles": 1,
        "usuarios.id": 1,
        "usuarios.activo": 1,
        "usuarios.roles.id": 1,
        "matched": {
            "$eq": ["$roles.id", "$usuarios.roles.id"] //macthed roles.id == usuarios.roles.id
        }
    }
}, {
    "$match": {
        "matched": true
    }
}).pretty()