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.

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.

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

• 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.

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.

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.

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"
          }
        }
      }
    }
  ])

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.

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 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.

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.

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

• 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

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 the Executable Directory (or "Open User Data Directory" in the old version)
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

Question:

We're having a situation where we want to call a function with an async callback. The issue that arises is that the script thread terminates before the callback is executed. Is there any way to prevent the script thread from terminating before we get the callback?

Background:

We're intending to read a collection containing phone numbers, call Twilio Api (an sms service) to send a message, and finally store the status from Twilio into another collection.

1
2
3
4
5

db.users.find({...}).forEach((user) => {
  sendSms(user.phone, "A short message", (status) => {
    db.smsDeliveries.insert(status);
  });
});

Answer:

MongoBooster has a build-in function await (It's 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.

Please try the following code:

1
2
3
4
5
6
7
8
9
10
11
12

function sendSmsAsync(phone, message){ //promisify sendSms
    return new Promise((resolve, reject)=> {
            sendSms(phone, message, (status)=>{
                resolve(status)
            })
    });
}

db.users.find({}).forEach((user) => {
   let status=await(sendSmsAsync(user.phone, "A short message")); //await a promise
   db.smsDeliveries.insert({status});
});

Read More

So happy to release MongoBooster 3.5 today. This version distributes Linux application in the AppImage format, add Azure DocumentDB support along with some useful minor improvements and bugfix.

Hotfix in MongoBooster 3.5.7

• improved: SSH connection config UI
• fixed: a "use db" bug in copying collection
• fixed: missing "," issue when exporting collection to MySQL as SQL format
• fixed: throw subCollection.getCollection is not a function error while executing script "db.getCollection("xxxx.group.xxxx").find({})"
• fixed: A Javascript JSON.parse error occurred in the main process while reading "window-state-main.json"
• fixed: connection close issue when calling discoverReplicaSet

Hotfix in MongoBooster 3.5.6

• added: Short cut "Mod+0" - "toggle Output Panel" to maximize the script editor.
• added: Short cut "Mod+9" - "toggle Editor Panel" to maximize the output panel.
• improved: Update node version to 7.4.0
• fixed: Create new shell tab button is not visible in dark theme
• fixed: Missing icons in dark theme
• fixed: Can see HTML source in Replica Set Members header

Hotfix in MongoBooster 3.5.5

• fixed: should not sort by property name on a getIndex
• fixed: "Query by example" doesn't work with nested array
• fixed: can't add double/integer value into the nested array.
• fixed: import JSON file which only contain single JSON object doesn't work.

Hotfix in MongoBooster 3.5.4

• fixed: shell tab can't be closed when the tab name includes space char.
• fixed: mongotools 3.4.2 compatibility issue

Hotfix in MongoBooster 3.5.3

• fixed: Kerberos Library is not installed issue.
• fixed: database with name "<DATABASE>" maked problems
• improved: add bluebird Promise typings

Hotfix in MongoBooster 3.5.2

• fixed: Error connecting to Mongodb Atlas replicaset.
• improved:Upgrade MongoDB driver to the latest version 2.2.24

Linux AppImage format

This version packages desktop applications as AppImages that run on common Linux-based operating systems, such as RHEL, CentOS, Ubuntu, Fedora, debian and derivatives. An AppImage is a downloadable file for Linux that contains an application and everything the application needs to run (e.g., libraries, icons, fonts, translations, etc.) that cannot be reasonably expected to be part of each target system.

To run an AppImage, simply:

Make it executable

1

$ chmod a+x mongobooster*.AppImage

and run!

Read More

So happy to release MongoBooster 3.3 today. This version includes a few useful shell script extensions (cursor.getShellScript, cursor.saveAsView...) along with some useful minor improvements and bugfix.

shell script extensions

cursor.getShellScript()

Cursor.getShellScript() method return the MongoDB find shell script produced by the MongoBooster Fluent Query Builder.

For example:

Run the following Fluent query script in MongoBooster:

1
2
3
4
5
6

db.user.where('age').gte(18).lte(65)
       .select('name age -_id')
       .sort("-age name")
       .limit(5)
       .skip(100)
       .getShellScript()

We got the following MongoDB shell find script

1
2
3
4
5
6
7
8
9
10
11
12
13

db.user.find({
            "age" : {
                "$gte" : 18,
                "$lte" : 65
            }
        }, {
            "name" : 1,
            "age" : 1,
            "_id" : 0
        })
       .sort({  "age" : -1,  "name" : 1 })
       .limit(5)
       .skip(100)

Read More

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

Hotfix in MongoBooster 3.2.3

• Improved: add "Current Query Result" export option for non-cursor collections (plain collections)
• Changed: default "Tab Name Format" to "Database:Collection"
• Changed: loosen the restriction - Test Data Generator restricts to test database.
• Changed: loosen the restriction -Read-only status lock restricts to localhost:27017 connection

Hotfix in MongoBooster 3.2.2

• Fixed: missing edit icon in the dark theme
• Fixed: windows freezes or stops responding when SSH connection lost (Windows platform only)

MongoDB 3.4 support

MongoBooster 3.2 adds support for all the new shell methods and operations of MongoDB 3.4. Sharding Zones, Linearizable Read Concern, Decimal Type, New Aggregation Stages, Views, and Collation.

New Aggregation Stages

MongoDB 3.4 adds a few aggregation stages for Recursive Search, Faceted Search, Facilitate Reshaping Documents, Count and Monitor. In MongoBooster 3.2, all new aggregation stages can be used as the chainable methods, rather than specifying a JSON object.

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

db.employees.aggregate()
      .graphLookup({
         from: "employees",
         startWith: "$reportsTo",
         connectFromField: "reportsTo",
         connectToField: "name",
         as: "reportingHierarchy"
      })
db.artwork.aggregate()
     .bucketAuto({
         groupBy: "$price",
         buckets: 4
     })
db.scores.aggregate()
    .addFields({
       totalHomework: { $sum: "$homework" } ,
       totalQuiz: { $sum: "$quiz" }
     })
    .addFields({ totalScore:
       { $add: [ "$totalHomework", "$totalQuiz", "$extraCredit" ] } 
   })
db.exhibits.aggregate().unwind("$tags").sortByCount("$tags")
db.people.aggregate()
   .match(qb.where("pets").exists(true))
   .replaceRoot("$pets")
db.scores.aggregate()
      .match(qb.where("score").gt(80))
      .$count("passing_scores")////why not count? MongoBooster already has a method called count which return number

Read More

This version includes query by example, ReplicaSet discover members, export query and selected documents can choose fields and export to sql, improved connection config other options editor along with some useful minor improvements and bugfix.

Hotfix in MongoBooster 3.1.5

• Changed: Bind shortcut F5 to “Run/Execute Current Statement”, replacing F4.
• New: CTRL+F5 to execute the current statement into a new tab sheet.
• New: CTRL+F6 to execute the entire/selected script into a new tab sheet.
• Improved: Sort exported fields by name in the export dialog
• Improved: Auto-fetch all fields for the small-size collection (< 2M)
• Fixed: An autocomplete malfunction bug for the local connection after trial expired

Hotfix in MongoBooster 3.1.3

• Changed, In order to keep the behavior consistent with mongodb shell, MongoBooster will insert/update number as double type.

1
2
3

db.test.insert({a:1, b:1.0, c: 1.1, d: NumberInt("1.0"), e:Double("1.0")}) //or update
//before(<3.1.3):  a: int32,   b: int32,   c:double, d: int32,  e:double
//now   (>=3.1.3): a: double,  b: double,  c:double, d: int32,  e:double

• Improved, execute current statement(F4) improved even focus on the commented line or empty line;
• fixed, local timezone didn't show in json view;
• fixed, collection export cannot change filename;
• fixed, collection cannot export to mongoShell format.
• fixed, closing tab with mouse middle button closes the active one instead of the aimed one

Hotfix in MongoBooster 3.1.2

• Added, Ctrl(Cmd)+D added as a new “duplicate selection” shortcut in editor
• Improved, table view column autosize for Date/Number type performance improved;
• Improved, run current line(F4) now can run a complete script statement even it split into multiple lines;
• Improved, custom snippets(favorite) now can be used in the middle of a script;
• Improved, Ctrl(Cmd)+Enter can be used to save in Type/Value Editor;
• Fixed, value contains scripts execute when using find.

what's new in MongoBooster 3.1

Query by Example

It's more easy to get a query via this small but very useful feature. Multiple fields supported, nested object/array supported.

Read More

We're so proud to release Mongobooster 3.0 today. In this brand new version, it gets more and more useful with our full-featured shell and several new tools.

New free edition added with Mongobooster 3.0, this free edition is free for personal/commercial use but limited function.

Schema Analyzer

Schema Analyzer is a very useful build-in tool. Due to schema-less feature, collections in MongoDB do not have a schema document to describe field's datatype, collection structure and validations. With our brand new Schema Analyzer Tool, you will get a document to describe the schema of certain collection from sampled(random, first, last) N or all records.

The document shows the probability of sampled objects , different types percentage, you could get a brief of certain collection's schema. If you want more accurate result, you could sample more records or analyze whole collection, but it may took a long time to finish if the collection has millions records or thousands fields.

It also shows document validation of the collection, which is a new feature in MongoDB 3.2. There will be a validator window showing below the document. If you click the link, field will be highlighted in the window.

You could export this document to most popular document file types, like MS Word, PDF, HTML, along with JSON, TXT and CSV. Mongoose.js schema file supported as well. Sample file

Read More

Today, we're thrilled to announce the release of MongoBooster 2.3. This release includes significantly improved console.log/print tab, JSON view format options, bug fixes and a few minor improvements.

Improved console.log/print tab

The brand-new, improved console.log/print Tab collects all output messages in only one tab, instead of showing them in separated tabs. You can find and view these messages much easier than before. The elapsed time shows when the message occurs during the whole script execution.

Read More