I have an array of objects embedded in a document and there are multiple such documents in an collection. How to do I query those embedded array of objects with below conditions(based on the documents I have below).
First get objects whose "status" is "active"(status will not be in all the objects but only few)
Then get the "parent_user_id" of the above satisfied object and match it with the rest of the objects "parent_user_id" and get those objects
the result of the above conditions have to set instead of the original Array (i.e: "users") of objects in the output instead of all the objects present.
So if you take a look at the result am expecting there are 3 elements missing from the user array because those elements did not satisfy the above conditions.
Document I have in collection(there will be multiple document as such)
{
"_id" : ObjectId("63a8808652f40e1d48a3d1d7"),
"name" : "A",
"description" : null,
"users" : [
{
"id" : "63a8808c52f40e1d48a3d1da",
"owner" : "John Doe",
"purchase_date" : "2022-12-25,
"status" : "active",
"parent_user_id" : "63a8808c52f40e1d48a3d1da",
"recent_items": ["tomato",onion]
},
{
"id" : "63a880a552f40e1d48a3d1dc",
"owner" : "John Doe 1",
"purchase_date" : "2022-12-25,
"parent_user_id" : "63a8808c52f40e1d48a3d1da",
"recent_items": ["onion"]
},
{
"id" : "63a880f752f40e1d48assddd"
"owner" : "John Doe 2",
"purchase_date" : "2022-12-25,
"parent_user_id" : "63a8808c52f40e1d48a3d1da",
},
{
"id" : "63a880f752f40e1d48a3d207"
"owner" : "John Doe 11",
"dt" : "2022-12-25,
"status" : "inactive",
"parent_user_id" : "63a880f752f40e1d48a3d207",
},
{
"id" : "63a880f752f40e1d48agfmmb"
"owner" : "John Doe 112",
"dt" : "2022-12-25,
"status" : "active",
"parent_user_id" : "63a880f752f40e1d48agfmmb",
"recent_items": ["tomato"]
}
{
"id" : "63a880f752f40e1d48agggg"
"owner" : "John SS",
"dt" : "2022-12-25,
"status" : "inactive",
"parent_user_id" : "63a880f752f40e1d48agggg",
}
{
"id" : "63a880f752f40e1d487777"
"owner" : "John SS",
"dt" : "2022-12-25,
"parent_user_id" : "63a880f752f40e1d48agggg",
}
]
}
Result am expecting
{
"_id" : ObjectId("63a8808652f40e1d48a3d1d7"),
"name" : "A",
"description" : null,
"users" : [
{
"id" : "63a8808c52f40e1d48a3d1da",
"owner" : "John Doe",
"purchase_date" : "2022-12-25,
"status" : "active",
"parent_user_id" : "63a8808c52f40e1d48a3d1da",
"recent_items": ["tomato",onion]
},
{
"id" : "63a880a552f40e1d48a3d1dc",
"owner" : "John Doe 1",
"purchase_date" : "2022-12-25,
"parent_user_id" : "63a8808c52f40e1d48a3d1da",
},
{
"id" : "63a880f752f40e1d48assddd"
"owner" : "John Doe 2",
"purchase_date" : "2022-12-25,
"parent_user_id" : "63a8808c52f40e1d48a3d1da",
},
{
"id" : "63a880f752f40e1d48agfmmb"
"owner" : "John Doe 112",
"dt" : "2022-12-25,
"status" : "active",
"parent_user_id" : "63a880f752f40e1d48agfmmb",
"recent_items": ["tomato"]
}
]
}
i would use some $filter stages as follows :
db.collection.aggregate([
{
$addFields: {
users_matched: {
$filter: {
input: "$users",
as: "user",
cond: {
$eq: [
"active",
"$$user.status"
],
},
},
},
},
},
{
$set: {
users: {
$filter: {
input: "$users",
as: "user",
cond: {
$in: [
"$$user.parent_user_id",
"$users_matched.id"
],
},
},
},
},
},
{
$unset: "users_matched"
}
])
You can check for yourself on mongoplayground https://mongoplayground.net/p/SrpsWb4v21x
EDIT TO ANSWER THE SECOND QUESTION:
You could fix your tomato problem as follows :
db.collection.aggregate([
{
$addFields: {
active_users: {
$filter: {
input: "$users",
as: "user",
cond: {
$eq: [
"active",
"$$user.status"
],
},
},
},
tomato_users: {
$filter: {
input: "$users",
as: "user",
cond: {
$in: [
"tomato",
{
"$ifNull": [
"$$user.recent_items",
[]
]
}
],
},
},
}
},
},
{
$set: {
users: {
$filter: {
input: "$users",
as: "user",
cond: {
$and: [
{
$in: [
"$$user.parent_user_id",
"$active_users.id"
],
},
{
$in: [
"$$user.parent_user_id",
"$tomato_users.parent_user_id"
],
}
]
},
},
},
},
},
{
$unset: [
"active_users",
"tomato_users"
]
}
])
See on mongoplayground https://mongoplayground.net/p/mb21UT475yt