CODEWITHSUNDEEP
javamongodbmongodb-queryaggregation-frameworkmongodb-java
user1870400
user1870400

Reputation: 6364

How to use MongoDB Java driver to group by dayOfYear on ISODate attributes?

How to use mongodb java driver to compare dayOfYear of two ISODate objects?

Here are my docs

{"name": "hello", "count": 4, "TIMESTAMP": ISODate("2017-10-02T02:00:35.098Z")}
{"name": "hello", "count": 5, "TIMESTAMP": ISODate("2017-10-02T02:00:35.098Z")}
{"name": "goodbye", "count": 6, "TIMESTAMP": ISODate("2017-10-01T02:00:35.098Z")}
{"name": "foo", "count": 6, "TIMESTAMP": ISODate("2017-10-02T02:00:35.098Z")}

I want to compare the day in "TIMESTAMP" to perform some aggregation

 Bson match = Aggregates.match(eq("name": "hello"));
 Bson group = Aggregates.group(new Document("name", "$name"), Accumulators.sum("total", 1));

collection.aggregate(Arrays.asList(match, group))

Now I am not sure how to do this aggregation for all the records that belongs to particular day?

so my expected result for "2017-10-02" is

[{"_id": {"name":"hello"}, "total": 9}, {"_id": {"name":"foo"}, "total": 6}]

Upvotes: 0

Views: 1142

Answers (1)

glytching
glytching

Reputation: 47905

Given the following documents:

{"name": "hello", "count": 4, "TIMESTAMP": ISODate("2017-10-02T02:00:35.098Z")}
{"name": "hello", "count": 5, "TIMESTAMP": ISODate("2017-10-02T02:00:35.098Z")}
{"name": "goodbye", "count": 6, "TIMESTAMP": ISODate("2017-10-01T02:00:35.098Z")}
{"name": "foo", "count": 6, "TIMESTAMP": ISODate("2017-10-02T02:00:35.098Z")}

The following command ...

db.getCollection('dayOfYear').aggregate([

    // project dayOfYear as an attribute
    { $project: { name: 1, count: 1, dayOfYear: { $dayOfYear: "$TIMESTAMP" } } },

    // match documents with dayOfYear=275
    { $match: { dayOfYear: 275 } },

    // sum the count attribute for the selected day and name
    { $group : { _id : { name: "$name" }, total: { $sum: "$count" } } } 

])

... will return:

{
    "_id" : {
        "name" : "foo"
    },
    "total" : 6
}

{
    "_id" : {
        "name" : "hello"
    },
    "total" : 9
}

I think this meets the requirement expressed in your OP.

Here's the same command expressed using the MongoDB Java driver:

MongoCollection<Document> collection = mongoClient.getDatabase("stackoverflow").getCollection("dayOfYear");

Document project = new Document("name", 1)
        .append("count", 1)
        .append("dayOfYear", new Document("$dayOfYear", "$TIMESTAMP"));

Document dayOfYearMatch = new Document("dayOfYear", 275);

Document grouping = new Document("_id", "$name").append("total", new Document("$sum", "$count"));

AggregateIterable<Document> documents = collection.aggregate(Arrays.asList(
        new Document("$project", project),
        new Document("$match", dayOfYearMatch),
        new Document("$group", grouping)
));

for (Document document : documents) {
    logger.info("{}", document.toJson());
}

Update based on this comment:

One of the problems with project is that it only include fields you specify . The above input is just an example. I have 100 fields in my doc I can't sepecify every single one so if I use project I have to specify all 100 fields in addition to "dayOfYear" field. – user1870400 11 mins ago

You can use the following command to return the same output but without a $project stage:

db.getCollection('dayOfYear').aggregate([
    // ignore any documents which do not match dayOfYear=275
    { "$redact": {
        "$cond": {
             if: { $eq: [ { $dayOfYear: "$TIMESTAMP" }, 275 ] },
             "then": "$$KEEP",
             "else": "$$PRUNE"
        }
    }},

    // sum the count attribute for the selected day
    { $group : { _id : { name: "$name" }, total: { $sum: "$count" } } } 

])

Here's that command in its 'Java form':

MongoCollection<Document> collection = mongoClient.getDatabase("stackoverflow").getCollection("dayOfYear");

Document redact = new Document("$cond", new Document("if", new Document("$eq", Arrays.asList(new Document("$dayOfYear", "$TIMESTAMP"), 275)))
        .append("then", "$$KEEP")
        .append("else", "$$PRUNE"));

Document grouping = new Document("_id", "$name").append("total", new Document("$sum", "$count"));

AggregateIterable<Document> documents = collection.aggregate(Arrays.asList(
        new Document("$redact", redact),
        new Document("$group", grouping)
));

for (Document document : documents) {
    logger.info("{}", document.toJson());
}

Note: Depending on the size of your collection/your non functional requirements/etc you may want to consider the performance of these solutions and either (a) add a match stage before you start projecting/redacting or (b) extract dayOfYear into its own attribute so that you can avoid this complexity entirely.

Upvotes: 2

Related Questions