Query a document and all of its subdocuments that match a condition in mongodb (using spring)

Question

I have a MongoDB storing data from different sensors. It has the following structure:

 {
     "_id" : 1,
     "sensorName" : "Heart Rate",
     "samplePeriod" : 1000,
     "data" : [
             {
                 "timestamp" : NumberLong("1483537204046"),
                 "dataPoints" : [ 68 70 ]
             },
             {
                 "timestamp" : NumberLong("1483537206046"),
                 "dataPoints" : [ 68 70 ]
             }
     ]
}
{
    "_id" : 2,
    "sensorName" : "Ambient Light",
    "samplePeriod" : 500,
    "data" : [
            {
                "timestamp" : NumberLong("1483537204058"),
                "dataPoints" : [ 56, 54, 54, 54 ]
            },
            {
                "timestamp" : NumberLong("1483537206058"),
                "dataPoints" : [ 56, 54, 54, 54 ]
            }
    ]
}

Now for example i need the "Heart Rate" - document with all of its fields and those of its "data" - subdocuments matching the condition "timestamp between 1483537204000 and 1483537214000".

I already got the answer on how to do this in the mongo shell in another Question. See this code:

aggregate([{
    $match: {
        "_id": 1
    }
}, {
    "$project": {
        "_id": 1,
        "sensorName": 1,
        "samplePeriod": 1,
        "data": {
            "$filter": {
                "input": "$data",
                "as": "result",
                "cond": {
                    $and: [{
                        $gte: ["$$result.timestamp", 1483537204000]
                    }, {
                        $lte: ["$$result.timestamp", 1483537214000]
                    }]
                }
            }
        }
    }
}])

But how do I do this in java spring-data? It seems there is nothing like $filter in spring-data. Is there a workaround?

How efficient is $filter anyway? Can you think of a more efficient/practical way of structuring this kind of data in mongodb?

Thanks in advance!

Mcmorris

If you are planning to do it with Spring, I highly recommend you to use Spring-data which will give you methods to handle this kinds of queries – Kavita

i am using spring data already (changed this in the text). I only struggle when creating a new aggregation. I can "match", "project", but not "filter". – Mcmorris

Have a look at this post i guess is what you need #34752345 – Kavita

Answer 1

You'll need to make use of MongoTemplate provided in the spring mongo data dependency. There is no out of box support for $filter in the current release version. Make use of AggressionExpression. Include below projection in project. Use 1.8.5 spring mongo data version.

Aggregation aggregation = newAggregation(
        match(Criteria.where("_id").is(1)),
        project( "_id", "sensorName", "samplePeriod").and(new AggregationExpression() {
            @Override
            public DBObject toDbObject(AggregationOperationContext aggregationOperationContext) {
                DBObject filter = new BasicDBObject("input", "$data").append("as", "result").append("cond",
                        new BasicDBObject("$and", Arrays.<Object> asList(new BasicDBObject("$gte", Arrays.<Object> asList("$$result.timestamp", 1483537204000L)),
                                new BasicDBObject("$lte", Arrays.<Object> asList("$$result.timestamp", 1483537214000L)))));
                return new BasicDBObject("$filter", filter);
            }
        }).as("data")
);

List<BasicDBObject> dbObjects = monoTemplate.aggregate(aggregation, "collectionname", BasicDBObject.class).getMappedResults();

Diathermic

Works great, but you missed a "$" before the first "data" in filter Object. I will accept your answer as most practical, because it does support older versions of spring data and does not use "unwind", which is better in my use case. Thanks Sagar – Mcmorris

Answer 2

I think the same can be achieved by the use of unwind and an extra match. Spring mongo driver does provide support for unwind and it looks bit cleaner.

aggregate([{
 $match: {
    "_id": 1
   }
 }, {
  $unwind : "$data"
 },{
   $match : {'data.timestamp' : {$gte : 1483537204000, $lte : 1483537214000}}
 }, {
  $group : {
      _id : $_id,
      data : {$push:$data}
  }
 }])

Anacoluthon

But unwind is slow on big documents, as it creates seperate documents for each entry, isn't it? I would like to avoid that. What about "filter"s performance in comparison? – Mcmorris

what make you think unwind will be slow? Imo it would be same if not faster than the filtering in the projection stage where each data is passed and parsed through condition. unwind also gives us hand to go for more complex maneuver. Also if you are doing for one document which have a 16MB limit, you would not be able to gaze the difference in performance in either of them. – Anacoluthon

This is not entirely true. Filter is an in-place operation. It may not matter for your specific case. The performance will take a hit if you have large documents. This is tailor made case for filter. Why do you want to unwind when there is no need ? – Diathermic

@SagarReddy It goes to perception isn't it? for you there is no need of unwind, for me there is no need of complex filter operation inside projection. just because it's inplace doesn't mean it's better. It's called 'aggregation' for a reason where data seems to be cleanly aggregated and not morphing of each and every inner data with filter conditions. But you sure are entitled for your opinion. – Anacoluthon

Just to clarify it doesn't go to perception. I'll leave it to the OP to decide. – Diathermic

I guess both are good ways depending on the use case. Thank you both. – Mcmorris

Answer 3

The Spring Data MongoDB 1.10 RC1 (Ingalls), 2.0 M2 (Kay) releases (as of writing) have added support for $filter and this can be implemented as follows:

Aggregation.newAggregation(Entity.class,
    match(Criteria.where("_id").is(1)),
    project("sensorName", "samplePeriod")
        .and(
            filter("data")
                .as("item")
                .by(
                    GTE.of(field("item.timestamp"), 1483537204000)
                    .LTE.of(field("item.timestamp"), 1483537214000)
                )
        ).as("data")
    )

Rubenstein