Mongodb: how to compare 2 fields from a projection?

Mongodb: how to compare 2 fields from a projection?

Mongodb: how to compare 2 fields from a projection?

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

product 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.


[{'$project': {'price_vte': {'$filter': {'input': '$prix', 'as':' list ',' cond ': {' $eq ': [' $ ',' prix_vente_ht_eur ']}}},' prix_vte_ttc ': {' $filter ': {' input ':' $prix ',' as ':' list ',' cond ': {' $eq ': [' $ ',' 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

product project

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

product project

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.

Leave a Reply