mongodbmongodb-queryaggregation-frameworkredash

Comparing objects inside a nested array - mongoDB


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!


Solution

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