Query MongoDB with SQL Editor

SQL Editor translates SQL commands to MongoDB queries and runs them automatically. It uses SQL syntax based on SQL Server dialect.
Following are the most important SQL syntax rules you should use with SQL Editor:

• SELECT, INSERT, DELETE and UPDATE statements are supported.
• Use double quotes to escape names ("collection-name").
• Use single quotes to escape strings ('some text').
• Use TOP(n) clause to set query limit (select top(10) user_id...).
• Use -- for single line comments (-- It's a comment).
• Use ; to delim statements.
• You can use BinData(), CSUUID(), Date(), HaxData(), ISODate(), NumberDecimal(), ObjectId(), Timestamp() and UUID() MongoDB functions in SQL statements.

Using SQL Editor

1. Setting database

SQL Editor tool executes commands against a MongoDB database. When you open SQL Editor it is automatically associated with an active database selected in DB Explorer. All SQL commands will be executed in the context of this database. The database name is displayed on the window tab. Also you can use popup hint to view the associated database details. SQL Editor database cannot be changed. To work with another database you should select it in the DB Explorer and open a new SQL Editor window.

2. Executing commands in SQL Editor

Using SQL Editor you can execute all the commands with button (F5 key), current command under cursor position with button (F6 key), selected text command with button (F9 key) or just parse and validate all commands without executing with button.

3. Working with result

SQL Editor result panel consists of three pages - Output, Documents View and Statements View.

Output tab displays statements information like execution time, count of affected documents, warning and error messages.

Documents View tab shows returned documents. You can view the documents using Tree, Table and Text view modes.

Statements View tab shows MongoDB commands converted from processed SQL statements. To get such statements without execution you can press Parse button .

SQL to MongoDB mapping

select * from people

db.people.find()

select top(10) user_id, status, age from people 
where status = 'A' and age >= 51
order by user_id

db.people.find(
    {
      $and : [{
          "status" : "A"
        }, {
          "age" : {
            $gte : 51
          }
        }]
    },
    {
      "user_id" : 1,
      "status" : 1,
      "age" : 1
    }
).sort({ "user_id" : 1 }).limit(10)

select * from people where
status is NOT NULL

db.people.find({ "status" : { $ne : null } })

select * from people where 
status in ('A', 'B', 'C')

db.people.find(
    {
      "status" : {
        $in : ["A", "B", "C"]
      }
    }
)

select * from people 
where status like 'A%'

db.people.find({ "status" : { $regex : /A.*/ } })

insert into people(user_id, status, age) values
(1, 'A', NumberInt(42))

db.people.insert({
  "user_id" : 1,
  "status" : "A",
  "age" : 42
})

insert into people(user_id, status, age) values
(1, 'A', 30), (2, 'B', 40)

db.people.insert(
    [
        {
          "user_id" : 1,
          "status" : "A",
          "age" : 30
        },
        {
          "user_id" : 2,
          "status" : "B",
          "age" : 40
        }
    ]
)

insert into table1(_id, date, timestamp, long)
values (
    ObjectId('507f191e810c19729de860ea'), 
    ISODate('2022-05-24T21:01:40.337Z'), 
    Timestamp(120292993), 
    NumberLong(123456)
)

db.table1.insert(
{
  "_id" : ObjectId("507f191e810c19729de860ea"),
  "date" : ISODate("2022-05-24T21:01:40.337Z"),
  "timestamp" : Timestamp(0, 120292993),
  "long" : NumberLong(123456)
})

update "people" set
age=56, status='A' where user_id = 3

db.people.updateMany(
    { "age" = 56, "status" = "A" }, 
    { "user_id" : 3 }
)

delete from people
where status <> 'A'

db.people.deleteMany({ "status" : { $ne : "A" } })