NoSQLBooster

https://nosqlbooster.com/
Admin
NoSQLBooster is a cross-platform GUI IDE for MongoDB that provides AI-based natural language...
Tokens:49,197
Snippets:631
Trust Score:8.5
Update:5 months ago
Show doc for...
Tokens:
Raw
### Install FUSE for Ubuntu < 22.04

Source: https://nosqlbooster.com/downloads

Use this command to install FUSE and libfuse2 on Ubuntu versions prior to 22.04, which are required for running AppImages.

```bash
sudo apt-get install fuse libfuse2

```

--------------------------------

### Install FUSE for Ubuntu >= 22.04

Source: https://nosqlbooster.com/downloads

Use this command to install libfuse2 on Ubuntu version 22.04 and later, which is necessary for running AppImages.

```bash
sudo apt install libfuse2

```

--------------------------------

### Aggregation Pipeline Example

Source: https://nosqlbooster.com/FluentQueryAPI

Illustrates how to build an aggregation pipeline using the fluent API and retrieve the translated MongoDB shell script.

```APIDOC
## Aggregation Pipeline with Operator Helper

### Description
This example demonstrates chaining aggregation pipeline stages using the fluent API and the operator helper (`$`), and then obtaining the equivalent MongoDB shell script using `getShellScript()`.

### Method
`db.collection.aggregate()` with fluent methods and `cursor.getShellScript()`

### Endpoint
N/A (SDK/Library Method)

### Parameters
#### Aggregation Stages
- **match** (Object) - Filters documents for the pipeline.
- **group** (Object) - Groups documents by a specified identifier.
- **sort** (String) - Sorts the results.
- **limit** (Number) - Limits the number of documents returned.

#### Operator Helper (`$`)
- **where** (String) - Used to specify conditions within operators like `$match`.
- **gte** (Number) - Greater than or equal to operator.
- **lte** (Number) - Less than or equal to operator.

### Request Example
```javascript
// Aggregation pipeline using fluent API and operator helper
db.companies.aggregate()
  .match($.where('founded_year').gte(2000).lte(2010)) // $: operator helper
  .group({_id:"$category_code",count:{$sum:1}})
  .sort('-count')
  .limit(100)
  .getShellScript() // Get translated mongo shell script
```

### Response
#### Success Response (String)
Returns a string containing the translated MongoDB shell script for the aggregation pipeline.

#### Response Example
```javascript
// Example of the output from getShellScript()
db.companies.aggregate([
  { $match: { founded_year: { $gte: 2000, $lte: 2010 } } },
  { $group: { _id: "$category_code", count: { $sum: 1 } } },
  { $sort: { count: -1 } },
  { $limit: 100 }
])
```
```

--------------------------------

### Install NPM Package (axios)

Source: https://nosqlbooster.com/useNodeModule

Install an NPM package like 'axios' within the NoSQLBooster user data directory using npm. This makes the package available for use in your scripts.

```bash
npm i axios  # run it in NoSQLBooster for MongoDB user data directory
```

--------------------------------

### Basic Usage Example

Source: https://nosqlbooster.com/FluentQueryAPI

Demonstrates how to use the fluent query builder to replace traditional JSON query objects for find operations.

```APIDOC
## Basic Usage

### Description
This example shows how to use the fluent query builder API for MongoDB find operations, offering a more readable and chainable syntax compared to traditional JSON query objects.

### Method
`db.collection.find()` with fluent methods

### Endpoint
N/A (SDK/Library Method)

### Parameters
#### Query Parameters
- **where** (Object or String) - Used to specify query conditions.
- **select** (String) - Specifies the fields to include or exclude.
- **sort** (String) - Specifies the sort order.

### Request Example
```javascript
// Instead of writing:
db.user.find({age:{$gte:18,$lte:65}},{name:1,age:1,_id:0}).sort({age:-1, name:1});

// We can write:
db.user.where('age').gte(18).lte(65).select('name age -_id').sort("-age name");

// Passing query conditions is permitted too
db.collection.find().where({ name: 'nosqlbooster' })

// Chaining multiple conditions
db.collection
    .where('age').gte(18).lte(65)
    .where({ 'name': /^nosqlbooster/i })
    .where('friends').slice(10)
```

### Response
#### Success Response (Cursor)
Returns a MongoDB cursor object that can be further manipulated or iterated over.

#### Response Example
N/A (Output is a cursor, not a direct JSON response)
```

--------------------------------

### SQL Query for MongoDB Example

Source: https://nosqlbooster.com/home

This demonstrates how to query MongoDB using SQL syntax. The tool translates this into a MongoDB query.

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

--------------------------------

### Install and Use NPM Packages in NoSQLBooster

Source: https://nosqlbooster.com/home

Install npm packages like 'axios' in the NoSQLBooster user data directory and use them in your scripts with 'require' and 'await'.

```bash
npm i axios  # run it in NoSQLBooster for MongoDB user data directory

```

```javascript
const axios=require("axios");
let rst=await (axios.get('https://api.github.com/users/github'));//await promise
console.log(rst.data);
```

--------------------------------

### hint()

Source: https://nosqlbooster.com/FluentQueryAPI

Sets query hints to guide the query planner. Cannot be used with `distinct()`.

```APIDOC
## hint()

### Description
Sets query hints.

### Method
db.collection.hint(spec: object)

### Example
```javascript
db.collection.hint({ indexA: 1, indexB: -1 })
```

_Cannot be used with `distinct()`._
```

--------------------------------

### Use Installed NPM Package (axios)

Source: https://nosqlbooster.com/useNodeModule

Require and use an installed NPM package such as 'axios' in your NoSQLBooster script. The built-in 'await' function can be used to handle promises returned by the package.

```javascript
const axios=require("axios");
let rst=await (axios.get('https://api.github.com/users/github'));//await promise
console.log(rst.data);
```

--------------------------------

### Install and Use NPM Packages in NoSQLBooster

Source: https://nosqlbooster.com/

Install npm packages like 'axios' in the NoSQLBooster user data directory and use them in your MongoDB scripts with 'require' and 'await'.

```bash
npm i axios  # run it in NoSQLBooster for MongoDB user data directory
```

```javascript
const axios=require("axios");
let rst=await (axios.get('https://api.github.com/users/github'));//await promise
console.log(rst.data);
```

--------------------------------

### SQL Equi JOIN Example

Source: https://nosqlbooster.com/runSQLQuery

Demonstrates an INNER JOIN operation using SQL syntax. This is translated to a MongoDB $lookup stage.

```SQL
SELECT * FROM orders JOIN inventory ON orders.item=inventory.sku
```

--------------------------------

### MongoDB Aggregate Query Example

Source: https://nosqlbooster.com/home

This is an example of a MongoDB aggregate query using the aggregation pipeline structure.

```javascript
db.employees.aggregate([{
        $group: { _id:   "$department", total: { $sum: "$salary" }}
    }])
```

--------------------------------

### Using Node.js Modules in Scripts

Source: https://nosqlbooster.com/shellExtensions

Demonstrates importing and using Node.js built-in modules like 'fs' and 'path', as well as external NPM packages such as 'axios'. Ensure external packages are installed in the NoSQLBooster user data directory. Supports asynchronous operations with 'await'.

```javascript
import {existsSync} from "fs"; //ES6 module standard
existsSync("myFile");

const path=require("path");
path.resolve('/foo/bar', './baz');// Returns: '/foo/bar/baz'

const axios=require("axios"); //npm i axios  //run it in NoSQLBooster for MongoDB user data directory
let rst=await (axios.get('https://api.github.com/users/github'));//await promise
console.log(rst.data);
```

--------------------------------

### SQL ALL Operator Example

Source: https://nosqlbooster.com/featuresForSQL

Demonstrates the use of the ALL operator in a WHERE clause to check if all values returned by the subquery meet the condition. Note: This specific SQL syntax is not supported.

```sql
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
```

--------------------------------

### SQL dateToString Function Example

Source: https://nosqlbooster.com/runSQLQuery

An example of using the dateToString function in SQL within NoSQLBooster, which translates to the MongoDB $dateToString operator. The format string is in single quotes and the date field in double quotes.

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

```

--------------------------------

### SQL Equi JOIN Example

Source: https://nosqlbooster.com/featuresForSQL

This SQL query demonstrates an equi-join between the `orders` and `inventory` tables on matching `item` and `sku` columns. This is translated into a MongoDB `$lookup` aggregation stage.

```sql
SELECT * FROM orders
   JOIN inventory ON orders.item=inventory.sku

```

--------------------------------

### toJS SQL Function Examples

Source: https://nosqlbooster.com/runSQLQuery

Illustrates the usage of the 'toJS' helper function to transform parameters into JSON objects or arrays. It shows how to handle quoted and unquoted object names, comparison operators, and nested objects.

```SQL
toJS(k='v'); //result {k:'v'}
```

```SQL
toJS(k="v"); //result {k:'$v'}, Double quotes quote object names
```

```SQL
toJS(k=v); //result {k:'$v'}, without quote, v is a object name
```

```SQL
toJS(k>5, k<=10); //result  { "k": { "$gt": 5, "$lte": 10} }
```

```SQL
toJS(a=1, b=toJS(b1=2, b2='b2')); //result {a : 1, b : {b1 : 2,    b2 : "b2"}
```

```SQL
toJS(1, 2,'3'); // result  [1,2,'3'];
```

--------------------------------

### SQL ANY Operator Example

Source: https://nosqlbooster.com/featuresForSQL

Illustrates the use of the ANY operator in a WHERE clause to check if any value returned by the subquery meets the condition. Note: This specific SQL syntax is not supported.

```sql
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
```

--------------------------------

### Run SQL Query with nbcli

Source: https://nosqlbooster.com/nbcli

Execute SQL queries by providing the query statement as the second argument to the nbcli command. Ensure the query starts with SELECT.

```bash
nbcli localhost/test "SELECT gender, COUNT(*) AS count FROM unicorns GROUP BY gender"
```

--------------------------------

### Querying Special BSON Data Types in SQL

Source: https://nosqlbooster.com/runSQLQuery

Provides examples of SQL queries for various BSON data types like Date, NumberLong, NumberDecimal, RegExp, ObjectId, BinData, UUID, Timestamp, Symbol, MinKey, and MaxKey.

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

--------------------------------

### ISO 8601 Date Formats

Source: https://nosqlbooster.com/runSQLQuery

Examples of various ISO 8601 compliant date and time formats supported by Moment.js for parsing date strings in NoSQLBooster.

```plaintext
# 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

```

--------------------------------

### SQL AND Operator Example

Source: https://nosqlbooster.com/featuresForSQL

The SQL AND operator requires all conditions to be true. This is directly mapped to MongoDB's implicit AND logic for fields in the query object.

```SQL
SELECT * FROM customers
WHERE country='China' AND city='ShangHai';
```

```MongoDB
db.customers.find({
  "country": "China",
  "city": "ShangHai"
})
```

--------------------------------

### SQL Column Aliases Example

Source: https://nosqlbooster.com/featuresForSQL

SQL ALIASES provide temporary names for columns to improve readability of result sets. Table aliases are not currently supported as NoSQLBooster operates on a single collection.

```SQL
SELECT dept_id, COUNT(*) AS total
FROM employees
GROUP BY dept_id;
```

--------------------------------

### Equivalent MongoDB Aggregation for JOIN

Source: https://nosqlbooster.com/featuresForSQL

The MongoDB aggregation pipeline equivalent to the SQL equi-join example. It uses `$lookup` to join `orders` with `inventory`, followed by stages to merge and project the results.

```javascript
db.getCollection("orders").aggregate(
 [
    { "$lookup" : { "from" : "inventory", "localField" : "item", "foreignField" : "sku", "as" : "inventory_docs" } },
    { "$match" : { "inventory_docs" : { "$ne" : [ ] } } },
    { "$addFields" : { "inventory_docs" : { "$arrayElemAt" : [ "$inventory_docs", 0 ] } } },
    { "$replaceRoot" : { "newRoot" : { "$mergeObjects" : [ "$inventory_docs", "$$ROOT" ] } } },
    { "$project" : { "inventory_docs" : 0 } }
])

```

--------------------------------

### Aggregation Pipeline - Basic Usage

Source: https://nosqlbooster.com/FluentQueryAPI

Demonstrates basic usage of the aggregation pipeline, including matching all documents or specific criteria.

```APIDOC
## Aggregation Pipeline

### Description
All MongoDB Aggregate Stage Operators have a corresponding chainable method. You can use it as currently documented or via the chainable methods.

Calling aggregate without an array of operations or $operations will make it a match.

### Examples
```javascript
// Matches every document
db.collection.aggregate()
db.collection.aggregate({})

// Matches documents where the "a" is equal to 1
db.collection.aggregate({a: 1})

// Matches documents where "a" is greater than 7
db.collection.aggregate({a: {$gt: 7}})
```
```

--------------------------------

### Accessing Mathjs Version

Source: https://nosqlbooster.com/shellExtensions

Demonstrates how to access the version property of the mathjs library.

```javascript
print("math.version",math["version"])
```

--------------------------------

### Aggregation Pipeline Chaining

Source: https://nosqlbooster.com/FluentQueryAPI

Demonstrates chaining multiple aggregation stages like match, project, group, and sort.

```javascript
// Match and project
db.collection.aggregate(<querydoc>).project(<projection>)
db.collection.aggregate({a: 1}).project({a: 1, _id: 0})
```

```javascript
// Match, group and sort
db.collection.aggregate({<match>}).group({<group>}).sort({<sort>})
db.test.aggregate().group({_id: '$a', 'sum': {'$sum': 1}}).sort({sum: -1})
```

```javascript
db.companies.aggregate()
  .match($.where('founded_year').gte(2000).lte(2010)) //qb:querybuilder
  .group({_id:"$category_code",count:{$sum:1}})
  .sort('-count')
  .limit(100)
```

--------------------------------

### SQL Combining AND and OR Example

Source: https://nosqlbooster.com/featuresForSQL

Complex conditions combining AND and OR in SQL can be translated to MongoDB using nested $and and $or operators.

```SQL
SELECT * FROM customers
WHERE country='China' AND (city='ShangHai' OR city='Wuxi');
```

```MongoDB
db.customers.find({
  "$and": [{
      "country": "China"
    },
    {
      "$or": [{
          "city": "ShangHai"
        },
        {
          "city": "Wuxi"
        }
      ]
    }
  ]
})
```

--------------------------------

### nbcli Usage Help

Source: https://nosqlbooster.com/nbcli

Displays the available commands and options for the nbcli tool. Use this to understand the different ways to interact with the command line interface.

```bash
Usages:

>> nbcli -h

bcli [db_address] [script] [options]

Positionals:
  db_address  MongoDB URI or NoSQLBooster connection name/id
              can be:
              my_connection                           my_connection is NoSQLBooster connection name
              my_connection/foo                       foo database on my_connection
              192.168.0.1/foo                         foo database on 192.168.0.1 machine
              192.168.0.1:9999/foo                    foo database on 192.168.0.1 machine on port 9999
              mongodb://192.168.0.1:9999/foo          URI, foo database on 192.168.0.1 port 9999  [string]

  script      script to run
              can be:
              db.users.find()                         javascript statement
              select * from users                     sql select statement
              ./test.js                               javascript file to run (ending in .js)  [string]

Options:
  --version       Show version number  [boolean]
  --task          to run import/export/restore/backup/query task
                  can be task id or task name  [string]
  --verbose       verbose mode  [boolean]
  --nodb          do not connect to mongodb  - no 'db_address' arg expected  [boolean]
  -u, --username  username for authentication  [string]
  -p, --password  password for authentication  [string]
  -h, --help      Show help  [boolean]

Examples:
  nbcli localhost:27017 "select name, address.email as email from users"   run SQL query
  nbcli test_connection "db.users.select('address.city, name').limit(10)"  run javascript statement
  nbcli "mongodb://localhost/test" ~/test.js                               run test.js javascript file
  nbcli --task 'my_mongo_backup_task'                                      run mongobackup task

copyright 2020 https://www.nosqlbooster.com

Here is an example to list all the collections in the current db:
bcli "localhost/mb-user-mgr" "select * from mborders"

```

--------------------------------

### Get Current Node.js Version

Source: https://nosqlbooster.com/useNodeModule

Check the Node.js version running within NoSQLBooster. This can be useful for compatibility checks.

```javascript
console.log(process.versions.node);
```

--------------------------------

### Make AppImage Executable

Source: https://nosqlbooster.com/downloads

This command grants execute permissions to the NoSQLBooster AppImage file, allowing it to be run.

```bash
chmod a+x nosqlbooster4mongo*.AppImage

```

--------------------------------

### Execute AppImage

Source: https://nosqlbooster.com/downloads

Run the NoSQLBooster AppImage after making it executable. The asterisk wildcard ensures it works with different version numbers.

```bash
./nosqlbooster4mongo*.AppImage

```

--------------------------------

### Prepare Demo Data for Employees Collection

Source: https://nosqlbooster.com/runSQLQuery

Insert sample employee data into the MongoDB 'employees' collection using the db.employees.insert() command. This data includes fields like number, name, salary, department, and hire_date.

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

```

--------------------------------

### SQL OR Operator Example

Source: https://nosqlbooster.com/featuresForSQL

The SQL OR operator allows records if any condition is true. In MongoDB, this is achieved using the $or operator with an array of conditions.

```SQL
SELECT * FROM Customers
WHERE city='ShangHai' OR city='Wuxi';
```

```MongoDB
db.Customers.find({
  "$or": [{
      "city": "ShangHai"
    },
    {
      "city": "Wuxi"
    }
  ]
})
```

--------------------------------

### SQL to Aggregation Pipeline with Chainable Methods

Source: https://nosqlbooster.com/runSQLQuery

Demonstrates how NoSQLBooster translates SQL queries into MongoDB aggregation pipelines. It shows chaining of SQL query with aggregation methods like unwind, project, and limit.

```JavaScript
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)
```

--------------------------------

### Basic SQL Query and MongoDB Equivalent

Source: https://nosqlbooster.com/runSQLQuery

Demonstrates a basic SQL SELECT statement and its equivalent MongoDB find query for selecting data from a collection.

```sql
SELECT * FROM employees WHERE department='sales';
```

```javascript
db.employees.find({
  "department": "sales"
})
```

--------------------------------

### SQL WHERE Clause Example

Source: https://nosqlbooster.com/featuresForSQL

Filters records to select customers from 'ShangHai'. Single quotes denote strings; double quotes may be misinterpreted.

```sql
SELECT * FROM customers WHERE country='ShangHai';
```

```javascript
db.customers.find({
  "country": "ShangHai"
})
```

--------------------------------

### Basic Aggregation Pipeline

Source: https://nosqlbooster.com/FluentQueryAPI

Initiate an aggregation pipeline. Calling aggregate without arguments or with an empty document matches every document.

```javascript
db.collection.aggregate()
```

```javascript
db.collection.aggregate({})
```

```javascript
db.collection.aggregate({a: 1})
```

```javascript
db.collection.aggregate({a: {$gt: 7}})
```

--------------------------------

### Run a Task with nbcli

Source: https://nosqlbooster.com/nbcli

Use the --task option to run NoSQLBooster tasks such as import, export, restore, backup, or query. The task name is provided as an argument.

```bash
nbcli --task my_mongo_backup_task
```

--------------------------------

### Run Task with nbcli

Source: https://nosqlbooster.com/nbcli

Executes a predefined NoSQLBooster task, such as import, export, restore, or backup. Specify the task by its ID or name using the --task option.

```bash
nbcli --task 'my_mongo_backup_task'
```

--------------------------------

### SQL BETWEEN Operator Example

Source: https://nosqlbooster.com/featuresForSQL

The SQL BETWEEN operator checks if an expression falls within an inclusive range. Use ISODate for casting strings to MongoDB Dates. NOT BETWEEN is not supported.

```SQL
SELECT *
FROM orders
WHERE ord_date BETWEEN ISODate('2016-02-27') AND ISODate('2017-02-27');
```

```javascript
db.orders.find({
  "ord_date": {
    "$gte": ISODate("2016-02-27T08:00:00.000+08:00"),
    "$lte": ISODate("2017-02-27T08:00:00.000+08:00")
  }
})
```

--------------------------------

### SQL IN Operator Example

Source: https://nosqlbooster.com/featuresForSQL

Use the SQL IN operator to match any value in a list, reducing the need for multiple OR conditions. The equivalent MongoDB query uses the $in operator.

```SQL
SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Flowers Foods');
```

```javascript
db.suppliers.find({
  "supplier_name": {
    "$in": [
      "Microsoft",
      "Oracle",
      "Flowers Foods"
    ]
  }
})
```

--------------------------------

### Simplified aggregation project with Operator Helper

Source: https://nosqlbooster.com/FluentQueryAPI

Construct complex aggregation pipeline stages, such as $project with $and conditions, using the operator helper for better readability.

```javascript
db.inventory.aggregate([
    {
        $project: {
            item: 1,
            qty: 1,
            qtyGte250Lt500: {
                $and: [{
                    $gte: ["$qty", 250]
                }, {
                    $lt: ["$qty", 300]
                }]
            },
            _id: 0
        }
    }, {
        $limit: 1000
    ]
)

//we can write:
db.inventory.aggregate().project({
    item: 1,
    qty: 1,
    qtyGte250Lt500: $.and($.gte("$qty",250), $.lt("$qty", 300))
    _id: 0
}).limit(1000)
```

```javascript
//or
db.inventory.aggregate().project(
    $.where("item",1)
        .where("qty",1)
        .where("qtyGte250Lt500").and($.gte("$qty",250), $.lt("$qty", 300))
        .where("_id",0)
).limit(1000)
```

--------------------------------

### SQL HAVING Clause Example

Source: https://nosqlbooster.com/featuresForSQL

The SQL HAVING clause filters groups based on a condition, used in conjunction with the GROUP BY clause. It restricts returned rows to groups where the condition is TRUE.

```SQL
SELECT dept_id, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 5000
GROUP BY dept_id
HAVING COUNT(*) > 5;
```

--------------------------------

### SQL ORDER BY ASC Example

Source: https://nosqlbooster.com/featuresForSQL

The SQL ORDER BY clause sorts records. ASC is the default and can be omitted. MongoDB uses the .sort() method with 1 for ascending order.

```SQL
SELECT *
FROM customers
ORDER BY last_name ASC; --Most programmers omit the ASC attribute if sorting in ascending order.
```

```MongoDB
db.customers.find({})
  .sort({
    "last_name": 1
  })
```

--------------------------------

### Use Custom JavaScript Files (CommonJS)

Source: https://nosqlbooster.com/useNodeModule

Import and use your own JavaScript files as modules in NoSQLBooster using CommonJS syntax. Ensure the file exports the desired functions or variables.

```javascript
//c://test/myproject/utils.js

const replaceStr = (str, char, replacer) => {
  const regex = new RegExp(char, "g")
  const replaced = str.replace(regex, replacer)
  return replaced
}
exports.replaceStr =  replaceStr;
```

```javascript
const {replaceStr}= require("c://test/myproject/utils");
console.log(replaceStr("hello world","world","nosqlbooster"));  //hello nosqlbooster
```

--------------------------------

### Apply JavaScript Method to Each Document

Source: https://nosqlbooster.com/runSQLQuery

Demonstrates using the 'forEach' method after running a SQL query to apply a custom JavaScript function to each returned document.

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

--------------------------------

### SQL Single Line Comments

Source: https://nosqlbooster.com/featuresForSQL

Single line comments in SQL start with '--' and are used to explain statements or temporarily disable code. Any text following '--' on the same line is ignored.

```SQL
--Select all customer:
SELECT * FROM customers;
```

```SQL
SELECT * FROM customers -- WHERE City='ShangHai';
```

--------------------------------

### SQL Select Individual Fields

Source: https://nosqlbooster.com/featuresForSQL

Selects specific fields ('supplier_name', 'city') from the 'suppliers' table. The MongoDB equivalent uses a $project stage.

```sql
SELECT supplier_name, city FROM suppliers
```

```javascript
db.suppliers.aggregate([
  {
    "$project": {
      "supplier_name": 1,
      "city": 1
    }
  }])
```

--------------------------------

### Mathjs Functions and Constants

Source: https://nosqlbooster.com/shellExtensions

Demonstrates basic usage of mathjs functions like round, atan2, log, sqrt, and pow, including handling complex numbers and matrices.

```javascript
print(math.round(math.e, 3));            // 2.718
print(math.atan2(3, -3) / math.pi);      // 0.75
print(math.log(1000, 10));               // 3
print(math.sqrt(-4));                    // 2i
print(math.pow([[-1, 2], [3, 1]], 2));   // [[7, 0], [0, 7]]
```

--------------------------------

### SQL Date Range Snippet

Source: https://nosqlbooster.com/runSQLQuery

Provides a SQL snippet for filtering data within a date range. It uses placeholder '|' for the date field and demonstrates how to specify start and end dates.

```SQL
--  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")
```

--------------------------------

### fetch()

Source: https://nosqlbooster.com/shellExtensions

Provides the `window.fetch` API for making HTTP requests directly within your MongoDB scripts. It supports both text and JSON responses.

```APIDOC
## fetch()

### Description
Integrates the `node-fetch` library, providing the `window.fetch` API for making HTTP requests from within MongoDB scripts. Supports fetching various resource types and parsing responses.

### Method
Not applicable (Global JavaScript function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```javascript
(async ()=> {
    // Fetch HTML content
    const textRes = await fetch('https://github.com/');
    const text = await textRes.text();
    console.log(text.substring(0, 100)); // Log first 100 chars

    // Fetch JSON data
    const jsonRes = await fetch('https://api.github.com/users/github');
    const json = await jsonRes.json();
    console.log(json.name);
})();
```

### Response
#### Success Response (200)
Returns a `Response` object that can be processed to extract data (e.g., `.text()`, `.json()`).

#### Response Example
```json
// Example for jsonRes.json()
{
  "login": "github",
  "id": 12345,
  "name": "GitHub"
}
```
```

--------------------------------

### MongoDB Equivalent for LIKE

Source: https://nosqlbooster.com/featuresForSQL

MongoDB uses regular expressions for pattern matching. /^J.*$/i matches strings starting with 'J' case-insensitively, and /^.5$/i matches strings with any single character followed by '5'.

```javascript
db.customers.find({
    "last_name": /^J.*$/i
  })  .sort({
    "last_name": 1
  })
```

```javascript
db.categories.find({
  "category_id": /^.5$/i
})
```

--------------------------------

### Basic Fluent Query Construction

Source: https://nosqlbooster.com/FluentQueryAPI

Use chained methods for building queries instead of JSON objects. Supports direct condition passing and chaining multiple conditions.

```javascript
db.user.where('age').gte(18).lte(65).select('name age -_id').sort("-age name");
```

```javascript
db.collection.find().where({ name: 'nosqlbooster' })
```

```javascript
db.collection
    .where('age').gte(18).lte(65)
    .where({ 'name': /^nosqlbooster/i })
    .where('friends').slice(10)
```

--------------------------------

### SQL ORDER BY ASC and DESC Example

Source: https://nosqlbooster.com/featuresForSQL

Sort records by multiple fields in different orders using SQL's ORDER BY with ASC and DESC. MongoDB's sort method uses 1 for ASC and -1 for DESC.

```SQL
SELECT *
FROM products
WHERE product_id <> 8
ORDER BY category_id DESC, product_name ASC;
```

```MongoDB
db.products.find({
    "product_id": {
      "$ne": 8
    }
  })
  .sort({
    "category_id": -1,
    "product_name": 1
  })
```

--------------------------------

### Chaining Skip and Limit in SQL Query

Source: https://nosqlbooster.com/featuresForSQL

When using mb.runSQLQuery(), you can chain .skip() and .limit() methods for pagination, similar to MongoDB's approach, instead of using the LIMIT offset, count syntax.

```javascript
mb.runSQLQuery(`
    select * from housing order by units desc
`)
  .skip(3)   //chaining skip and limit methods
  .limit(10)
```

--------------------------------

### Explain Complex MongoDB Script

Source: https://nosqlbooster.com/aihelper

Generate detailed step-by-step explanations for complex MongoDB scripts to help understand query logic and improve performance. Includes performance considerations and index recommendations.

```mongodb
db.users.find({
  age: { $gt: 30 },
  $or: [
    { role: "admin" },
    { status: "active", lastLogin: { $gte: ISODate("2025-03-01") } }
  ],
  location: { $geoWithin: { $centerSphere: [ [ -73.93, 40.73 ], 10 / 3963.2 ] } }
}).sort({ lastLogin: -1 }).limit(50)
```

```text
This query performs the following operations:

1. Searches the "users" collection for documents where:
   - The "age" field is greater than 30
   - AND one of the following conditions is true:
     a. The "role" field equals "admin"
     b. The "status" field equals "active" AND the "lastLogin" field is greater than or equal to March 1, 2025

2. Additionally, the document's "location" field must be within 10 miles of the geographic coordinates [-73.93, 40.73] (near New York City)

3. Results are sorted by "lastLogin" field in descending order (most recent first)

4. Only the first 50 matching documents are returned

PERFORMANCE CONSIDERATIONS:
- For optimal performance, ensure you have indexes on "age", "role", "status", "lastLogin", and a geospatial index on "location"
- The $or operator may prevent the use of some indexes; consider monitoring query performance
```

--------------------------------

### SQL SELECT INTO Statement

Source: https://nosqlbooster.com/featuresForSQL

Shows the syntax for copying data from an existing table into a new table. Note: This specific SQL syntax is not supported.

```sql
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
```

--------------------------------

### SQL SELECT Syntax

Source: https://nosqlbooster.com/featuresForSQL

Use the SELECT statement to retrieve data from a table or collection. Specify columns or use '*' for all fields.

```sql
SELECT column1, column2, ...
FROM table_name;
```

```sql
SELECT * FROM table_name;
```

--------------------------------

### all()

Source: https://nosqlbooster.com/FluentQueryAPI

Specifies a $all query condition.

```APIDOC
## all()

### Description
Specifies a `$all` query condition.

### Method
```javascript
all(values)
```

### Parameters
- **values** (Array) - An array of values to match.

### Example
```javascript
db.collection.where('permission').all(['read', 'write'])
```
```

--------------------------------

### Other Aggregation Stage Operators

Source: https://nosqlbooster.com/FluentQueryAPI

Demonstrates chaining various aggregation stage operators including match, project, addFields, unwind, group, sort, and limit.

```javascript
db.scores.aggregate()
      .match($.where("status").eq("A"))
      .project("gender _id")
      .addFields({ totalScore:
           { $add: [ "$totalHomework", "$totalQuiz", "$extraCredit" ] } })
      .unwind("$arrayField")
      .group({ _id: "$gender", count: { $sum: 1 } })
      .sort("-count")
      .limit(5)
```

--------------------------------

### Use Node.js Built-in Modules (fs, path)

Source: https://nosqlbooster.com/useNodeModule

Import and use Node.js built-in modules like 'fs' and 'path' directly in your scripts using CommonJS syntax.

```javascript
const {existsSync}=require("fs"); //CommonJS module
existsSync("myFile");

const path=require("path");
path.resolve('/foo/bar', './baz');// Returns: '/foo/bar/baz'
```

--------------------------------

### project()

Source: https://nosqlbooster.com/FluentQueryAPI

Adds a $project stage to the aggregation pipeline to reshape documents.

```APIDOC
## project()

### Description
Add a $project stage to the aggregation pipeline. Specify which document fields to include or exclude.

### Method
db.collection.project(spec: object | string)

### Example
```javascript
// Include fields, exclude _id
db.collection.project({ name: 1, address: 1, _id: 0 })

// Exclude field using prefix -
db.collection.project('name address -_id')
```
```

--------------------------------

### Run SQL GROUP BY Query with SUM Function

Source: https://nosqlbooster.com/runSQLQuery

Execute a SQL query to group employees by department and calculate the total salary for each department using the SUM function. This demonstrates the `mb.runSQLQuery` interface for integrating SQL queries into scripts.

```javascript
mb.runSQLQuery(`

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

`);

```

--------------------------------

### Run JavaScript File with nbcli

Source: https://nosqlbooster.com/nbcli

Executes a JavaScript file. Provide the path to the .js file as the script argument. Ensure the file contains valid JavaScript code executable by NoSQLBooster.

```bash
echo db.getName() > ~/tmp/get_db_name.js
bcli localhost/test ~/tmp/get_db_name.js
```

```bash
nbcli "mongodb://localhost/test" ~/test.js
```

--------------------------------

### Aggregation Pipeline - Chained Stages

Source: https://nosqlbooster.com/FluentQueryAPI

Shows how to chain multiple aggregation stages for complex data processing.

```APIDOC
### Aggregation Pipeline - Chained Stages

Additional methods can then be chained on top of the initial match to make more complicated aggregations.

### Examples
```javascript
// Match and project
db.collection.aggregate(<querydoc>).project(<projection>)
db.collection.aggregate({a: 1}).project({a: 1, _id: 0})

// Match, group and sort
db.collection.aggregate({<match>}).group({<group>}).sort({<sort>})
db.test.aggregate().group({_id: '$a', 'sum': {'$sum': 1}}).sort({sum: -1})

db.companies.aggregate()
  .match($.where('founded_year').gte(2000).lte(2010)) //qb:querybuilder
  .group({_id:"$category_code",count:{$sum:1}})
  .sort('-count')
  .limit(100)
```
```

--------------------------------

### bitsAllSet()

Source: https://nosqlbooster.com/FluentQueryAPI

Specifies arguments for a $bitsAllSet condition.

```APIDOC
## bitsAllSet()

### Description
Specifies arguments for a $bitsAllSet condition. $bitsAllSet matches documents where all of the bit positions given by the query are set (i.e., 1) in the field.

### Method
```javascript
bitsAllSet(bits)
```

### Parameters
- **bits** (Array) - An array of bit positions.

### Example
```javascript
db.collection.where("a").bitsAllSet([ 1, 5 ])
```
```

--------------------------------

### Specifying Equivalent Comparison Value

Source: https://nosqlbooster.com/FluentQueryAPI

Use `eq()` or `equals()` to specify an equivalent comparison value for a given path. This is equivalent to providing a direct key-value pair in the query object.

```javascript
db.collection.where('age').eq(49);
db.collection.where('age').equals(49);
```

```javascript
// is the same as
db.collection.where({ 'age': 49 });
```

--------------------------------

### Set Query Hints

Source: https://nosqlbooster.com/FluentQueryAPI

Use the hint() method to specify query hints for optimizing query performance. This option cannot be used with distinct().

```javascript
db.collection.hint({ indexA: 1, indexB: -1 })
```

--------------------------------

### Generate MongoShell Script with Operator Helper

Source: https://nosqlbooster.com/FluentQueryAPI

Use the getShellScript() method with the operator helper to obtain the equivalent MongoShell syntax for complex queries.

```javascript
db.survey.where("results").elemMatch($.where("item").eq("b").where("score").gt(8))
    .getShellScript();
```

--------------------------------

### SQL Subquery with EXISTS

Source: https://nosqlbooster.com/featuresForSQL

Demonstrates the use of the EXISTS operator in a subquery to test for record existence. Note: This specific SQL syntax is not supported.

```sql
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
```

--------------------------------

### Cursor Extension Methods

Source: https://nosqlbooster.com/FluentQueryAPI

Details on cursor extension methods like getShellScript, getAggregationPipeline, and saveAsView.

```APIDOC
## Cursor Extension Methods

### Description
NoSQLBooster extends the MongoDB cursor object with several useful methods for generating shell scripts, retrieving aggregation pipelines, and saving pipelines as views.

### Methods

#### `cursor.getShellScript()`

##### Description
Returns the translated MongoDB shell script for the query or aggregation pipeline that produced this cursor.

##### Usage
```javascript
// For a find query:
db.collection.find().where('age').gte(18).getShellScript();

// For an aggregation pipeline:
db.companies.aggregate().match({status: 'active'}).getShellScript();
```

#### `cursor.getAggregationPipeline()`

##### Description
Returns the underlying aggregation pipeline as a JavaScript array of stage objects.

##### Usage
```javascript
db.companies.aggregate()
  .match({status: 'active'})
  .group({_id: '$category'})
  .getAggregationPipeline();
```

#### `cursor.saveAsView(viewName)`

##### Description
Saves the underlying aggregation pipeline as a MongoDB readonly view.

##### Parameters
- **viewName** (String) - Required. The name of the view to create.

##### Usage
```javascript
db.companies.aggregate()
  .match({status: 'active'})
  .group({_id: '$category'})
  .saveAsView('active_companies_by_category');
```
```

--------------------------------

### cursor.getAggregationPipeline()

Source: https://nosqlbooster.com/FluentQueryAPI

Returns the aggregation pipeline generated by the Fluent Query Builder.

```APIDOC
## cursor.getAggregationPipeline()

### Description
Cursor.getAggregationPipeline() returns the aggregation pipeline produced by the NoSQLBooster for MongoDB Fluent Query Builder.

### Method
```javascript
getAggregationPipeline()
```

### Example
```javascript
db.user.where('age').gte(18).lte(65)
       .select('name age -_id')
       .sort("-age name")
       .limit(5)
       .skip(100)
       .getAggregationPipeline()
```

### Response Example
```javascript
[
    {
        "$match" : {
            "age" : {
                "$gte" : 18,
                "$lte" : 65
            }
        }
    },
    {
        "$project" : {
            "name" : 1,
            "age" : 1,
            "_id" : 0
        }
    },
    {
        "$sort" : {
            "age" : -1,
            "name" : 1
        }
    },
    {
        "$skip" : 100
    },
    {
        "$limit" : 5
    }
]
```
```

--------------------------------

### SQL Select All Fields

Source: https://nosqlbooster.com/featuresForSQL

Selects all fields from the 'customers' table. The equivalent MongoDB query uses the aggregate function.

```sql
SELECT * FROM customers
```

```javascript
db.customers.aggregate()
```

--------------------------------

### Run SQL Query with nbcli

Source: https://nosqlbooster.com/nbcli

Executes a SQL query against the specified database. Ensure the database address and the SQL statement are correctly formatted.

```bash
nbcli localhost:27017 "select name, address.email as email from users"
```

```bash
nbcli "localhost/mb-user-mgr" "select * from mborders"
```

--------------------------------

### Create Readonly View using Pipeline Builder

Source: https://nosqlbooster.com/FluentQueryAPI

Demonstrates creating a readonly view by defining the aggregation pipeline using $.pipelineBuilder's match and sort methods.

```javascript
//instead of writing:
db.createView(
    "firstYearsScoreDesc",
    "students",
    [ { $match: { year: 1 } }, {$sort :{score: -1}} ] //pipeline
)

//we can write:
db.createView(
    "firstYearsScoreDesc",
    "students",
    $.pipelineBuilder.match({year:1}).sort({score: -1})  //pipeline
)
```

--------------------------------

### Send Mongo Shell Commands to Multiple Replica Nodes

Source: https://nosqlbooster.com/codeEditing

Execute Mongo Shell commands on multiple replica set nodes simultaneously. The results are aggregated into a JSON document, indicating the member, its role (PRI for primary), and the execution result.

```javascript
db.version()
```

```json
[
    {
        "member": "localhost:28000-PRI",
        "result": "8.0.0"
    },
    {
        "member": "localhost:28001",
        "result": "8.0.0"
    },
    {
        "member": "localhost:28002",
        "result": "8.0.0"
    }
]
```

--------------------------------

### Specify Batch Size Option

Source: https://nosqlbooster.com/FluentQueryAPI

Use the batchSize() method to specify the batch size option for queries. Cannot be used with distinct().

```javascript
query.batchSize(100)
```

--------------------------------

### Add $project Stage to Aggregation

Source: https://nosqlbooster.com/FluentQueryAPI

Use the project() method to add a $project stage, specifying which document fields to include or exclude.

```javascript
// 1 means include, 0 means exclude
db.collection.project({ name: 1, address: 1, _id: 0 })
```

```javascript
//or prefixing a path with - will flag that path as excluded.
db.collection.project('name address -_id')
```
Tokens:
Raw