How to Write This SQL Query in Mongodb Syntax

Convert SQL Query to MongoDB syntax

I personally do not know of any good converters, It's just easier to understand each query and translate it on your own, especially as the queries you posted here are not too complex.

---

Query 1:

{
    id: "KVV",
    areo: "RSSO",
    sto: {$nin: ["AMA", "BR"]},
    use: 1
}

Combining with the "select" options:

db.prodw.find(
    {
        id: "KVV",
        areo: "RSSO",
        sto: {$nin: ["AMA", "BR"]},
        use: 1
    },
    {sto: 1, areo: 1, use: 1}
)

Mongo Playground

---

Query 2:

db.collection.aggregate([
  {
    $match: {
      CLIENT_ID: {
        $regex: "^(STK|CP1|LP1WA)"
      }
    }
  },
  {
    $group: {
      _id: {
        "$substr": [
          "$CLIENT_ID",
          0,
          4
        ]
      },
      x: {
        $avg: "$x"
      },
      y: {
        $avg: "$y"
      },
      
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $project: {
      _id: 0,
      SL: "$_id",
      x: 1,
      y: 1
    }
  }
])

Mongo Playground

---

Query Syntax in mongodb for sql like - i am using java

For this issue you should use a regex as in this example:

BasicDBObject query = new BasicDBObject();
Pattern regex = Pattern.compile("the title you are looking for"); // should be m in your case
query.put("title", regex);

How to query MongoDB with like

That would have to be:

db.users.find({"name": /.*m.*/})

Or, similar:

db.users.find({"name": /m/})

You're looking for something that contains "m" somewhere (SQL's '%' operator is equivalent to regular expressions' '.*'), not something that has "m" anchored to the beginning of the string.

Note: MongoDB uses regular expressions which are more powerful than "LIKE" in SQL. With regular expressions you can create any pattern that you imagine.

For more information on regular expressions, refer to Regular expressions (MDN).

how to write mongo query like WHERE student_id AND ( (name like %xyz%) OR ((department like %xyz%)) OR (city like %xyz%) )

The below given should serve as your mongo syntax for the SQL query you mentioned

 db.student.find(  { $and : [  , ,  { $or : [  { name : /.*xyz.*/}, 
 { department : /.*xyz.*/}, 
 { city : /.*xyz.*/} ] }  
 ] }  )

sql query to mongodb?

The simple anwser? No.

The slightly more complex anwser is some people have had luck translating more complex SQL to Mapreduce functions ...

http://rickosborne.org/blog/index.php/2010/02/08/playing-around-with-mongodb-and-mapreduce-functions/

http://rickosborne.org/blog/index.php/2010/02/19/yes-virginia-thats-automated-sql-to-mongodb-mapreduce/

However, that said ... generally speaking you might as well learn mapreduce properly because if the data is in MongoDB already ... you'll really need to know how to properly query MongoDB to get anything meaningful done!

MongoDB has wonderful and helpful docs http://www.mongodb.org/display/DOCS/Advanced+Queries

As well as an easy to use online tutorial: http://try.mongodb.org/

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

What's the correct syntax for given mongodb query?

You can go into your coord index like this

db.restaurants.find({"address.coord.0" : {$lt: -95.754168}});

According to the documentation here,

MongoDB uses the dot notation to access the elements of an array and to access the fields of an embedded document.

E.g. <array>.<index>

Note that the index is 0-based.

---

Related Topics