How to convert sql query with exist into mongodb query

Question

I have two documents on mongodb, these are percentages and items. I'm good at SQL, I can write PLSql query as follows but i can not convert to mongodb query. Because my mongodb level of knowledge is at the beginning.

Actually I know I have to use $gt for the and condition. But I don't know how I can say not exists or union keyword for mongodb. How can I write mongodb query? which keywords should i search for?

select p.*, "to_top" as list 
  from percentages p
 where p.percentage > 5
   and p.updatetime > sysdate - 1/24
   and not exists (select 1
                     from items i
                    where i.id = p.p_id
                      and i.seller = p.seller)
 order by p.percentage desc
union
select p2.*, "to_bottom" as list 
  from percentages p2
 where p2.percentage > 5
   and p2.updatetime > sysdate - 1/24
   and exists (select 1
                  from items i2
                 where i2.id = p2.p_id
                   and i2.seller = p2.seller)
order by p2.percentage desc

You does your collection looks like and what is it's corresponding output that you want? — ngShravil.py, Jun 14 '20 at 12:13

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

There is no UNION for MongoDB. Luckely, each query is performed on the same collection and have very close condition, so we can implement "Mongo way" query.

Explanation

Normally, alsmost all complex SQL queries are done with the MongoDB aggregation framework.

We filter document by percentage / updatetime. Explanation why we need to use $expr
SQL JOIN / Subquery is done with the $lookup operator.
SQL SYSDATE in MongoDB way can be NOW or CLUSTER_TIME variable.

db.percentages.aggregate([
  {
    $match: {
      percentage: { $gt: 5 },
      $expr: {
        $gt: [
          "$updatetime",
          {
            $subtract: [
              ISODate("2020-06-14T13:00:00Z"), //Change to $$NOW or $$CLUSTER_TIME
              3600000
            ]
          }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "items",
      let: {
        p_id: "$p_id",
        seller: "$seller"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [ "$$p_id", "$id"]
                },
                {
                  $eq: [ "$$seller", "$seller"]
                }
              ]
            }
          }
        },
        {
          $limit: 1
        }
      ],
      as: "items"
    }
  },
  {
    $addFields: {
      list: {
        $cond: [
          {
            $eq: [{$size: "$items"}, 0]
          },
          "$to_top",
          "$to_bottom"
        ]
      },
      items: "$$REMOVE"
    }
  },
  {
    $sort: { percentage: -1 }
  }
])

MongoPlayground

Note: The MongoDB aggregation has the $facet operator that allows to perform different queries on the same collection.

SCHEMA:

db.percentages.aggregate([
  {$facet:{
    q1:[...],
    q2:[...],
  }},
  //We apply "UNION" the result documents for each pipeline into single array
  {$project:{
    data:{$concatArrays:["$q1","$q2"]}
  }},
  //Flatten array into single object
  {$unwind:"$data"}
  //Replace top-level document
  {$replaceWith:"$data"}
])

MongoPlayground

score 0 · Answer 2 · answered Jun 14 '20 at 20:07

0

why you don't import your mangoDB data into oracle and use sql(that is more easy and powerful than mango.)

answered Jun 14 '20 at 20:07

Muhammad Faisal

54
1
5

Yep, oracle more easy for me but I thought mongodb would produce faster results. my nodejs app has to be faster. Also i wanted to learn mongo. – gencero Jun 14 '20 at 20:54

How to convert sql query with exist into mongodb query

2 Answers2

Explanation