getting problem with condition in mongodb

Question

See my condition does work fine for my history's first collection but for second it does not work either If I replace my specified id with 5e4a8d2d3952132a08ae5724 that exists in my second object of history's collection return me all data which is not correct because it's date is greater then main collection's date so it should return me nothing please suggest how to fix it.

db.main.aggregate([
  {
    $lookup: {
      from: "history",
      localField: "history_id",
      foreignField: "history_id",
      as: "History"
    }
  },
  {
    $unwind: "$History"
  },
  {
    "$match": {
      $expr: {
        $cond: {
          if: {
            $eq: [
              "5e4a8d2d3952132a08ae5764",
              "$History.user_id"
            ]
          },
          then: {
            $and: [
              {
                $gt: [
                  "$date",
                  "$History.date"
                ]
              },
              {
                $eq: [
                  "5e4a8d2d3952132a08ae5764",
                  "$History.user_id"
                ]
              }
            ]
          },
          else: {}
        }
      }
    }
  }
])

when I put two object into history collection it does not work either MongoPlayground

Here is working playground with single history object https://mongoplayground.net/p/hrNofrq1c3S

Could you post your expected output and on what conditions you want to filter the documents?? — ngShravil.py, Apr 29 '20 at 18:29
My question is simple filter out records from main collection which has lesser date than history's collection based on specified user_id here is configuration https://mongoplayground.net/p/hrNofrq1c3S — Sunil Dubey, Apr 29 '20 at 18:32
This link is not working, it's showing 'no document found, when I am running the query. — ngShravil.py, Apr 29 '20 at 18:35
Yes that's correct because history date 1587650683440 is bigger then main collection date. Also keep in mind if specified data does not match should return all data from main collection, I am stucking when history's collection has more than one object. — Sunil Dubey, Apr 29 '20 at 18:38
Main collection data base on filter or fulfil condition like this https://mongoplayground.net/p/u3UVrAMarYr — Sunil Dubey, Apr 29 '20 at 18:45
can't we do compare with specified id let's say I will pass specified id and then compare.. ? — Sunil Dubey, Apr 29 '20 at 19:02
Why I am saying because I want to pass user_id that will check in history's collection if possible @ngShravil.py it will say like cond: { $lt: [ "$$his.date", "$date" ] } and specified $eq: [ "5e4a8d2d3952132a08ae5764", "$History.user_id" ] ? — Sunil Dubey, Apr 29 '20 at 19:03
This will help you with passing the arguments to MongoDB query: https://stackoverflow.com/a/10114802/6635464 — ngShravil.py, Apr 29 '20 at 19:19
where and how to pass this could you please make a demo inside playground ? — Sunil Dubey, Apr 29 '20 at 19:21
One case is failing here it should return all data if history collection is null and specified id does not match it should return all data see https://mongoplayground.net/p/X2B58D_SuLl — Sunil Dubey, Apr 29 '20 at 19:33

score 1 · Accepted Answer · answered Apr 29 '20 at 19:21

1

The reason, why your query (which is posted in the OP) is not working because, you have not specified anything in else part. So, the better approach will be with '$filter' operator.

You can try the below:

db.main.aggregate([
  {
    $lookup: {
      from: "history",
      localField: "history_id",
      foreignField: "history_id",
      as: "History"
    }
  },
  {
    $project: {
      "History": {
        $filter: {
          input: "$History",
          as: "his",
          cond: {
            $and: [
              {
                $lt: [
                  "$$his.date",
                  "$date"
                ]
              },
              {
                $eq: [
                  "5e4a8d2d3952132a08ae5764",
                  "$$his.user_id"
                ]
              }
            ]
          }
        }
      },
      data: 1,
      history_id: 1,
      sender_id: 1,
      text: 1,
      date: 1
    }
  },
  {
    $unwind: "$History"
  }
])

MongoPlayGroundLink

answered Apr 29 '20 at 19:21

ngShravil.py

4,742
3
18
30

Thank you buddy that really help me – Sunil Dubey Apr 29 '20 at 19:29
One case is failing here it should return all data if history collection is null and specified id does not match it should return all data see https://mongoplayground.net/p/X2B58D_SuLl – Sunil Dubey Apr 29 '20 at 19:37
Can yo please look into this I tried doesn't work for me – Sunil Dubey Apr 30 '20 at 03:35
I am waiting for your response even created a new question https://stackoverflow.com/questions/61514726/mongodb-if-then-condition-under-filter-how-to-make – Sunil Dubey Apr 30 '20 at 04:13

getting problem with condition in mongodb

1 Answers1