In my db I have a nested array of elements inside each document containing items, in the following form:
elements:[
{
"elem_id": 12,
items: [ {"i_id": 1, "type": x}, {"i_id": 2, "type": y}, {"i_id": 3, "type": x}]
},
{
"elem_id": 13,
items: [ {"i_id": 4, "type": x}, {"i_id": 5, "type": x}]
}
]
I am trying to return all elements that have items of different types, meaning I would get back only:
{
"elem_id": 12,
items: [ {"i_id": 1, "type": x}, {"i_id": 2, "type": y}, {"i_id": 3, "type": x}]
}
since there are items of type x and of type y.
I think I need to iterate the items array and compare the type of every item in the array to the types of the previous items but I can't figure out how to do this in aggregation.
Just to note - I am using Redash and so I can't include any JS in the query.
Thank you for the assistance!
Try this:
db.elements.aggregate([
{ $unwind: "$elements" },
{
$addFields: {
"count": { $size: "$elements.items" },
"uniqueValues": {
$reduce: {
input: "$elements.items",
initialValue: [{ $arrayElemAt: ["$elements.items.type", 0] }],
in: {
$setUnion: ["$$value", ["$$this.type"]]
}
}
}
}
},
{
$match: {
$expr: {
$eq: ["$count", { $size: "$uniqueValues" }]
}
}
}
]);
Output:
{
"_id" : ObjectId("603f8f05bcece4372062bcea"),
"elements" : {
"elem_id" : 12,
"items" : [
{
"i_id" : 1,
"type" : 1
},
{
"i_id" : 2,
"type" : 2
},
{
"i_id" : 3,
"type" : 3
}
]
},
"count" : 3,
"uniqueValues" : [1, 2, 3]
}