Home > other >  Trying to Compute Difference Between Two fields in MongoDB
Trying to Compute Difference Between Two fields in MongoDB

Time:12-11

Good evening. I have a project in MongoDB and i keep getting an error while trying to run my last querie. The dataset i have is about wines. What i want to do is to print the wines that match the Red wine category and that are at least 20 years old (i have a start date-the date that they were put in their barrels- and an end date, which is the date they were bottled). Note:i want all these fields to be printed at the end and be printed according to their rating.

let me give an example of the data:

{ _id: ObjectId("638f389d8830abb3f19aaf51"),
tconst: 'tt0040030',
wineType: 'Red',
Brand: '#brand',
startYear: 1990,
endYear: 2002
rating:6.6}

When i am using the $match function for my three criteria all is working just fine but i haven't figured out how to subtract the two date fields so i can find the wines that are at least 20 years old and print the results properly.

CodePudding user response:

in mongoDB, the $where query operator is used for comparing the fields or documents thast satisfy as java script expreesion . we can also pass a string

CodePudding user response:

Query

  • you need to refer to a field to compute the difference so you need $expr and aggregate operators
  • the bellow keeps red wines, with years difference>=20, and then sorts by rating.

Playmongo

aggregate(
[{"$match": 
   {"$expr": 
     {"$and": 
       [{"$eq": ["$wineType", "Red"]},
         {"$gte": [{"$subtract": ["$endYear", "$startYear"]}, 20]}]}}},
 {"$sort": {"rating": -1}}])
  • Related