Comparing objects inside a nested array - mongoDB

Question

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!

The last `item` of `items` array for `"elem_id": 12` must be of `"type": z` right? — Dheemanth Bhat, Mar 03 '21 at 14:42
actually it is type: x because I only want to check that there are at least 2 unique keys and not that all are unique. But your answer is very helpfull- I just needed to use $gt on [{$size: "uniqueValues"}, 2] to get what I was looking for. Thanks! — Roni, Mar 03 '21 at 16:48

score 1 · Accepted Answer · answered Mar 03 '21 at 14:38

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]
}

Comparing objects inside a nested array - mongoDB

1 Answers1