CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkspring-data-mongodb
Tolledo
Tolledo

Reputation: 609

Spring Data MongoDB aggregation - match by calculated value

I've got a collection of documents with 2 Number fields and I need to filter all the documents based on a calculated condition:

Number1/Number2 >= CONST%

How can I make this posible using Spring Data Mongo and Aggregation?

--UPDATE--

I've tried the following code:

return new RedactAggregationOperation(
            new BasicDBObject(
                    "$redact",
                    new BasicDBObject(
                            "$cond", new BasicDBObject()
                            .append("if", new BasicDBObject(
                                    "$gte", Arrays.asList(new BasicDBObject(
                                    "$divide",
                                    Arrays.asList(
                                            "$Number1",
                                            new BasicDBObject(
                                                    "$cond",
                                                    new BasicDBObject()
                                                            .append("$or", Arrays.asList(
                                                                    new BasicDBObject(
                                                                            "$eq", new BasicDBObject(
                                                                            "$Number2", 0)),
                                                                    new BasicDBObject(
                                                                            "$eq", new BasicDBObject(
                                                                            new BasicDBObject(
                                                                                    "$ifNull",
                                                                                    new BasicDBObject(
                                                                                            "$Number2", 0)).toString(), 0))))
                                                            .append("then", 1000000)
                                                            .append("else", "$Number2")), CONSTANT_VAR))
                            )))
                            .append("then", "$$KEEP")
                            .append("else", "$$PRUNE")
                    )
            )
    );

But there is an error "Unrecognized parameter to $cond: $or" Could you please assist with this issue?

Upvotes: 1

Views: 2501

Answers (1)

chridam
chridam

Reputation: 103345

What you need is the $redact operator in the aggregation framework which allows you to proccess the above logical condition with the $cond operator and uses the special operations $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

This operation is similar to having a $project pipeline that selects the fields in the collection and creates a new field that holds the result from the logical condition query and then a subsequent $match, except that $redact uses a single pipeline stage which is more efficient.

Consider the following example which demonstrate the above concept:

db.collection.aggregate([
    { 
        "$redact": {
            "$cond": [
                { 
                    "$gte": [
                        { "$divide": ["$Number1", "$Number2"] },
                        CONSTANT_VAR
                    ]
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

Since there's no support for the $redact operator yet (at the time of writing), a workaround will be to implement an AggregationOperation interface that wraps the aggregation operation with a class to take in a DBObject:

public class RedactAggregationOperation implements AggregationOperation {
    private DBObject operation;

    public RedactAggregationOperation (DBObject operation) {
        this.operation = operation;
    }

    @Override
    public DBObject toDBObject(AggregationOperationContext context) {
        return context.getMappedObject(operation);
    }
}

which you can then use in TypeAggregation:

Aggregation agg = newAggregation(
    new RedactAggregationOperation(
        new BasicDBObject(
            "$redact",
            new BasicDBObject(
                "$cond", new BasicDBObject()
                    .append("if", new BasicDBObject(
                        "$gte", Arrays.asList(
                            new BasicDBObject(
                                "$divide", Arrays.asList( "$Number1", "$Number2" )
                            ), 
                            CONSTANT_VAR
                        )
                    )
                )
                .append("then", "$$KEEP")
                .append("else", "$$PRUNE")
            )
        )
    )
);

AggregationResults<Example> results = mongoTemplate.aggregate(
    (TypedAggregation<Example>) agg, Example.class);

--UPDATE--

Follow-up from the comments, to check for nulls or zero values on the Number2 field in the division, you would have to nest a $cond expression with the logic instead.

The following example assumes you have a placeholder value of 1 if either Number2 does not exist/is null or have a value of zero:

db.collection.aggregate([
    { 
        "$redact": {
            "$cond": [
                { 
                    "$gte": [
                        { 
                            "$divide": [
                                "$Number1", {
                                    "$cond": [
                                        {
                                            "$or": [
                                                { "$eq": ["$Number2", 0] },
                                                { 
                                                    "$eq": [
                                                        { "$ifNull": ["$Number2", 0] }, 0
                                                    ]
                                                }
                                            ]
                                        },
                                        1,  // placeholder value if Number2 is 0
                                        "$Number2"                                          
                                    ]
                                }
                            ] 
                        },
                        CONSTANT_VAR
                    ]
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

Equivalent Spring Data Aggregation (untested)

Aggregation agg = newAggregation(
    new RedactAggregationOperation(
        new BasicDBObject(
            "$redact",
            new BasicDBObject(
                "$cond", Arrays.asList(
                    new BasicDBObject(
                        "$gte", Arrays.asList(
                            new BasicDBObject(
                                "$divide", Arrays.asList( 
                                    "$Number1", 
                                    new BasicDBObject(
                                        "$cond", Arrays.asList( 
                                            new BasicDBObject( "$or": Arrays.asList( 
                                                    new BasicDBObject("$eq", Arrays.asList("$Number2", 0)),
                                                    new BasicDBObject("$eq", Arrays.asList(
                                                            new BasicDBObject("$ifNull", Arrays.asList("$Number2", 0)), 0
                                                        )
                                                    )
                                                )
                                            ),
                                            1,  // placeholder value if Number2 is 0
                                            "$Number2"                                          
                                        )
                                    ) 
                                )
                            ), 
                            CONSTANT_VAR
                        )
                    ), "$$KEEP", "$$PRUNE"
                )
            )
        )
    )
);

Upvotes: 2

Related Questions