NoSQLBooster 6.1 Released! official support for MongoDB 4.4

We're so proud to release NoSQLBooster for MongoDB 6.1 today. This version includes official support for MongoDB 4.4, SQL exclude column, SQL UNION, Table view filter row along with some useful improvements and bugfix. For more information on MongoDB 4.4 , see Release Notes for MongoDB 4.4.

MongoDB 4.4 support

NoSQLBooster for MongoDB 6.1 upgrades the MongoDB Node.js driver to the latest 3.6 and embedded MongoDB Shell to 4.4, adding support for all the new mongo shell methods and aggregation operators of MongoDB 4.4.

Union All ($unionWith Stage) and New Aggregation Operators

MongoDB 4.4 adds the $unionWith aggregation stage, providing the ability to combines pipeline results from multiple collections into a single result set. NoSQLBooster 6.1 adds "unionWith" chain method to AggregationCursor and provide the appropriate code snippets and mouse hover information to support code completion. In addition to the enhanced chain method, NoSQLBooster also enhances SQL queries to support SQL UNION and UNION ALL.

1
2
3
4
5
6
7
8
db.suppliers.aggregate()
    .project("state,-_id")
    .unionWith({
        coll: "warehouses",
        pipeline: [{
            $project: { state: 1, _id: 0 }
        }]
    })

Equivalent to the following MongoShell script, but more concise, and easy to write, not to mention code completion.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
db.suppliers.aggregate([{
    $project: {
        state: 1,
        _id: 0
    }
}, {
    $unionWith: {
        coll: "warehouses",
        pipeline: [{
            $project: {
                state: 1,
                _id: 0
            }
        }]
    }
}])

NoSQLBooster 6.1 also enhanced mouse hover for all new aggregation chain methods and aggregation Operators. In addition to method and type definitions, hover is now able to display document and example for these aggregation chained methods.

SQL Query Improvements

Exclude Columns in a SQL Query

NoSQLBooster6.1 extends the syntax of the SQL query. You can exclude the selected columns from the query results by adding a minus sign (eg. -_id) to the name of the columns. The MongoDB query implicitly returns the _id field, see the following example to exclude the _id column from the query result. 

1
SELECT number, concat(first_name,' ',last_name) as name, salary,-_id FROM employees
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//javascript
use NoSQLBoosterSamples
//prepare test data
db.employees.insert([   
    {_id:1, "number":1001,"last_name":"Smith","first_name":"John","salary":62000,},
    {_id:2, "number":1002,"last_name":"Anderson","first_name":"Jane","salary":57500},
]);

//Let's fetch the first_name, last_name and salary fields of the employees available in employees table and 
//sort the result in the descending order by salary.
//use -_id to exclude _id column
mb.runSQLQuery(`

    SELECT number, concat(first_name,' ',last_name) as name, salary,-_id FROM employees
      
`);

The result of the query is as follows, and the _ id field has been excluded

number name salary
1001 John Smith 62000
1002 Jane Anderson 57500

SQL UNION and UNION ALL (MongoDB 4.4+)

In addition to the enhanced aggregate cursor chain method(unionWith), NoSQLBooster also enhances SQL queries to support SQL UNION and UNION ALL. The SQL UNION statement is translated into $unionWith Aggregation Stage of MongoDB.

UNION Operator(does not contains duplicates)

The UNION operator selects only distinct values by default. 

1
SELECT state, -_id FROM suppliers UNION SELECT state, -_id from warehouses
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//javascript

use NoSQLBoosterSamples

db.suppliers.drop(); //prepare data
db.suppliers.insertMany([
  { _id: 1, supplier: "Aardvark and Sons", state: "Texas" },
  { _id: 2, supplier: "Bears Run Amok.", state: "Colorado"},
])

db.warehouses.drop();
db.warehouses.insertMany([
  { _id: 2, warehouse: "B", region: "Central", state: "Colorado"},
  { _id: 3, warehouse: "C", region: "East", state: "Florida" },
])

 mb.runSQLQuery(`
    SELECT state, -_id FROM suppliers UNION SELECT state, -_id from warehouses
`)

The result of the query is as follows

state
Texas
Florida
Colorado

As can be seen from the returned documents, the result set does not contain duplicates.

UNION ALL Operator(allow duplicate values)

To allow duplicate values, use UNION ALL:

1
SELECT state, -_id FROM suppliers UNION ALL SELECT state, -_id from warehouses

The result of the query is as follows

state
Texas
Colorado
Colorado
Florida

MongoDB 4.4 New Expressions as SQL Functions

NoSQLBooster 6.1 allows all new Mongodb4.4 aggregation expressions to be used as SQL function in SQL statements. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
use NoSQLBoosterSamples
db.myColl.drop(); //prepare data
db.myColl.insertMany([
   { _id: 1, name: "cup after cup of coffee" },
])

mb.runSQLQuery(`

SELECT 
    binarySize('重庆小面') as binary_size, -- 12 Each character is encoded using three bytes. Returns the size of a given string or binary data value’s content in bytes.
    bsonSize(toJS(a=1,b=2)) as bson_size, -- 27 Returns the size in bytes of a given document (bsontype Object) 
    first([1,2,3]) as first_item, --1 Returns the first element in an array.
    last([1,2,3]) as last_item, --3 Returns the last element in an array.
    isNumber(123) as int_is_number, --true checks if the specified expression resolves to one of the following numeric BSON types:
    isNumber('123') as str_is_not_number, --false
    replaceOne(name,'cup','CUP') as repalce_one, --Replaces the first instance of a matched string in a given input.
    replaceAll(name,'cup','Cup') as repalce_all, -- Replaces all instances of a matched string in a given input.
    -_id

FROM myColl       
`)

The result of the query is as follows

1
2
3
4
5
6
7
8
9
10
{
    "binary_size" : 12,
    "bson_size" : 27,
    "first_item" : 1,
    "last_item" : 3,
    "int_is_number" : true,
    "str_is_not_number" : false,
    "repalce_one" : "CUP after cup of coffee",
    "repalce_all" : "Cup after Cup of coffee"
}

MongoDB 4.4 Log Viewer

Starting in MongoDB 4.4, mongod/mongos instances now output all log messages in structured JSON format. NoSQLBooster 6.1 supports parsing the structured JSON format log of the new MongoDB 4.4, as well as the plain text log before MongoDB 4.4.

Other Improvements

Table View - Toggle Filter Row

The new filter row allows end-users to filter grid data by typing text directly into the row. The filter row is scoped only to data on the current page.
To display filter rows at the top of the table view, press "CTRL+SHIFT+L" or click the "filter" icon in the data View toolbar.

Filter Row

AutoSave Working State Every Minute

NoSQLBooster 6.1 automatically saves changes to the workspace every minute you work. You can toggle this option in the "Menu -> Options -> AutoSave Working State Every Minute".

Add the Run/Execute Command to the Right-Click Menu of the Script Editor

Run/Execute Command to the Right-Click Menu

Minor Improvements and Bugfix

  • Improved, upgrade MongoDB Node.js driver to the latest 3.6.
  • Improved, embedded MongoDB Shell to 4.4.0.
  • Improved, saving, and restoring table view state.
  • Improved, split the paths of mongo shell and mongo tools in the configuration, allowing the two paths to be configured separately.
  • Fixed, automatically resize the in-place editor.
  • Fixed, Favorites, when the file is saved, the file should be saved first over collection in the favorites menu item.

Patch Releases

Version 6.1.1-6.1.3

Bugfix

  • Added, Add the Run/Execute Command to the Right-Click Menu of the Script Editor.
  • Improved, allow "--no-sandbox" switch to linux AppImage.
  • Fixed, Connection Tree- connection node read-only icon status error.

Version 6.1.4

Bugfix

  • Fixed, Query Builder, If the two rules have the same name, the generated query ignores "AND"

Version 6.1.5-6.1.6

Bugfix

  • Fixed, Right-Click Drop User or Drop role appears to try and drop from the wrong database. ref#
  • Fixed, Ports not accepted with Atlas mongodb+srv URIs over SSH. ref#

Version 6.1.7

Bugfix

  • Fixed, Importing documents from the clipboard does not work
  • Fixed, MongoImport/MongoExport GUI, fixed a connection error when the connection is "mongodb+uri" over SSH
  • Added, MongoImport GUI, add cli option --useArrayIndexFields (Interpret natural numbers in fields as array indexes when importing csv or tsv files. New in mongoimport version 100.0.0.)
  • Added, Connection SSL Options TAB, Added support for custom Server Name Indication (SNI) when using SSL connections

Version 6.1.8

Bugfix

  • Fixed, When exporting a CSV file, the BOM character is mistakenly added to each chunk.

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.