I need some help to filter the field Internals array to only show the item with the same _id of this field profile_id using aggregate, but I realy cant get this.
I try use filter, lookup and search in many posts.
I want using aggregate, to get the documents with filtered array of users based on the objectID of the profile.
Every profile id has only one internal id
Each user has only one INTERNAL.
PRODUCTS TABLE
[
{
"_id": {"$oid": "5dd5a66e8ddbd07fa251b88e"},
"name": "Product One",
"internals": [
{
"default": false,
"_id": {"$oid": "5e94da0f4d499253049ec532"},
"name": "Type A"
},
{
"_id": {"$oid": "5e94da0f4d499253049ec534"},
"name": "Type B"
},
{
"_id": {"$oid": "5e94da0f4d499253049ec536"},
"name": "Type C"
},
{
"_id": {"$oid": "5e94da0f4d499253049ec538"},
"name": "Type D"
},
{
"_id": {"$oid": "5f05d9a2bfc8fa1361d8beea"},
"name": "Type E"
},
{
"_id": {"$oid": "5f05d9a2bfc8fa1361d8beec"},
"name": "Type F"
}
]
},
{
"_id": {"$oid": "5e8b40bc91dd0e12e0219578"},
"name": "Product Two",
"internals": [
{
"_id": {"$oid": "5e8b40bc91dd0e12e0219579"},
"name": "Default Type"
}
],
}
]
USER TABLE
[
{
"_id": {"$oid": "603d4c44505a51001c9b936f"},
"email": "mister.paper@papers.org",
"name": "Mister Paper",
"profile": {
"products": [
{
"_id": {"$oid": "603d4d101c24a9001cf79db1"},
"product": {"$oid": "5dd5a66e8ddbd07fa251b88e"},
"profile": {"$oid": "5e94da0f4d499253049ec534"}
},
{
"_id": {"$oid": "603fe3ebca063ea357ca0666"},
"product": {"$oid": "5e8b40bc91dd0e12e0219578"},
"profile": {"$oid": "5e8b40bc91dd0e12e0219579"}
},
{
"_id": {"$oid": "603fe3ebca063ea357ca0667"},
"product": {"$oid": "5dd5a66e8ddbd07fa251b88e"},
"profile": {"$oid": "5e94da0f4d499253049ec538"}
}
],
},
"username": "mister.paper@papers.org"
}
]
That is my query at this moment after try many others :
QUERY
db.getCollection('users').aggregate([
{ $match: { email: { $in: [ /mister.paper/i ] }} }, { $sort: { created_at: -1 } },
{$unwind:"$profile"},
{$unwind:"$profile.products"},
{$unwind:"$profile.products.profile"},
{'$lookup': { from: 'products', localField: 'profile.products.product', foreignField: '_id', as: 'products' } },
{$unwind:"$products"},
//{$unwind:"$products"},
//{$unwind:"$products.internals"},
{$addFields: { "products.profile_id": '$profile.products.profile'}},
{'$lookup': { from: 'products.internals', localField: 'products.profile_id', foreignField: '_id', as: 'internalsArray' } },
{
'$project': {
name: 1,
products: {
_id: "$products._id",
name: "$products.name",
profile_id : 1,
},
productsId: 1,
"internals": { _id: "$products.internals._id" , name: "$products.internals.name" },
internalsArray : 1,
}
}
], {});