MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Aggregates with project stage

Hi,
I’m trying to add a project stage to a pipeline in Java code.
My collection contains the fields:
_id, instrument, ctype, cname, pname, alias, timestamp, value

The purpose of my query is to get distinct groups of (ctype+cname+pname+alias) for a given instrument and a range of dates.

My code -before adding project stage- is:

AggregateIterable<Document> parameters = valuesCollection.aggregate(
				Arrays.asList(
						Aggregates.match(Filters.and(Arrays.asList(
								Filters.eq(instrument, 35), 
								Filters.gte(timestamp, fromDate), 
								Filters.lte(timestamp, toDate)
								))), 
						Aggregates.group(Filters.and(
								Filters.eq(ctype, "$ctype"), 
								Filters.eq(cname, "$cname"), 
								Filters.eq(pname, "$pname"),
								Filters.eq(alias, "$alias")))
						));

The reason why I need to use a project stage is that the only field in the returned document is _id.
Adding the project stage, my query is now like this:

AggregateIterable<Document> parameters = valuesCollection.aggregate(
				Arrays.asList(
						Aggregates.match(Filters.and(Arrays.asList(
								Filters.eq(instrument, 35), 
								Filters.gte(timestamp, fromDate), 
								Filters.lte(timestamp, toDate)
								))), 
						Aggregates.group(Filters.and(
								Filters.eq(ctype, "$ctype"), 
								Filters.eq(cname, "$cname"), 
								Filters.eq(pname, "$pname"),
								Filters.eq(alias, "$alias"))),
						Aggregates.project(Projections.fields(Projections.excludeId(), Projections.include("ctype","cname","pname","alias")))
						));

But it doesn’t work and returns no document.

Thanks for your help!

Hello Hélène Ortiz,

The group stage returns documents with only the _id field. If you notice carefully, the _id field is a sub-document with the four fields (ctype, pname, …). So, you need to project, for example, _id.ctype as ctype (same for the remaining 3 fields). The following code does that:

Aggregates.project(
    Projections.fields(
        Projections.computed("ctype", "$_id.ctype"), 
        Projections.computed("pname", "$_id.pname"),
        Projections.computed("ctype", "$_id.ctype"), 
        Projections.computed("pname", "$_id.pname"),
        Projections.excludeId()
    )
)

I do not see that from the code posted. Please enlighten me.

This is the Java code (of MongoDB Java Driver) posted by @Helene_ORTIZ. I have only shown the group and project stages of the aggregation, respectively.

Aggregates.group(
    Filters.and(
        Filters.eq(ctype, “$ctype”),
        Filters.eq(cname, “$cname”),
        Filters.eq(pname, “$pname”),
        Filters.eq(alias, “$alias”))
)
Aggregates.project(
    Projections.fields(
        Projections.excludeId(), 
        Projections.include(“ctype”,“cname”,“pname”,“alias”)
    )
)

The group and project stages are equivalent to the following in mongo shell:

{ 
    $group: { 
        _id: { 
            ctype: "$ctype",
            cname: "$cname",
            pname: "$pname",
            alias: "$alias"
        }
    }
}
{
    $project: {
        _id: 0,
        ctype: 1,
        cname: 1,
        pname: 1,
        alias: 1
    }
}

The problem is now quite evident in the project stage. My solution (in Java and the shell query):

Aggregates.project(
    Projections.fields(
        Projections.computed("ctype", "$_id.ctype"), 
        Projections.computed("cname", "$_id.cname"),
        Projections.computed("pname", "$_id.pname"), 
        Projections.computed("alias", "$_id.alias"),
        Projections.excludeId()
    )
)

{
    $project: {
        _id: 0,
        ctype: "$_id.ctype",
        cname: "$_id.cname",
        pname: "$_id.pname",
        alias: "$_id.alias"
    }
}
1 Like

Thanks.

I am so used to not use the Java builders that I completely missed that. I agree now that

Thanks a lot, it works great now !