MongoBooster 4.0 Released! Query MongoDB with SQL

Today, we are extremely pleased to announce the release of MongoBooster 4.0. This major upgrade includes Query MongoDB with SQL, ES7 Async/Await support and more.

Although we are showing screenshots of MongoBooster for Windows, all these new features are available for Mac OS X and Linux as well.

Query MongoDB with SQL

With MongoBooster V4, you can run SQL SELECT Query against MongoDB. SQL support includes functions, expressions, aggregation for collections with nested objects and arrays.

Let's look at how to use the GROUP BY clause with the SUM function in SQL.

Instead of writing the MongoDB query which is represented as a JSON-like structure

1
2
3
4
5
6
7
8
db.employees.aggregate([
    {
        $group: {
            _id: "$department",
            total: { $sum: "$salary" }
        },
    }
])

You can query MongoDB by using old SQL which you probably already know

1
2
3
4
5
mb.runSQLQuery(`

  SELECT department, SUM(salary) AS total FROM employees GROUP BY department

`);

Open a shell tab, enter the above script. MongoBooster also offers a "runSQLQuery" code snippets. Just type a snippet prefix "run", and enter "tab" to insert this snippet, then press "Command-Enter" to execute it and get the result.

Query MongoDB with SQL Result Tab

Just Click on the "console.log/print" tab to show the equivalent MongoDB query:

Query MongoDB with SQL Console Tab

  • The build-in SQL language service knows all possible completions, SQL functions, keywords, MongoDB collection names and field names. The IntelliSense suggestions will pop up as you type. You can always manually trigger the auto-complete feature with Ctrl-Shift-Space.
  • MongoBooster supports in-place editing in result tree view. Double-click on any value or array element to edit. Pressing shortcut "Esc" will return the previous value and exit the editor.
  • If you want the results not to be edited directly, you can enable the "read-only" mode by clicking the lock button in the toolbar.
  • SQL features are not natively supported by MongoDB. The SQL query is validated and translated into a MongoDB query and executed by MongoBooster. The Equivalent MongoDB Query can be viewed in console.log tab.

Click here to learn how to run SQL SELECT Query against MongoDB

SQL Query Features

  • Access data via SQL including WHERE filters, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT
  • SQL Functions (COUNT, SUM, MAX, MIN, AVG)
  • Aggregation Pipeline Operators as SQL Functions (dateToString, toUpper, split, substr...)
  • Provide a programming interface (mb.runSQLQuery) that can be integrated into your script
  • Autocomplete for keywords, MongoDB collection names, field names and SQL functions

See the features and SQL examples supported by the MongoBooster.

ES7 Async/Await support

Within this release, we have also added support for ES7 Async/Await in MongoBooster shell.

For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
function delay(ms) {
    return new Promise((resolve)=> {
        setTimeout(resolve, ms);
    });
}

async function go() {
    await delay(1000);//wait 1s
    
    return db.orders.find().limit(5); 
}

go();

As a comparison， MongoBooster has a build-in function await which is a common js method, not a keyword. It can await a promise or a promise array. Note this await function is different from es7 await, this await function may be used in functions without the async keyword marked.

The above example can be written with the MongoBooster await function.

1
2
3
4
5
6
7
8
9
function delay(ms) {
    return new Promise((resolve)=> {
        setTimeout(resolve, ms);
    });
}

await(delay(1000));

db.orders.find().limit(5);

Intellisense for Node.js Required Packages

Within this release, MongoBooster also offers Intellisense experience for Node.js required packages. The IntelliSense suggestions will pop up as you type and automatically complete Javascript method names, variables, etc. You can always manually trigger it with Ctrl-Shift-Space. Out of the box, Ctrl-Space, Alt-Space are acceptable triggers.

For example:

Intellisense for Node.js Required Packages

Click here to learn how to use Node.js Modules in Your Script

Minor Enhancement

More Useful MongoDB Shell Extensions

Global fetch() method

MongoBooser integrates node-fetch to bring window.fetch to MongoDB Script.

Global scope: fetch

1
2
3
console.log(await(await(fetch('https://github.com')).text()));

console.log(await(await(fetch('https://api.github.com/users/github')).json()));

cursor.not() method

The cursor.not(operator-expression) method performs a logical NOT operation on the specified "operator-expression" and selects the documents that do not match the "operator-expression". This includes documents that do not contain the field.

Consider the following example which uses the pattern match expression //:

1
2
3
4
5
db.inventory.where("item").not(/^p.*/)

//the equavalent MongoDB JSON-like Query 

db.inventory.find( { item: { $not: /^p.*/ } } )

The query will select all documents in the inventory collection where the item field value does not start with the letter p.

cursor.type() method

The cursor.type( BSON type number | String alias ) method selects the documents where the value of the field is an instance of the specified BSON type. Querying by data type is useful when dealing with highly unstructured data where data types are not predictable.

The following queries return all documents where zipCode is the BSON type string:

1
2
3
4
5
6
7
db.addressBook.where("zipCode").type(2);
db.addressBook.where("zipCode").type("string");

//the equavalent MongoDB JSON-like Query 

db.addressBook.find( { "zipCode" : { $type : 2 } } );
db.addressBook.find( { "zipCode" : { $type : "string" } } );

Click here to view Available BSON Types

Group History Scripts by Date

In the previous version, only up to 30 scripts can be saved. We got feedback from users, they say this value is too small. In the new version, we allow to save up to 1000 history scripts. Histrory entries can be grouped by date, which is easier to retrieve and query.

Group history Scripts by Date

Hide/Show System Objects in Object Explorer

The Databases node of Object Explorer contains system objects such as the admin databases. To hide system objects in Object Explorer

  1. Click Options Menu
  2. Click Explorer/Connection Tree
  3. Toggle "Show System Object" on/off to hide/show system object

This change will take effect immediately.

Bugfix and other improvements

  • Added, Menu -> Options-> Options That May Affect Performance -> Enable Fields/Collection Auto Complete, default value:true
  • Added, Menu -> Options-> Output panel -> Result JSON View Format -> Show Int32 as NumberInt("xxx"), default value:false
  • Added, a new SSH config option to ask for SSH password each time
  • Improved, adjust sub-items of options menu
  • Improved, enable "Query by example" action for AggregationCursor
  • Improved, enable in-place-edit feature for AggregationCursor
  • Improved, SSL connection configuration
  • Fixed, Try finally connection close issue in discovering ReplicaSet action

Hotfix

Hotfix in MongoBooster 4.0.2

  • Added, add "stop script", "pause log" and "clear-log" buttons to the run-time log dialog.
  • Fixed, SQL Query assert "failed : no shortName" when the collection name can not be used as a JavaScript variable name.
  • Improved, Linux: Add category entry "Development" to the *.desktop file.
  • Improved, Limit the maximum number of the run-tim log records to 10,000.

Thank you!

Please visit our feedback page or click the “Feedback” button in the app. Feel free to suggest improvements to our product or service. Users can discuss your suggestion and vote for and against it. We’ll look at it too.