Announcing NoSQLBooster 4.7!

Today we're happy to bring you NoSQLBooster 4.7. We've got a few new items that we’re proud to highlight! Optimizes connection management, making the new shell open more smoother, enhanced Object Explorer (Users and Roles node, Collection Schema and validator), watch Collection/Database/Deployment data changes, new SQL Functions(type conversion, string operations,date operations) and MongoDB ObjectId ↔ Timestamp Converter ...

Let’s dive in and get an overview of what’s coming in NoSQLBooster 4.7!

Hotfix

Hotfix in 4.7.5

  • Fixed, DBRef is missing $ref, $id, $db attributes #ref
  • Fixed, Cannot use Object.bsonsize bug #ref
  • Fixed, in the case of mixed Chinese and English input, the font is not displayed correctly bug
  • Fixed, a Minor Python Query Code Generator bug
  • Fixed, faker.finance.amount method stackoverflow bug
  • Fixed, "NumberLong","NumberDecimal" method typing definition issue
  • Fixed, View/Download files in GridFS collection, fails on Windows when file name contains braces / colon issue #ref
  • Changed, set the default export batchsize value to 1000

Hotfix in 4.7.3 and 4.7.4

  • Added, Add %%database%% variable to default collection window template #ref
  • Fixed, NumberLong conversion fails for negative numbers #ref
  • Fixed, regression "search using uuid helpers" bug #ref
  • Fixed, regression "table view header breaks the columns of the table when column name contains commas" bug. #ref

Hotfix in 4.7.2

The release 4.7.2 is a minor update, it is mainly to speed up the opening of the Script editor, modify default code template for opening collection.

  • Improved, speed up the opening of the Script editor
  • Changed, append running script indicator to output panel
  • Added, option: "AutoExecute Script When Collection Node is Double-Clicked", toggle this option to enable/disable auto-execute script when collection/view node is double-clicked.
  • Fixed, connect mongodb+srv URI using SSH tunnel #ref
  • Fixed, UUID function without arguments returns an error #ref
  • Fixed, table view header breaks the columns of the table when column name contains commas. #ref

Hotfix in 4.7.1

  • Fixed, Cut/Copy/Paste in editor context menu doesn't work with MacOS. #ref
  • Fixed, the ability to increase the paging size does not work correctly.

Optimizes Connection Management

In previous versions, each tab has an embedded MongoDB shell, and each embedded Shell will acquire an exclusive MongoDB client connection, the main purpose of this is to simplify programming and debugging, but the simple one-tab-one-connection model wastes connection resources and slows the opening of new tab. In the new version, we use an all-application shared connection pool, the multiple-tabs-one-connection mechanism can greatly improve the efficiency of "open tab", makes the application's UI response smoother. Please download to give it a shot now.

Enhanced Object Explorer

NoSQLBooster 4.7 greatly enhanced Object Explorer, new global user and role nodes, new collection schema and validator nodes, database node's storage size ...

Database object explorer

Database Object Explorer

Read More

NoSQLBooster for MongoDB 4.6 Released! official support for MongoDB 4.0

We're so proud to release NoSQLBooster for MongoDB 4.6 today. This version includes official support for MongoDB 4.0 along with some useful minor improvements and bugfix. For more information on MongoDB 4.0 , see Release Notes for MongoDB 4.0.

Hotfix

Hotfix in MongoBooster 4.6.1

  • Fixed, a serious bug that prevented the query builder and aggregate method from working. #ref

MongoDB 4.0 support

NoSQLBooster for MongoDB 4.6 upgrades embedded MongoShell to 4.0, adds support for all the new shell methods and operations of MongoDB 4.0. Multi-Document Transactions and New Type Conversion Operators.

WARNING: MongoDB 2.4 reached end of life in March of 2016, MongoDB Node.js 3.x driver dropped support for MongoDB 2.4 or below. Beginning in NoSQLBooster for MongoDB 4.5, versions of MongoDB server prior to version 2.6 are no longer supported.

Transactions

Minor Improvements

Connection troubleshoot

Added connection log console to show debugging data when a "test connection are being established.

connection_troubleshoot

Read More

NoSQLBooster 4.5 released, with significant improvements to SQL Query for MongoDB

By Qinghai | February 12, 2018

Today, we're happy to announce the release of NoSQLBooster 4.5. This version introduces a bunch of significant improvements SQL Query for MongoDB along with some minor improvements and bugfix.

If you’re not familiar with NoSQLBooster(formerly MongoBooster), it is a shell-centric cross-platform(Windows, Mac, Linux) GUI tool for MongoDB v2.6-3.6, which provides fluent query builder, SQL Query, update-in-place, ES2017 syntax support and true intellisense experience.

NoSQLBooster Download Link
How to Query MongoDB with SQL?

Hotfix

Hotfix in MongoBooster 4.5.6

  • Fixed, unable to do a CSV export, throw "mongo.convertBsonObjToCsvStr is not a function"

Hotfix in MongoBooster 4.5.5

  • Fixed, AppImage icon does not show up in Ubuntu GNOME #ref
  • Fixed, sorting a table by Int or Double column sorts them as a string #ref
  • Added, two new items to context menu of "table" view. 'Copy Document(s) to Clipboard - Tab-Separated Values' and 'Copy Document(s) to Clipboard - Comma-separated Values'
  • Improved, "showIndex" code template for MongoDB sharded cluster

Hotfix in MongoBooster 4.5.4

  • Added, auth option: Always ask for password when connecting to MongoDB database #ref
  • Improved, export to CSV, Add a BOM character at the beginning of the csv to make Excel displaying special characters correctly
  • Fixed, a minor UI bug in displaying recent history scripts

Hotfix in MongoBooster 4.5.3

  • Fixed, east asian language input does not work properly #ref
  • Fixed, $unwinds, cannot select middle item on the tree #ref
  • Fixed, instanceof keyword is not working as intended bug #ref
  • Fixed, mongo.NumberInt(${record[key]}) throws an error when record[key] = 10004743968 #ref
  • Fixed, Int64 truncated as Int32 bug in table & tree view #ref
  • Fixed, out of range bug when importing a table-view from MSSQL with more than 1000 rows #ref
  • Fixed, convert ObjectId to hexadecimal string bug #ref

Hotfix in MongoBooster 4.5.2

  • New, "New Shell Tab..." and "New SQL Query Tab..." buttons to main toolbar
  • New option, Menu->Options-> Output Panel-> Keeping Input Focus on the Editor After Running (default false)
  • Improved, If the script does not need to be executed immediately, the editor window is maximized by default
  • Improved, Using IndexedDB instead of local storage to store history scripts
  • Changed, Export features will be disabled after the trial period

Hotfix in MongoBooster 4.5.1

  • New, SQL Query: add RegExp Constructor Function e.g. select * from table where name = RegExp('^hai','i')
  • Fixed, a bug when storing history script ref
  • Fixed, a shell and C# generator bug ref
  • Fixed, NoSQLBoooster runs out of Memory with Big BSON Documents => blank screen. ref
  • Enabled MongoDB Enterprise Connection in free edition

SQL Query Improvements

The "toJS" SQL Function and named parameter

NoSQLBooster 4.5 supports named parameter in SQL function. The new “toJS” helper function transforms the named parameters and arithmetic operator to a JSON object, also transforms an ordinary parameter list into an array.

1
2
3
4
5
6
toJS(k='v'); //result {k:'v'}
toJS(k="v"); //result {k:'$v'}, Double quotes quote object names
toJS(k=v); //result {k:'$v'}, without quote, v is a object name
toJS(k>5, k<=10); //result { "k": { "$gt": 5, "$lte": 10} }
toJS(a=1, b=toJS(b1=2, b2='b2')); //result {a : 1, b : {b1 : 2, b2 : "b2"}
toJS(1, 2,'3'); // result [1,2,'3'];

With named parameter and "toJS" helper function, you can query complex objects or pass JSON-object parameter to a SQL function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--elemMatch, named parameter and Arithmetic operators
--cool stuff, (score>8, score<=10) will be translated as {"score": { "$gt": 8, "$lte": 10 }}
SELECT * FROM survey WHERE results =elemMatch(item='abc', score>8, score<=10)

--date timezone, named parameter
SELECT year(date="date", timezone='America/Chicago') as year FROM sales

--query object type value, object_field: { a : 1, b : {b1 : 2, b2 : "b2"}
SELECT * FROM collection WHERE object_field = toJS(a=1, b=toJS(b1=2, b2='b2'))

-- text search with full text search options
SELECT * FROM article WHERE
$text = toJS($search='cake', $language='en', $caseSensitive=false, $diacriticSensitive=false)

SELECT literal(toJS(k>5, k<=10)) FROM collection

Read More

MongoDB 3.6 Change Stream Example with NoSQLBooster

By Qinghai | January 15, 2018

MongoDB 3.6 have a new change notification API, called a “change stream.” Change streams allow applications to access real-time data changes without the complexity and risk of tailing the oplog. Applications can use change streams to subscribe to all data changes on a collection and immediately react to them.

This post is a step by step tutorial on how to open and configure change streams with NoSQLBooster.

Change streams are available for replica sets or sharded clusters with replica set shards. You cannot open a change stream against a standalone mongod. The example below assume that you have connected to a MongoDB 3.6 replica set and have accessed a database. If you want to deploy a replica set in a development or test environment, please refer to this link.


Prepare Demo Data

Insert the following demo data to MongoDB. Open a shell tab Ctrl-T and execute the following script to get the unicorns collection.

1
2
3
4
5
6
7
8
9
use test

db.unicorns.insert([
{ "name" : "Horny", "weight" : 600 },
{ "name" : "Aurora", "weight" : 450},
{ "name" : "Unicrom", "weight" : 984},
{ "name" : "Roooooodles", "weight" : 590},
{ "name" : "Solnara", "weight" : 550}
])

Setup Watch on Collection

Here is the most important part. Let's setup watch on collection, so that we can listen for any changes in the operation that occur in the collection, such as insert, delete, replace, update, invalidate and so on.

Right-click the collection node in the connection tree and select "Watch Collection" item like this:

Watch Collection Menu Item

Read More

NoSQLBooster for MongoDB 4.3 Released! official support for MongoDB 3.6

We're so proud to release NoSQLBooster for MongoDB 4.3 today. This version includes official support for MongoDB 3.6 along with some useful minor improvements and bugfix. For more information on MongoDB 3.6 , see Release Notes for MongoDB 3.6.

MongoDB 3.6 support

NoSQLBooster for MongoDB 4.3 upgrades dependent MongoDB Node.js driver to 3.0, adds support for all the new shell methods and operations of MongoDB 3.6. DNS-constructed Seedlist mongodb+srv, Change Streams, Sessions, JSON Schema , New Query Operators and New Aggregation Stages and Operators.

WARNING: MongoDB 2.4 reached end of life in March of 2016, MongoDB Node.js 3.x driver dropped support for MongoDB 2.4 or below. Beginning in NoSQLBooster for MongoDB 4.3, versions of MongoDB server prior to version 2.6 are no longer supported.

DNS-constructed Seedlist mongodb+srv

In addition to the standard connection format, NoSQLBooster for MongoDB 4.3 quick connect (connect from URI...) support a DNS-constructed seedlist.

To use it, Click Menu -> File -> Quick Connect ... (Enter mongodb+srv://) or Click Connect -> From URI .. in the main toolbar.

DNS-constructed Seedlist mongodb+srv

Change Streams (db.collection.watch)

NoSQLBooster for MongoDB 4.3 adds shell method and code snippet "db.collection.watch" to open and watch a change stream.

To use it

  • Method1: Enter "db.collection.watch", pop-up code complete dialog (Ctrl-Shift-Space), select "watch" snippet.
  • Method2: Right-click collection node in the connection tree, and click -> Watch Collection.

db.collection.watch

Read More

NoSQLBooster for MongoDB (formerly MongoBooster) 4.2 Released! Convert MongoDB queries to Nodejs, Java, C#, Python and MongoShell.

Today, we're happy to announce the release of NoSQLBooster for MongoDB 4.2 (formerly MongoBooster). This release includes query code generator for various target languages and one-click explain to return the queryPlanner information.

One important change worth to mention is that we changed our name from MongoBooster to NoSQLBooster for MongoDB because of trademark reasons. Because of the name change, self-updating from previous versions to 4.2 will not work and you will have to manually download and install version 4.2. The user data and license information of previous versions will be automatically migrated after the installation is complete.


Query Code Generator

NoSQLBooster for MongoDB 4.2 comes with query code generator that allows users to translate MongoDB queries (find, aggregate or SQL query) to various target languages: MongoDB Shell, JavaScript (Node.js), Java, C# and Python.

To use query code generator:

  1. Enter find, aggregate or SQL Query statement, execute it and get the results. (tip: With Release 4.1, you can even use visual query builder)
  2. Click the "Query Code Generator" button in the toolbar of result view and pop-up the "Query Code Generator" dialog
  3. Choose your target language from the drop-down list. The "Query Code Generator" currently supports MongoDB Shell, Javascript Node.js ES5, Javascript Node.js ES6 Promise, Javascript Node.js ES7 Async/Await, Python (PyMongo 3.x), C# (2.x driver) and Java (3.x driver)
  4. Copy generated code to clipboard

Query Code Generator

Read More

MongoBooster 4.1 Released! Visual Query Builder and Scripts History Search

Today, we're happy to announce the release of MongoBooster 4.1. This release includes visual query builder, scripts history search and a few minor updates.


Visual Query Builder

MongoBooster 4.1 comes with visual query builder. The two-way query builder could help you construct and display complex MongoDB find statements even without the knowledge of the MongoDB shell commands syntax.

To use query builder:

  1. Use the Query Builder button in the editor toolbar.
  2. Right-click the collection node in the connection tree, execute "Show Query Builder..."
  3. Popup Command Palette (Ctrl-Shift-P), enter "Query Builder"

Query Builder

Read More

How to Query MongoDB with SQL SELECT

By Qinghai | September 12, 2017

With NoSQLBooster for MongoDB, you can run SQL SELECT Query against MongoDB. SQL support includes functions, expressions, aggregation for collections with nested objects and arrays.
See the features and SQL examples supported by the NoSQLBooster for MongoDB.

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

`);

Features

  • Access data via SQL including WHERE filters, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT
  • SQL Functions (COUNT, SUM, MAX, MIN, AVG)
  • SQL Functions (Date, String, Conversion)
  • SQL Equi JOIN and Uncorrelated SubQuery
  • 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

Please note that MongoDB does not natively support SQL features. The SQL query is validated and translated into a MongoDB query and executed by NoSQLBooster for MongoDB. The Equivalent MongoDB Query can be viewed in the console.log tab.

It should be mentioned that there is a tutorial on NoSQLBooster SQL Query for MongoDB in the lower left “Samples” pane. With this tutorial, you can learn and understand how to use NoSQLBooster SQL Query for MongoDB. Better yet, all SQL Functions provide the appropriate code snippets and mouse hover information and support code completion.

SQL Query for MongoDB Tutorial


Getting Started

For example, the employees' collection has the following fields including number, first_name, last_name, salary, department, and hire_date.

Prepare Demo Data

Insert the following demo data to MongoDB. Open a shell tab "Command-T" and execute the following script to get the employees collection.

1
2
3
4
5
6
db.employees.insert([   
{"number":1001,"last_name":"Smith","first_name":"John","salary":62000,"department":"sales", hire_date:ISODate("2016-01-02")},
{"number":1002,"last_name":"Anderson","first_name":"Jane","salary":57500,"department":"marketing", hire_date:ISODate("2013-11-09")},
{"number":1003,"last_name":"Everest","first_name":"Brad","salary":71000,"department":"sales", hire_date:ISODate("2017-02-03")},
{"number":1004,"last_name":"Horvath","first_name":"Jack","salary":42000,"department":"marketing", hire_date:ISODate("2017-06-01")},
])

Select All Fields

First, click on employees collection, then click on "SQL Query Tab" in the tab toolbar or use the "Command-Alt-T" keyboard shortcut. A basic "SELECT * from employees" is automatically generated for us! NoSQLBooster for MongoDB also offers a "runSQLQuery" code snippets. Just type a snippet prefix "run", and press "tab" to insert this snippet.

alt text

Just execute the query by clicking the execute button or use the "Command-Enter" keyboard shortcut. This would produce the result as shown below.

alt text

  • NoSQLBooster for MongoDB provides in-place editing in result tree view. Double-click on any value or array element to edit. Pressing "Esc" 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.

Select Individual Fields and Field Name Auto-complete

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.

alt text

The built-in SQL language service knows all possible completions, SQL functions, keywords, MongoDB collection names, and field names. The IntelliSense suggestions pop up as you type. You can always manually trigger it with "Ctrl-Shift-Space"}}}. Out of the box, "Ctrl-Space"}}}, "Alt-Space"}}} are also acceptable triggers.

View the Equivalent MongoDB Query

How to show the equivalent MongoDB query?

  • Method 1: Turn on the Verbose Shell option, Main Menu-> Options -> Verbose Shell(setVerboseShell)
  • Method 2: Click the "Code" button in the upper-right corner of the editor toolbar to show the equivalent MongoDB query.

alt text

As you know, NoSQLBooster for MongoDB supports mongoose-like fluent Query API, Click Menu-> Options -> Translate SQL to MongoDB Shell Script, click "Translate SQL to NoSQLBooster for MongoDB Fluent API". Re-execute the script, the equivalent fluent MongoDB query will be shown in the "console.log/print" tab.

alt text

Use String and Date SQL Functions

This time, we want to find all employees who are hired this year, and display first_name and last_name as full name. Please enter the following SQL statement and click execute button:

1
2
3
4
SELECT  concat("first_name", ' ', "last_name") as fullname,
dateToString('%Y-%m-%d',"hire_date") as hiredate
FROM employees
WHERE "hire_date" >= date('2017-01-01')

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
db.employees.aggregate(
[{
"$match": {
"hire_date": {
"$gte": ISODate("2017-01-01T08:00:00.000+08:00")
}
}
},
{
"$project": {
"fullname": {
"$concat": [
"$first_name",
" ",
"$last_name"
]
},
"hiredate": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": "$hire_date"
}
}
}
}
])

alt text

Let us look at concat function concat("first_name", ' ', "last_name"). The concat function is a MongoDB string aggregation operators. Through mapping SQL functions to MongoDB operators, NoSQLBooster for MongoDB allows you to use all MongoDB aggregation operators as SQL functions in you SQL statement.

1
2
3
4
5
6
7
8
// instead of writing
{ $concat: [ "$first_name", " ", "last_name" ] }

// we write,
concat("first_name", ' ', "last_name") //Double quotes quote field name, Single quotes are for strings

// or
concat(first_name, ' ', last_name) //allow to omit double quotes
  1. No $ prefix with MongoDB operator and collection field name.
  2. Double quotes quote field name. Single quotes are for strings.
  3. All function names are case-sensitive except for COUNT, SUM, MAX, MIN, AVG.
  4. We can use the standard SQL comparison operators: =, !=, <>, <, <=, >=, or >.

The date function converts a string to a MongoDB Date type. NoSQLBooster for MongoDB uses Moment.js to parse date string. When creating a date from a string, Moment.js first check if the string matches the known ISO 8601 formats, Moment.js then check if the string matches the RFC 2822 Date time format before dropping to the fall back of new Date(string) if a known format is not found.

Please refer to Moment.js parse string document to view all supported string formats.

1
2
3
4
5
6
7
8
9
10
11
12
13
# An ISO 8601 string requires a date part.

2013-02-08 # A calendar date part

#A time part can also be included, separated from the date part by a space or an uppercase T.
2013-02-08 09:30 # An hour and minute time part
2013-02-08 09:30:26 # An hour, minute, and second time part

#If a time part is included, an offset from UTC can also be included as +-HH:mm, +-HHmm, +-HH or Z.

2017-01-01T08:00:00.000+08:00
2013-02-08 09+07:00 # +-HH:mm
2013-02-08 09:30:26.123+07:00 # +-HH:mm

The dateToString is another MongoDB date operator to convert a date object to a string according to a user-specified format. The $dateToString expression has the following syntax:

1
{ $dateToString: { format: <formatString>, date: <dateExpression> } }

As SQL functions doesn't support JSON object parameter, NoSQLBooster for MongoDB converts the object param as plain parameter list.

1
dateToString('%Y-%m-%d',"hire_date") as hiredate

The first parameter is formatString, single quotes, the second parameter is "Date Field", double quotes.

Please follow this link to learn more about MongoDB date aggregation operators.

Quoting Names and String Values

In NoSQLBooster, we follow ANSI SQL standard. Single quotes delimit a string constant or a date/time constant. Double quotes delimit identifiers e.g., collection names or column names. This is generally only necessary when your identifier doesn't fit the rules for simple identifiers.

The following SQL statement selects all the customers from the department "sales," in the "employees" collection:

1
SELECT * FROM employees WHERE department='sales';

The equivalent MongoDB query

1
2
3
db.employees.find({
"department": "sales"
})

Single quotes are for strings. If you double quotes "sales," NoSQLBooster for MongoDB treat it as a column "sales," not a string 'sales'.

1
SELECT * FROM employees WHERE department="sales";

The SQL generated the following MongoDB query that you may not want

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
db.employees.aggregate(
[{
"$addFields": {
"__tmp_cond_1": {
"$eq": [
"$department",
"$sales"
]
}
}
},
{
"$match": {
"__tmp_cond_1": true
}
},
{
"$project": {
"__tmp_cond_1": 0
}
}
])

Stick to using single quotes.

Explain Data for the Query

Just add a new line ".explain()" to the end of mb.runSQLQuery and click execute button again. It returns the information on the processing of the pipeline.

alt text

Querying Special BSON Data Types, UUID, BinData, DBRef...

To query values of these particular BSON Data types, write the values as you would in the MongoDB shell. All MongoDB build-in Data Types functions are available.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
--date
SELECT * FROM collection WHERE date_field >= date("2018-02-09T00:00:00+08:00")
SELECT * FROM collection WHERE date_field >= ISODate("2018-02-09")

--number
SELECT * FROM collection WHERE int64_field >= NumberLong("3223123123122132992")
SELECT * FROM collection WHERE decimal_field = NumberDecimal("8989922322323232.12")

--Regular Expression
SELECT * FROM collection WHERE string_field = RegExp('query','i')

--binary
SELECT * FROM collection WHERE objectId_field = ObjectId("56034dae9b835b3ee6a52cb7")
SELECT * FROM collection WHERE binary_field = BinData(0,"X96v3g==")
SELECT * FROM collection WHERE md5_field = MD5("f65485ac0686409aabfa006f0c771fbb")
SELECT * FROM collection WHERE hex_field = HexData(0,"00112233445566778899aabbccddeeff")

--uuid
SELECT * FROM collection WHERE uuid_field = UUID("4ae5bfce-1dba-4776-80eb-17678822b94e")
SELECT * FROM collection WHERE luuid_field = LUUID("8c425c91-6a72-c25c-1c9d-3cfe237e7c92")
SELECT * FROM collection WHERE luuid_field = JSUUID("8c425c91-6a72-c25c-1c9d-3cfe237e7c92")
SELECT * FROM collection WHERE luuid_field = CSUUID("6a72c25c-5c91-8c42-927c-7e23fe3c9d1c")
SELECT * FROM collection WHERE luuid_field = PYUUID("5cc2726a-915c-428c-927c-7e23fe3c9d1c")

--timstamp
SELECT * FROM collection WHERE timestamp_field = Timestamp(1443057070, 1)
--symbol
SELECT * FROM collection WHERE symbol_field = Symbol('I am a symbol')
--dbref
SELECT * FROM collection WHERE dbref_field = DBRef("unicorns", ObjectId("55f23233edad44cb25b0d51a"))
--minkey maxkey
SELECT * FROM collection WHERE minkey_field = MinKey and maxkey_field = MaxKey

--array, array_field is [1, 2, '3']
SELECT * FROM collection WHERE array_field = [1,2,'3']

--object, object_field is { a : 1, b : {b1 : 2, b2 : "b2"}
SELECT * FROM collection WHERE object_field = toJS(a=1, b=toJS(b1=2, b2='b2'))

Accessing Arrays and Embedded Documents

Nested documents (sub-documents) and arrays including filters and expressions are supported. You can access such fields using a dotted name.

Given the following documents in the survey collection:

1
2
3
4
5
db.survey.insert([   
{ _id: 1, results: [ { product: "abc", score: 10 }, { product: "xyz", score: 5 } ]},
{ _id: 2, results: [ { product: "abc", score: 8 }, { product: "xyz", score: 7 } ]},
{ _id: 3, results: [ { product: "abc", score: 7 }, { product: "xyz", score: 8 } ]}
])

The "product" and "score" would be referenced as results.product and results.score respectively:

1
SELECT * FROM survey  WHERE results.product = 'xyz' AND results.score >= 8;

or

1
SELECT * FROM survey  WHERE "results.product" = 'xyz' AND "results.score" >= 8;

Element Match with Embedded Documents

The elemMatch query criteria (score >=8) will be translated as "score": { "$gte": 8 }. This syntax is more concise and expressive.

1
2
--  Enter "elemMatch [Tab]", to trigger auto-complete
SELECT * FROM survey WHERE "results" =elemMatch(product='xyz', score >=8)

SQL Equi JOIN

NoSQLBooster supports SQL Equi JOIN which performs a JOIN against equality or matching column(s) values of the associated tables.
An equal sign (=) is used as comparison operator in the where clause to refer equality.

NoSQLBooster supports INNER JOIN and LEFT JOIN, OUTER JOIN is not supported.

  1. (INNER) JOIN: Returns records that have matching values in both tables
  2. LEFT JOIN: Return all records from the left table, and the matched records from the right table

Equi JOIN will be translated as MongoDB $lookup stage. (MongoDB 3.2+) refer to: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup

1
SELECT * FROM orders JOIN inventory ON orders.item=inventory.sku

The "toJS" SQL Function and named parameter

The “toJS” helper function transforms the named parameters and arithmetic operator into a JSON object, also transforms an ordinary parameter list into an array.

1
2
3
4
5
6
toJS(k='v'); //result {k:'v'}
toJS(k="v"); //result {k:'$v'}, Double quotes quote object names
toJS(k=v); //result {k:'$v'}, without quote, v is a object name
toJS(k>5, k<=10); //result { "k": { "$gt": 5, "$lte": 10} }
toJS(a=1, b=toJS(b1=2, b2='b2')); //result {a : 1, b : {b1 : 2, b2 : "b2"}
toJS(1, 2,'3'); // result [1,2,'3'];

With named parameter and "toJS" helper function, you can query complex objects or pass JSON-object parameter to a SQL function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--elemMatch, named parameter and Arithmetic operators
--cool stuff, (score>8, score<=10) will be translated as {"score": { "$gt": 8, "$lte": 10 }}
SELECT * FROM survey WHERE results =elemMatch(item='abc', score>8, score<=10)

--date timezone, named parameter
SELECT year(date="date", timezone='America/Chicago') as year FROM sales

--query object type value, object_field: { a : 1, b : {b1 : 2, b2 : "b2"}
SELECT * FROM collection WHERE object_field = toJS(a=1, b=toJS(b1=2, b2='b2'))

-- text search with full-text search options
SELECT * FROM article WHERE
$text = toJS($search='cake', $language='en', $caseSensitive=false, $diacriticSensitive=false)

SELECT literal(toJS(k>5, k<=10)) FROM collection

Mixed use of SQL and Chainable Aggregation Pipeline

NoSQLBooster for MongoDB translates SQL to MongoDB find/aggregate method which returns a AggregateCursor. All MongoDB cursor methods and NoSQLBooster for MongoDB's extension methods can be called. This also allows NoSQLBooster Intellisense to know all AggregateCursor's chainable stage methods. (sort, limit, match, project, unwind...)

1
2
3
4
5
6
7
8
mb.runSQLQuery(`

SELECT * FROM "survey" where type='ABC' and year(date) = 2018 ORDER BY "results.score" DESC

`)
.unwind('$tags')
.project("-_id") //alias select
.limit(1000)

The equivalent MongoDB Query is a bit longer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
db.survey.aggregate(
[
{
"$addFields" : {
"year(date)" : {
"$year" : "$date"
}
}
},
{
"$match" : {
"type" : "ABC",
"year(date)" : 2018
}
},
{
"$project" : {
"year(date)" : 0
}
},
{
"$sort" : {
"results.score" : -1
}
},
{
"$unwind" : "$tags"
},
{
"$project" : {
"_id" : 0
}
},
{
"$limit" : 1000
}
])

Save SQL Query as MongoDB Read-only View

You can use the extension method "saveAsView" to save SQL Query result as a MongoDB read-only view.

1
2
3
4
5
6
7
8
//double quotes quote object names (e.g. "collection"). Single quotes are for strings 'string'.
mb.runSQLQuery("
SELECT concat(first_name, ' ', last_name) as fullname,
dateToString('%Y-%m-%d',hire_date) as hiredate
FROM employees
WHERE hire_date >= date('2017-01-01')

").saveAsView("Employers_hired_after_2017", {dropIfExists:true}) //drop view if it exists.

You can also use the forEach method to apply a javascript method to each document.

1
2
3
4
mb.runSQLQuery("SELECT * FROM employees WHERE hire_date >= date('2017-01-01')")
.forEach(it=>{
//sendToMail(it)
});

SQL Snippets

NoSQLBooster includes a lot of SQL-specific code snippets to save you time, Date Range, Text Search, Query and Array, Existence Check, Type Check and so on. You can always manually trigger it with "Ctrl-Shift-Space"}}}. Out of the box, "Ctrl-Space"}}}, "Alt-Space"}}} are acceptable triggers.

SQL Snippet

SQL Date Range Snippets

1
2
3
--  Enter "daterange [Tab]," then..., today, yesterday, lastNDays
SELECT * FROM collection WHERE
"|" >= date("2018-02-09T00:00:00+08:00") and "|" < date("2018-02-10T00:00:00+08:00")

Text Search Snippets

1
2
3
4
5
6
7
8

-- Enter "text [Tab]", then...
SELECT * FROM collection WHERE $text = toJS($search='|')

-- Enter "textopt [Tab]", then...
-- with full-text search options
SELECT * FROM collection WHERE
$text = toJS($search='|', $language='en', $caseSensitive=false, $diacriticSensitive=false)

The equivalent MongoDB Text Search

1
2
3
4
5
6
7
8
db.collection.find({
"$text": {
"$search": "|",
"$language": "en",
"$caseSensitive": false,
"$diacriticSensitive": false
}
})

Query an Array ($all and $elemMatch) Snippets

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

  • Element Match with Embedded Documents

The elemMatch query criteria (quantity>2, quantity<=10) will be translated as *** "quantity": { "$gt": 2, "$lte": 10 }***. This syntax is more concise and expressive.

1
2
3
--  Enter "elemem [Tab]", then...
--
SELECT * FROM survey WHERE "|" =elemMatch(product='abc', quantity>2, quantity<=10)

The equivalent MongoDB Query

1
2
3
4
5
6
7
8
9
10
11
db.survey.find({
"|": {
"$elemMatch": {
"product": "abc",
"quantity": {
"$gt": 2,
"$lte": 10
}
}
}
})
  • Element Match
1
2
--  Enter "elem [Tab]", then...
SELECT * FROM survey WHERE "score" =elemMatch($gte=80, $lte=85)

The equivalent MongoDB Query

1
2
3
4
5
6
7
8
db.survey.find({
"score": {
"$elemMatch": {
"$gte": 80,
"$lte": 85
}
}
})

The $all array operator selects the documents where the value of a field is an array that contains all the specified elements.

1
2
--  Enter "all [Tab]", then...
SELECT * FROM survey WHERE "|" = toJS($all=['', ''])

Existence Check and Type Check Snippets

  • Existence Check ($exists)
1
2
3
4
5
6
7
8
9
10
11
--  Enter "exists [Tab]", then...
SELECT * FROM collection WHERE "|" =$exists(TRUE)

-- Enter "nonExist [Tab]", then...
SELECT * FROM collection WHERE "|" = $exists(FALSE)

-- Enter "existAndIsNull [Tab]", then...
SELECT * FROM collection WHERE "|" = $exists(TRUE) and "|" IS NULL

-- Enter "existAndIsNotNull [Tab]", then...
SELECT * FROM collection WHERE "|" = $exists(TRUE) and "|" IS NOT NULL
  • Querying by Multiple Data Type ($type)
1
2
--  Enter "typeSearch [Tab]", then...
SELECT * FROM collection WHERE "|" = toJS($type=['double','int','string','bool','date','object','array','null'])

Complete List of SQL Support

To check out the complete list of SQL Support, visit this link.

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.

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.

Read More

Using node modules in MongoBooster

Question:

Can I use node modules in MongoBooster?  I want to use axios in my script.  And, Do I have to install node module globally?

Answer:

Yes,  You can use pure JS node modules in MongoBooster.  And, you don't need to install node modules globally.  

  1. Launch MongoBooster.

  2. Execute   Main Menu -> Help -> Open User Data Directory

  3. New Terminal at this folder

1
npm i axios  # run it in MongoBooster user data directory

After successfully installing this package in the MongoBooster User Data Directory, you can require and access it in the MongoBooster script.

1
2
3
const axios=require("axios");
let rst=await (axios.get('https://api.github.com/users/github'));//await promise The "await" is a build-in method in MongoBooster. refer to: https://www.mongobooster.com/blog/using-functions-with-async-callback
console.log(rst.data);

Read More