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

Mixed use of SQL and Chainable Aggregation Pipeline

This feature already existed in previous versions, but in 4.5, mb.runSQLQuery returns AggregateCursor instead of DBCursor. This 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
	}
])

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

NoSQLBooster 4.5 supports all MongoDB build-in Data Types functions. To query values of these special BSON Data types, just write the values as you would in the MongoDB shell. 

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'))

Date Functions with Optional Timezone

NoSQLBooster 4.5 adds the timezone support for all SQL Date functions(Aggregation Operators), this feature requires MongoDB 3.6 or later.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT  date,

        year("date", 'America/Chicago') as year, --Olson Timezone Identifier
        month(date="date", timezone='America/Chicago') as month, -- named parameters
        dayOfMonth("date", '-04:35') as day, --UTC Offset 
        hour("date",'+0800') as hour,
        minute("date",'+03') as minutes,
        second(date, 'GMT') as seconds, 
        millisecond('$date','+08') as millisecond,  -- single quote string, use $ as prefix
        dayOfYear("date",'Europe/London') as dayOfYear,
        dayOfWeek(date) as dayOfWeek,
        week("date") as week,

        dateToString(format='%H:%M:%S:%L%z', date=date, timezone='America/New_York') as timewithOffsetNY, 
        dateToString(format='%Z', date="date", timezone='+04:30') as minutesOffset430,

        dateToParts(date="date", iso8601=true) as date_iso,
        dateToParts(date="date", timezone='America/New_York') as date_timezone,

        dateFromParts(year=2016, month=12, day=31, hour=23, minute=46, second=12, timezone='America/New_York') as date_timezone
FROM sales

Improved GROUP BY Clause

The improved GROUP By clause supports MongoDB Pipeline Operators and SQL Aliases.

1
2
3
4
mb.runSQLQuery(`
   SELECT count(*), year FROM employees
          GROUP BY year(hire_date,'+08:00')  as year  -- year function is MongoDB $year operator      
`)

The following is equivalent MongoDB Aggregation Pipelines. Compare these two statements, it is clear that the SQL SELECT is simpler and easier to write and read than the MongoDB JSON-LIKE aggregation pipelines.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
db.employees.aggregate(
  [{
      "$group": {
        "_id": {
          "year": {
            "$year": {
              "date": "$hire_date",
              "timezone": "+08:00"
            }
          }
        },
        "COUNT(*)": {
          "$sum": NumberInt("1")
        }
      }
    },
    {
      "$project": {
        "COUNT(*)": "$COUNT(*)",
        "year": "$_id.year"
      }
    }
  ])

Better Error Handling in SQL Query

All exceptions thrown when running a SQL Query have associated location information.

SQL Error Report

SQL Editor Mouse Hover

The mouse hover will show many useful information, such as document and example.

SQL Token Tooltip

Press F1 will lead you to MongoDB online document as the cursor is over a valid MongoDB function or aggregation operator.

Lots of New SQL Snippets

NoSQLBooster 4.5 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'])

Other Notable Improvements

Improved tooltip of Index Node

Statistics on the index use will be displayed in the tooltip of index node.

Index Tooltip

Auto-Detect MongoDB URI

Starting with version 4.5, NoSQLBooster can detect whether you have a MongoDB URI in your clipboard and auto-populate the connection dialog from the URI.

Detect MongoDB URI

Show the Recent Access Time of the Connection

Recent Access Time

Minor Improvements and Bugfix

  • Improved, Humanize Array values in the table view, show as "(3) [1, 2.0 ,'3']"
  • Changed, the default sample size of schema analysis increases from 100 to 1000
  • Changed, default database name of test data generator dialog from "test" to the current db
  • Added, "View Index Statistics ($indexStats)" menu item to indexes sub items of the collection node
  • Upgraded from Chrome 58.0.3029.110 to 59.0.3071.115
  • Upgraded from Node 7.9.0 to 8.2.1
  • Enabled SSL connection in free edition

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.