In MongoDB it is possible to store information in the form of more or less complex objects. It may happen that we want to compare several fields from a sub-object of a collection.
In our example we will use a Product collection in which the Price field will define the different prices (purchase, sale HT, sale TTC,…).
Structure of the Collection
The Price field here contains 2 values that we will compare:
- the selling price excluding VAT,
- the selling price including VAT
=> data in the original format
How to make a projection with filter?
As these data are complex, it is not possible to directly compare the values with each other. For this it is necessary to go through a Projection.
Request
[{'$project': {'price_vte': {'$filter': {'input': '$prix', 'as':' list ',' cond ': {' $eq ': [' $list.id ',' prix_vente_ht_eur ']}}},' prix_vte_ttc ': {' $filter ': {' input ':' $prix ',' as ':' list ',' cond ': {' $eq ': [' $list.id ',' prix_vente_ttc_eur ']}}}}}, {' $match ': {' $expr ': {' $gte ': [' $prix_vte.value ',' $prix_vte_ttc.value ']}}}]
1 Data projection
First we make a projection "$project" in order to filter and name information to reuse them in our filter "$match".
In 'price_vte'we assign the result of the filter' $filter 'on the price field with value' id '=' prix_vente_ht_eur '
Ditto for 'price_vte_ttc'we assign the result of the filter' $filter 'on the price field having for value' id '=' prix_vente_ttc_eur '.
=> data in projected format
2 Comparison of values
It then remains the last step which consists in comparing our 2 values with a '$match' in the form of an expression or we test if '$prix_vte'> = '$prix_vte_ttc'
'$match': {'$expr': {'$gte': ['$prix_vte.value', '$prix_vte_ttc.value']}}
3 Retrieving results
In the end, only the recordings that pass the test will remain.
How do I run the query?
Simply execute the query using the 'aggregate' function by doing the following:
Db.Product.aggregate ([{'$project': {'price_price': {……}])
Other scenarios
In simpler cases when our 2 fields compared are simple fields there are several ways to compare 2 fields.
Here no complex data, only 2 fields of type 'double'
The equivalent in MySQL query would give this:
SELECT * FROM MyCollection field1> field2
Use $where
You can use a '$where'. Just be aware that it will be slow (because Javascript code must be executed on each record), so remember to create indexes.
Db.MyCollection.find ({$where: function () {return this.champ1> this.champ2}});
Or more simply:
Db.MyCollection.find ({$where: "this.champ1> this.champ2"});
If your request consists only of a $where you can even pass only the javascript code
Expressions $expr
Since MongoDB 3.6 it is possible to use the aggregation functions in a normal 'find' request
Db.MyCollection.find ({$expr: {$gt:}} ["$field1", "$champ2"]);
Or as an aggragation request
Db.MyCollection.aggregate ({$match: {$expr: {$gt:}}) ["$champ1", "$champ2"]
Screenings twice
Sometimes a large number of records need to be processed during a search. In order to greatly accelerate the processing speed, it is sometimes necessary to use projections.
We are here in case our collection contains several thousand records with about 30 fields
Test with a classic 'find'
Response time of 4.41s
Db.MyCollection.find ({$where: "this.champ1> this.champ2"});
Test with a '$project'
185ms response time
Db.MyCollection.aggregate ([{'$project': {"isGreater": {"$cmp": ["$champ1", "$champ2"]}, 'field1': 1, 'field2': 1}}, {'$match ': {' isGreater ': 1}}]))
By the projection we only retrieve our fields 1 and 2, as well as the result of the comparison between field1 and field2 that we assign in the field'isGreater' that we test.