How project DBRef on Spring MongoDB Aggregation?

Question

I have the following aggregation done in a MongoDB shell to get the number of alerts of each type for each user:

db.getCollection('alerts').aggregate(

        {
            $unwind:"$son"
        },
        {
            $group:
            {
                _id:{
                    son: "$son",
                    level: "$level"
                },
                count: { $sum: 1 }
            }
        },
        {
            $group:
                {
                    _id:{ 
                        son: "$_id.son"
                    },
                    alerts: { $addToSet: {
                        level: "$_id.level",
                        count: "$count"
                    }}

                }
         }
       )

I have translated it to Spring Data MongoDB as follows:

TypedAggregation<AlertEntity> alertsAggregation = 
                Aggregation.newAggregation(AlertEntity.class,
                        unwind("$son"),
                        Aggregation.group("$son", "$level").count().as("count"),
                        Aggregation.group("$_id.son")
                            .addToSet(new BasicDBObject("level", "$_id.level").append("count", "$count")).as("alerts"));

        // Aggregation.match(Criteria.where("_id").in(sonIds)

            AggregationResults<AlertsBySonDTO> results = mongoTemplate.
                 aggregate(alertsAggregation, AlertsBySonDTO.class);

            List<AlertsBySonDTO> alertsBySonResultsList = results.getMappedResults();

            return alertsBySonResultsList;

What I have not clear and I can not get it to work, is to project the identifier and if possible the name of the user (son variable).

The resulting DTO is as follows


public final class AlertsBySonDTO implements Serializable {

    private static final long serialVersionUID = 1L;


    @JsonProperty("identity")
    private String id;

    @JsonProperty("alerts")
    private ArrayList<Map<String, String>> alerts;

}

but in the id property the entire embedded child entity.

This is the structure of the collection of alerts.

JSON alerts format:

{
    "_id" : ObjectId("59e6ff3d9ef9d46a91112890"),
    "_class" : "es.bisite.usal.bulltect.persistence.entity.AlertEntity",
    "level" : "INFO",
    "title" : "Alerta de Prueba",
    "payload" : "Alerta de Prueba",
    "create_at" : ISODate("2017-10-18T07:13:45.091Z"),
    "delivery_mode" : "PUSH_NOTIFICATION",
    "delivered" : false,
    "parent" : {
        "$ref" : "parents",
        "$id" : ObjectId("59e6ff369ef9d46a91112878")
    },
    "son" : {
        "$ref" : "children",
        "$id" : ObjectId("59e6ff389ef9d46a9111287b")
    }
}

/* 2 */
{
    "_id" : ObjectId("59e6ff6d9ef9d46a91112892"),
    "_class" : "es.bisite.usal.bulltect.persistence.entity.AlertEntity",
    "level" : "WARNING",
    "title" : "Token de acceso inv�lido.",
    "payload" : "El token de acceso YOUTUBE no es v�lido",
    "create_at" : ISODate("2017-10-18T07:14:53.449Z"),
    "delivery_mode" : "PUSH_NOTIFICATION",
    "delivered" : false,
    "parent" : {
        "$ref" : "parents",
        "$id" : ObjectId("59e6ff369ef9d46a91112878")
    },
    "son" : {
        "$ref" : "children",
        "$id" : ObjectId("59e6ff389ef9d46a9111287b")
    }
}

/* 3 */
{
    "_id" : ObjectId("59e6ff6d9ef9d46a91112893"),
    "_class" : "es.bisite.usal.bulltect.persistence.entity.AlertEntity",
    "level" : "WARNING",
    "title" : "Token de acceso inv�lido.",
    "payload" : "El token de acceso INSTAGRAM no es v�lido",
    "create_at" : ISODate("2017-10-18T07:14:53.468Z"),
    "delivery_mode" : "PUSH_NOTIFICATION",
    "delivered" : false,
    "parent" : {
        "$ref" : "parents",
        "$id" : ObjectId("59e6ff369ef9d46a91112878")
    },
    "son" : {
        "$ref" : "children",
        "$id" : ObjectId("59e6ff389ef9d46a9111287c")
    }
}

Anyone know how I can approach this?

thanks in advance

From the image you pasted I can't see how son is an array, because in your aggregation you are unwinding. It's better if you paste the json instead of an image. — Alex P., Oct 17 '17 at 19:02
I do not really master the aggregations in MongoDB, it's the only way I got the result I wanted. But my question is more focused on the final part of projection. I can not get the ID of the DBRef field. — Sergio Sánchez Sánchez, Oct 18 '17 at 07:16

Alex P. · Answer 1 · 2017-10-18T14:57:15.897

1. With MongoDB version 3.4

These are the following collections I created to reproduce your use case:

Alerts Collection

{ 
    "_id" : ObjectId("59e6ff3d9ef9d46a91112890"), 
    "_class" : "es.bisite.usal.bulltect.persistence.entity.AlertEntity", 
    "level" : "INFO", 
    "title" : "Alerta de Prueba", 
    "payload" : "Alerta de Prueba", 
    "create_at" : ISODate("2017-10-18T07:13:45.091+0000"), 
    "delivery_mode" : "PUSH_NOTIFICATION", 
    "delivered" : false, 
    "parent" : DBRef("parents", ObjectId("59e6ff369ef9d46a91112878")), 
    "son" : DBRef("children", ObjectId("59e72ff0572ae72d8c063666"))
}
{ 
    "_id" : ObjectId("59e6ff6d9ef9d46a91112892"), 
    "_class" : "es.bisite.usal.bulltect.persistence.entity.AlertEntity", 
    "level" : "WARNING", 
    "title" : "Token de acceso inv�lido.", 
    "payload" : "El token de acceso YOUTUBE no es valido", 
    "create_at" : ISODate("2017-10-18T07:14:53.449+0000"), 
    "delivery_mode" : "PUSH_NOTIFICATION", 
    "delivered" : false, 
    "parent" : DBRef("parents", ObjectId("59e6ff369ef9d46a91112878")), 
    "son" : DBRef("children", ObjectId("59e72ff0572ae72d8c063666"))
}
{ 
    "_id" : ObjectId("59e6ff6d9ef9d46a91112893"), 
    "_class" : "es.bisite.usal.bulltect.persistence.entity.AlertEntity", 
    "level" : "WARNING", 
    "title" : "Token de acceso inv�lido.", 
    "payload" : "El token de acceso INSTAGRAM no es v�lido", 
    "create_at" : ISODate("2017-10-18T07:14:53.468+0000"), 
    "delivery_mode" : "PUSH_NOTIFICATION", 
    "delivered" : false, 
    "parent" : DBRef("parents", ObjectId("59e6ff369ef9d46a91112878")), 
    "son" : DBRef("children", ObjectId("59e72ffb572ae72d8c063669"))
}

Notice I changed the OBjectIds of the sons reference to match the children collection I created.

Children collection

{ 
    "_id" : ObjectId("59e72ff0572ae72d8c063666"), 
    "name" : "Bob"
}
{ 
    "_id" : ObjectId("59e72ffb572ae72d8c063669"), 
    "name" : "Tim"
}

Since you are using a reference you can't just access a field from the other collection. So I think you are missing some aggregation steps.

I did the following:

db.getCollection('alerts').aggregate(
{
            $unwind:"$son"
        },
        {
            $group:
            {
                _id:{
                    son: "$son",
                    level: "$level"
                },
                count: { $sum: 1 }
            }
        },
        {
            $group:
                {
                    _id:{ 
                        son: "$_id.son"
                    },
                    alerts: { $addToSet: {
                        level: "$_id.level",
                        count: "$count"
                    }}

                }
         },
        { $addFields: { sonsArray: { $objectToArray: "$_id.son" } } },
        { $match: { "sonsArray.k": "$id"}  },
        { $lookup: { from: "children", localField: "sonsArray.v", foreignField: "_id", as: "name" } }
)

And got the following results as json:

{ 
    "_id" : {
        "son" : DBRef("children", ObjectId("59e72ffb572ae72d8c063669"))
    }, 
    "alerts" : [
        {
            "level" : "WARNING", 
            "count" : NumberInt(1)
        }
    ], 
    "sonsArray" : [
        {
            "k" : "$ref", 
            "v" : "children"
        }, 
        {
            "k" : "$id", 
            "v" : ObjectId("59e72ffb572ae72d8c063669")
        }
    ], 
    "name" : [
        {
            "_id" : ObjectId("59e72ffb572ae72d8c063669"), 
            "name" : "Tim"
        }
    ]
}
{ 
    "_id" : {
        "son" : DBRef("children", ObjectId("59e72ff0572ae72d8c063666"))
    }, 
    "alerts" : [
        {
            "level" : "INFO", 
            "count" : NumberInt(1)
        }, 
        {
            "level" : "WARNING", 
            "count" : NumberInt(1)
        }
    ], 
    "sonsArray" : [
        {
            "k" : "$ref", 
            "v" : "children"
        }, 
        {
            "k" : "$id", 
            "v" : ObjectId("59e72ff0572ae72d8c063666")
        }
    ], 
    "name" : [
        {
            "_id" : ObjectId("59e72ff0572ae72d8c063666"), 
            "name" : "Bob"
        }
    ]
}

If you want to get rid of the fields that where additionally created like sonsArray etc. you can do add a $project pipeline to clean your result.

2. If you have older versions of mongodb and you can change your data structure.

If instead of using a reference like this:

"son" : DBRef("children", ObjectId("59e72ffb572ae72d8c063669"))

you can add the objectId of the son/s as an array like this:

"sonId" : [
        ObjectId("59e72ff0572ae72d8c063666")
    ]

then you can do your aggregation as follows:

db.getCollection('alerts').aggregate(
{
            $unwind:"$sonId"
        },
        {
            $group:
            {
                _id:{
                    sonId: "$sonId",
                    level: "$level"
                },
                count: { $sum: 1 }
            }
        },
        {
            $group:
                {
                    _id:{ 
                        sonId: "$_id.sonId"
                    },
                    alerts: { $addToSet: {
                        level: "$_id.level",
                        count: "$count"
                    }}

                }
         },
        { $lookup: { from: "children", localField: "_id.sonId", foreignField: "_id", as: "son" } }
)

Is that something you are looking for?

It's perfect, the problem is that I can not use such a recent version of mongodb. Apparently this stage "$ addFields" was added in version 3.4. Is it possible to do it differently for previous versions? — Sergio Sánchez Sánchez, Oct 18 '17 at 11:35
I'm afraid it's not possible, because even if I provide you with an alternative for the "$addFields" (replace it with a longer "$project" stage for example) you'll bump into the next one which is the "$objectToArray". This is also only available in 3.4. Maybe that's reason enough to upgrade to the newest mongo version. It could work only with 3.2 if you are flexible with changing your schema. — Alex P., Oct 18 '17 at 12:29
If you are interested in changing your schema, I can elaborate more on that. — Alex P., Oct 18 '17 at 13:13
I will not be able to upgrade MongoDB we have version 2.4.9. And it is not possible to update it. What I can do is to save the SonEntity as an ObjectId and not as a @DBRef. If so, would another alternative be possible? — Sergio Sánchez Sánchez, Oct 18 '17 at 14:41
Does this response solve the problem of dbref fields spring data mongodb ? — Amir Choubani, Apr 11 '18 at 15:46

How project DBRef on Spring MongoDB Aggregation?

1 Answers1

Linked