mongoose - Filter data based on hard-coded conditions (MongoDB)

Question

I'm trying to get updates between two dates. for this reason i have some hard-coded conditions. (See the example below)

Updates Collection:

{
  thread_id: 'Number',
  text: 'String',
  timestamp: 'Number',
}

The problem is that i want to make only one query for all of the conditions but i don't know how to and i don't know if its achievable.

Example:

// Records in the updates collection
const updates = [
  { thread_id: 1, text: 'Hello', date: 2000 },
  { thread_id: 2, text: 'GoodBye!', date: 1999 },
  { thread_id: 2, text: 'This is a test', date: 3000 },
  { thread_id: 3, text: 'We are happy!', date: 6000 }
];

// Conditions (Hard-Coded)
const conditions = [
  {
    thread_id: 1,
    date_start: 1899,
    date_end: 2059
  }, {
    thread_id: 2,
    date_start: 1905,
    date_end: 2000
  }, {
    thread_id: 3,
    date_start: 1905,
    date_end: null // means no limitation
  }
];

// Results:
const results = [
  { thread_id: 1, text: 'Hello', timestamp: 2000 },
  { thread_id: 2, text: 'GoodBye!', timestamp: 1999 },
  { thread_id: 3, text: 'We are happy!', timestamp: 6000 },
]

// Query (Pseudoquery)
update.thread_id = condition.thread_id
condition.date_start <= update.timestamp <= condition.date_end

As you see This is a test is excluded and We are happy! is present because the end property is not set in the conditions.

question from:https://stackoverflow.com/questions/65642800/filter-data-based-on-hard-coded-conditions-mongodb

Answer 1

Maybe you can change abit the hard codded conditions as follow:

mongos> const cond= [ {thread_id:1 ,$or:[ {date:{$lte:1899}} ,{date:{$gte:2059}}] },{thread_id:2 ,$or:[ {date:{$gte:2000}} ,{date:{$lte:1905} } ] } , {thread_id:3 , $or:[ {date:{$lte:1905}}, {date:{$gte:9999}}]}  ]
mongos> db.updates.find({$or:cond})
{ "_id" : ObjectId("5ff9b208da146da4e1359dcf"), "thread_id" : 2, "text" : "This is a test", "date" : 3000 }
mongos> db.updates.find()
{ "_id" : ObjectId("5ff9b208da146da4e1359dcf"), "thread_id" : 2, "text" : "This is a test", "date" : 3000 }
{ "_id" : ObjectId("5ff9b399da146da4e1359dd1"), "thread_id" : 1, "text" : "Hello", "date" : 2000 }
{ "_id" : ObjectId("5ff9b3aada146da4e1359dd2"), "thread_id" : 2, "text" : "GoodBye!", "date" : 1999 }
{ "_id" : ObjectId("5ff9b3c6da146da4e1359dd3"), "thread_id" : 3, "text" : "We are happy!", "date" : 6000 }
mongos> db.updates.find({$or:cond})
{ "_id" : ObjectId("5ff9b208da146da4e1359dcf"), "thread_id" : 2, "text" : "This is a test", "date" : 3000 }
mongos> db.updates.deleteMany({$or:cond})
{ "acknowledged" : true, "deletedCount" : 1 }
mongos> db.updates.find()
{ "_id" : ObjectId("5ff9b399da146da4e1359dd1"), "thread_id" : 1, "text" : "Hello", "date" : 2000 }
{ "_id" : ObjectId("5ff9b3aada146da4e1359dd2"), "thread_id" : 2, "text" : "GoodBye!", "date" : 1999 }
{ "_id" : ObjectId("5ff9b3c6da146da4e1359dd3"), "thread_id" : 3, "text" : "We are happy!", "date" : 6000 }
mongos>