SQL Query to Mongodb

SQL Query to Mongo DB to get the sum with group by

You can do this in a couple of different ways, i'll show the simplest approach using $lookup which is the Mongo equivalent of sql join ( or rather a "left outer join").

db.employees.aggregate([
  {
    "$lookup": {
      "from": "assignment",
      "localField": "empno",
      "foreignField": "empno",
      "as": "assignments"
    }
  },
  {
    "$lookup": {
      "from": "finalreport",
      "localField": "assignments.assid",
      "foreignField": "assid",
      "as": "reports"
    }
  },
  {
    $project: {
      points: {
        $sum: {
          $map: {
            input: "$reports",
            in: "$$this.points"
          }
        }
      },
      empno: 1
    }
  }
])

Mongo Playground

How to convert sql query with exist into mongodb query

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.

1. We filter document by percentage / updatetime. Explanation why we need to use $expr
2. SQL JOIN / Subquery is done with the $lookup operator.
3. 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

SQL Query to Mongo DB

MongoDB as noSQL distributed document store differs significantly from RDBMS SQL databases , you need in general to denormalize the relational model and transform it to document model avoiding such type of join queries as much as possible , in case you cannot avoid it, you can still use aggregation / $lookup to achieve similar results...

SQL equivalent queries in MongoDB

You can achieve the behaviour with a $expr in a simple find. Not a must to use aggregation pipeline.

db.collection.find({
  $expr: {
    $or: [
      {
        // $eq: [<score_min>, -1]
        $eq: [
          <score_min>,
          -1
        ]
      },
      {
        $and: [
          {
            // $gte: ["$Scores", <score_min>]
            $gte: [
              "$Scores",
              <score_min>
            ]
          },
          {
            $lte: [
              // $gte: ["$Scores", <score_max>]
              "$Scores",
              <score_max>
            ]
          }
        ]
      }
    ]
  }
})

Here is the Mongo playground for your reference.

---

Related Topics