Pavel Duchovny

# Generating MQL Shell Commands Using OpenAI and New mongosh Shell

[OpenAI](https://beta.openai.com/) is a fascinating and growing AI platform sponsored by Microsoft, allowing you to digest text cleverly to produce AI content with stunning results considering how small the “learning data set” you actually provide is.

MongoDB’s Query Language (MQL) is an intuitive language for developers to interact with MongoDB Documents. For this reason, I wanted to put OpenAI to the test of quickly learning the MongoDB language and using its overall knowledge to build queries from simple sentences. The results were more than satisfying to me. Github is already working on a project called [Github copilot](https://copilot.github.com/) which uses the same OpenAI engine to code.

In this article, I will show you my experiment, including the game-changing capabilities of the new [MongoDB Shell](https://docs.mongodb.com/mongodb-shell/) (`mongosh`) which can extend scripting with npm modules integrations.

## What is OpenAI and How Do I Get Access to It?

OpenAI is a unique project aiming to provide an API for many AI tasks built mostly on Natural Language Processing today. You can read more about their projects in this [blog](https://openai.com/blog/openai-api/).

There are a variety of examples for its [text processing capabilities](https://beta.openai.com/examples).

If you want to use OpenAI, you will need to get a trial API key first by joining the [waitlist](https://share.hsforms.com/1Lfc7WtPLRk2ppXhPjcYY-A4sk30) on their [main](https://beta.openai.com/) page. Once you are approved to get an API key, you will be granted about $18 for three months of testing. Each call in OpenAI is  [billed](https://beta.openai.com/pricing) and this is something to consider when using in production. For our purposes, $18 is more than enough to test the most expensive engine named “davinci.”


Once you get the API key, you can use various [clients](https://beta.openai.com/docs/developer-quickstart/community-libraries) to run their AI API from your script/application. 

Since we will be using the new `mongosh` shell, I have used the
 [JS API](https://www.npmjs.com/package/openai-api).

## Preparing the mongosh to Use OpenAI

First, we need to [install](https://docs.mongodb.com/mongodb-shell/install/) the new shell, if you haven’t done it so far. On my Mac laptop, I just issued:

``` bash
brew install mongosh
```

Windows users should download the MSI installer from our [download page](https://www.mongodb.com/try/download/shell) and follow the Windows instructions.

Once my mongosh is ready, I can start using it, but before I do so, let’s install [OpenAI JS](https://www.npmjs.com/package/openai-api), which we will import in the shell later on:

``` bash
$ mkdir openai-test
$ cd openai-test
Openai-test $ npm i openai-api
```

I’ve decided to use the Questions and Answers pattern, in the form of `Q: <Question>` and `A: <Answer>`, provided to the [text to command](https://beta.openai.com/examples/default-text-to-command) completion API to provide the learning material about MongoDB queries for the AI engine. To better feed it, I placed the training questions and answers in a file called `AI-input.txt` and its content:

```
Q: What is the query syntax?
A: db.collection.find(<filter>, <projection> , <options>)
Q:  Query users collection for username with value "boy"
A: db.users.find({"username" : "boy"})
Q:  Query users collection for username with value "girl"A:  db.users.find({"username" : "girl"})
Q: Query users collection for username with age bigger than 16
A:  db.users.find({"age" : {$gt : 16}})n;
Q: Query author collection for username with value "boy"
A: db.authors.find({"username" : "boy"})
Q:Query author collection for age lower than 7
A: db.authors.find({"age" : {$lt : 7}});

Q:insert a json document into collection authors with username equal to "girl"
A: db.authors.insert({"username" : "girl"}, {"age" : 10, "gender" : "female"})
Q: insert many documents into users collections
A: db.users.insertMany([{username : "john doe"}, {username : "joe doe"}]);
Q: The following aggregation uses $geoNear to find documents with a location at most 2 meters from the center [ -73.99279 , 40.719296 ] and a category equal to Parks.
A: db.places.aggregate([{ $geoNear: {near: { type: "Point", coordinates: [ -73.99279 , 40.719296 ] },distanceField: "dist.calculated",maxDistance: 2, query: { category: "Parks" },includeLocs: "dist.location", spherical: true}}])
Q: How to build atlas $search text query?
A: db.collection.aggregate({$search: {"index": <index name> "text": {"query": "<search-string>","path": "<field-to-search>",  "fuzzy": <options>,"score": <options>  }  }})

Q: What is the aggregate syntax?
A: db.collection.aggregate([<stage1>,<stage2>,<stage3>], <projection> , <options>);
Q: aggregate users collection to calculate salary sum per user
A: db.users.aggregate([{$group : { _id : "$username" , salary_sum : { $sum : "$salary" }}}]);
Q: aggregate person collection to calculate salary sum per person
A: db.persons.aggregate([{$group : { _id : "$person" , salary_sum : { $sum : "$salary" }}}]);
Q: Lookup users and orders collection
A: db.users.aggregate([{$lookup : {from: 'orders', localField : "_id", foreignField : "userId", as : "result" }} ]);

Q: What is the update syntax?
A:db.collection.update(query, update, options)
Q:  How to edit collection sports where sportname is 'football' and match is 'england vs portugal' to score of '3-3' and date to current date?
A: db.sports.update({ sportname: "football", match: "england vs portugal"} , {$set : {score: "3-3" , date : new Date()}} })
Q: Query and atomically update collection zoo where animal is "bear" with a counter increment on eat field, if the data does not exist user upsert
A: db.zoo.findOneAndUpdate({animal : "bear"}, {$inc: { eat : 1 }} , {upsert : true})
```

We will use this file later in our code.

This way, the completion will be based on a similar pattern.

### Prepare Your Atlas Cluster



[MongoDB Atlas](https://www.mongodb.com/cloud/atlas),  the database-as-a-platform service, is a great way to have a running cluster in seconds with a sample dataset already there for our test. To prepare it, please use the following steps:

1. Create an Atlas account (if you don’t have one already) and use/start a cluster. For detailed steps, follow this [documentation](https://docs.atlas.mongodb.com/getting-started/).
2. [Load the sample data set](https://docs.atlas.mongodb.com/sample-data/).
3. [Get your connection string](https://docs.atlas.mongodb.com/connect-to-cluster/).

Use the copied connection string, providing it to the `mongosh` binary to connect to the pre-populated Atlas cluster with sample data. Then, switch to `sample_restaurants`
database.


``` js
mongosh "mongodb+srv://<u>:<p>@<atlas-uri>/sample_restaurants"
Using Mongosh : X.X.X
Using MongoDB:		X.X.X

For mongosh info see: https://docs.mongodb.com/mongodb-shell/

ATLAS atlas-ugld61-shard-0 [primary]> use sample_restaurants;
```

## Using OpenAI Inside the mongosh Shell

Now, we can build our `textToMql` function by pasting it into the `mongosh`. The function will receive a text sentence, use our generated OpenAI API key, and will try to return the best MQL command for it:

``` js
async function textToMql(query){

const OpenAI = require('openai-api');
const openai-client = new OpenAI("<YOUR-OPENAI-API-KEY>");

const fs = require('fs');

var data =  await  fs.promises.readFile('AI-input.txt', 'utf8');

const learningPath = data;

var aiInput =  learningPath + "Q:" + query + "\nA:";

 const gptResponse = await openai-client.complete({
     engine: 'davinci',
     prompt: aiInput,
     "temperature": 0.3,
     "max_tokens": 400,
     "top_p": 1,
     "frequency_penalty": 0.2,
     "presence_penalty": 0,
     "stop": ["\n"]
 });

 console.log(gptResponse.data.choices[0].text);
}
```

In the above function, we first load the OpenAI npm module and initiate a client with the relevant API key from OpenAI. 

``` js
const OpenAI = require('openai-api');
const openai-client = new OpenAI("<YOUR-OPENAI-API-KEY>");

const fs = require('fs');
```

The new shell allows us to import built-in and external [modules](https://docs.mongodb.com/mongodb-shell/write-scripts/#require-a-native-module) to produce an unlimited flexibility with our scripts.

Then, we read the learning data from our `AI-input.txt` file. Finally we add our `Q: <query>` input to the end followed by the `A:` value which tells the engine we expect an answer based on the provided learningPath and our query. 


This data will go over to an OpenAI API call:

``` js
 const gptResponse = await openai.complete({
     engine: 'davinci',
     prompt: aiInput,
     "temperature": 0.3,
     "max_tokens": 400,
     "top_p": 1,
     "frequency_penalty": 0.2,
     "presence_penalty": 0,
     "stop": ["\n"]
 });
```

The call performs a completion API and gets the entire initial text as a `prompt` and receives some additional parameters, which I will elaborate on:

* `engine`: OpenAI supports a few AI engines which differ in quality and purpose as a tradeoff for pricing. The “davinci” engine is the most sophisticated one, according to OpenAI, and therefore is the most expensive one in terms of billing consumption.
* `temperature`: How creative will the AI be compared to the input we gave it? It can be between 0-1. 0.3 felt like a down-to-earth value, but you can play with it.
* `Max_tokens`: Describes the amount of data that will be returned.
* `Stop`: List of characters that will stop the engine from producing further content. Since we need to produce MQL statements, it will be one line based and “\n” is a stop character.

Once the content is returned, we parse the returned JSON and print it with `console.log`.

### Lets Put OpenAI to the Test with MQL

Once we have our function in place, we can try to produce a simple query to test it:

``` js
Atlas atlas-ugld61-shard-0 [primary] sample_restaurants> textToMql("query all restaurants where cuisine is American and name starts with 'Ri'")
 db.restaurants.find({cuisine : "American", name : /^Ri/})

Atlas atlas-ugld61-shard-0 [primary] sample_restaurants> db.restaurants.find({cuisine : "American", name : /^Ri/})
[
  {
    _id: ObjectId("5eb3d668b31de5d588f4292a"),
    address: {
      building: '2780',
      coord: [ -73.98241999999999, 40.579505 ],
      street: 'Stillwell Avenue',
      zipcode: '11224'
    },
    borough: 'Brooklyn',
    cuisine: 'American',
    grades: [
      {
        date: ISODate("2014-06-10T00:00:00.000Z"),
        grade: 'A',
        score: 5
      },
      {
        date: ISODate("2013-06-05T00:00:00.000Z"),
        grade: 'A',
        score: 7
      },
      {
        date: ISODate("2012-04-13T00:00:00.000Z"),
        grade: 'A',
        score: 12
      },
      {
        date: ISODate("2011-10-12T00:00:00.000Z"),
        grade: 'A',
        score: 12
      }
    ],
    name: 'Riviera Caterer',
    restaurant_id: '40356018'
  }
...
```

Nice! We never taught the engine about the  `restaurants` collection or how to filter with [regex](https://docs.mongodb.com/manual/reference/operator/query/regex/) operators but it still made the correct AI decisions. 

Let's do something more creative.

``` js
Atlas atlas-ugld61-shard-0 [primary] sample_restaurants> textToMql("Generate an insert many command with random fruit names and their weight")
 db.fruits.insertMany([{name: "apple", weight: 10}, {name: "banana", weight: 5}, {name: "grapes", weight: 15}])
Atlas atlas-ugld61-shard-0 [primary]sample_restaurants>  db.fruits.insertMany([{name: "apple", weight: 10}, {name: "banana", weight: 5}, {name: "grapes", weight: 15}])
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("60e55621dc4197f07a26f5e1"),
    '1': ObjectId("60e55621dc4197f07a26f5e2"),
    '2': ObjectId("60e55621dc4197f07a26f5e3")
  }
}
```

Okay, now let's put it to the ultimate test: [aggregations](https://docs.mongodb.com/manual/aggregation/)!

``` js
Atlas atlas-ugld61-shard-0 [primary] sample_restaurants> use sample_mflix;
Atlas atlas-ugld61-shard-0 [primary] sample_mflix> textToMql("Aggregate the count of movies per year (sum : 1) on collection movies")
 db.movies.aggregate([{$group : { _id : "$year", count : { $sum : 1 }}}]);

Atlas atlas-ugld61-shard-0 [primary] sample_mflix>  db.movies.aggregate([{$group : { _id : "$year", count : { $sum : 1 }}}]);
[
  { _id: 1967, count: 107 },
  { _id: 1986, count: 206 },
  { _id: '2006è2012', count: 2 },
  { _id: 2004, count: 741 },
  { _id: 1918, count: 1 },
  { _id: 1991, count: 252 },
  { _id: 1968, count: 112 },
  { _id: 1990, count: 244 },
  { _id: 1933, count: 27 },
  { _id: 1997, count: 458 },
  { _id: 1957, count: 89 },
  { _id: 1931, count: 24 },
  { _id: 1925, count: 13 },
  { _id: 1948, count: 70 },
  { _id: 1922, count: 7 },
  { _id: '2005è', count: 2 },
  { _id: 1975, count: 112 },
  { _id: 1999, count: 542 },
  { _id: 2002, count: 655 },
  { _id: 2015, count: 484 }
]
```

Now *that* is the AI power of MongoDB pipelines!

## DEMO

[![asciicast](https://asciinema.org/a/424297.svg)](https://asciinema.org/a/424297)

## Wrap-Up

MongoDB's new shell allows us to script with enormous power like never before by utilizing npm external packages. Together with the power of OpenAI sophisticated AI patterns, we were able to teach the shell how to prompt text to accurate complex MongoDB commands, and with further learning and tuning, we can probably get much better results.

Try this today using the new MongoDB shell.

Learn how new mongosh external modules can be used to generate MQL language via OpenAI engine. Transform simple text sentences into sophisticated queries. 

Generating MQL Shell Commands Using OpenAI and New mongosh Shell

Nic Raboy

There are many methods available for interacting with MongoDB and depending on what you're trying to accomplish, one way to work with MongoDB might be better than another. For example, if you're a power user of Visual Studio Code, then the [MongoDB Extension for Visual Studio Code](https://code.visualstudio.com/docs/azure/mongodb) might make sense. If you're constantly working with infrastructure and deployments, maybe the [MongoDB CLI](https://docs.mongodb.com/mongocli/stable/) makes the most sense. If you're working with data but prefer a command line experience, the MongoDB Shell is something you'll be interested in.

The [MongoDB Shell](https://www.mongodb.com/products/shell) gives you a rich experience to work with your data through syntax highlighting, intelligent autocomplete, clear error messages, and the ability to extend and customize it however you'd like.

In this article, we're going to look a little deeper at the things we can do with the MongoDB Shell.

## Syntax Highlighting and Intelligent Autocomplete

If you're like me, looking at a wall of code or text that is a single color can be mind-numbing to you. It makes it difficult to spot things and creates overall strain, which could damage productivity. Most development IDEs don't have this problem because they have proper syntax highlighting, but it's common for command line tools to not have this luxury. However, this is no longer true when it comes to the MongoDB Shell because it is a command line tool that has syntax highlighting.

![MongoDB Shell Syntax Highlighting](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/mongosh_syntax_highlight_3c9ebac7b9.png "MongoDB Shell Syntax Highlighting")

When you write commands and view results, you'll see colors that match your command line setup as well as pretty-print formatting that is readable and easy to process.

Formatting and colors are only part of the battle that typical command line advocates encounter. The other common pain-point, that the MongoDB Shell fixes, is around autocomplete. Most IDEs have autocomplete functionality to save you from having to memorize every little thing, but it is less common in command line tools.

As you're using the MongoDB Shell, simply pressing the "Tab" key on your keyboard will bring up valuable suggestions related to what you're trying to accomplish.

![Autocomplete with the MongoDB Shell](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/mongosh_autocomplete_7a1ac066ae.gif "Autocomplete with the MongoDB Shell")

Syntax highlighting, formatting, and autocomplete are just a few small things that can go a long way towards making the developer experience significantly more pleasant.

## Error Messages that Actually Make Sense

How many times have you used a CLI, gotten some errors you didn't understand, and then either wasted half your day finding a missing comma or rage quit? It's happened to me too many times because of poor error reporting in whatever tool I was using.

With the MongoDB Shell, you'll get significantly better error reporting than a typical command line tool.

![MongoDB Shell Error Reporting](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/mongosh_error_reporting_4455bb3eca.png "MongoDB Shell Error Reporting")

In the above example, I've forgotten a comma, something I do regularly along with colons and semi-colons, and it told me, along with providing a general area on where the comma should go. That's a lot better than something like "Generic Runtime Error 0x234223."

## Extending the MongoDB Shell with Plugins Known as Snippets

If you use the MongoDB Shell enough, you'll probably reach a point in time where you wish it did something specific to your needs on a repetitive basis. Should this happen, you can always extend the tool with snippets, which are similar to plugins.

To get an idea of some of the official MongoDB Shell snippets, execute the following from the MongoDB Shell:

```bash
snippet search
```

The above command searches the snippets found in a repository on [GitHub](https://github.com/mongodb-labs/mongosh-snippets).

You can always define your own repository of snippets, but if you wanted to use one of the available but optional snippets, you could run something like this:

```bash
snippet install analyze-schema
```

The above snippet allows you to analyze any collection that you specify. So in the example of my "recipes" collection, I could do the following:

```bash
use food;
schema(db.recipes);
```

The results of the schema analysis, at least for my collection, is the following:

```
┌─────────┬───────────────┬───────────┬────────────┐
│ (index) │       0       │     1     │     2      │
├─────────┼───────────────┼───────────┼────────────┤
│    0    │ '_id        ' │ '100.0 %' │ 'ObjectID' │
│    1    │ 'ingredients' │ '100.0 %' │  'Array'   │
│    2    │ 'name       ' │ '100.0 %' │  'String'  │
└─────────┴───────────────┴───────────┴────────────┘
```

Snippets aren't the only way to extend functionality within the MongoDB Shell. You can also use Node.js in all its glory directly within the MongoDB Shell using custom scripts.

## Using Node.js Scripts within the MongoDB Shell

So let's say you've got a data need that you can't easily accomplish with the [MongoDB Query API](https://www.mongodb.com/mongodb-query-api) or an aggregation pipeline. If you can accomplish what you need using Node.js, you can accomplish what you need in the MongoDB Shell.

Let's take this example.

Say you need to consume some data from a remote service and store it in MongoDB. Typically, you'd probably write an application, download the data, maybe store it in a file, and load it into MongoDB or load it with one of the programming drivers. You can skip a few steps and make your life a little easier.

Try this.

When you are connected to the MongoDB Shell, execute the following commands:

```bash
use pokemon
.editor
```

The first will switch to a database—in this case, "pokemon"—and the second will open the editor. From the editor, paste in the following code:

```javascript
async function getData(url) {
    const fetch = require("node-fetch");
    const results = await fetch(url)
        .then(response => response.json());
    db.creatures.insertOne(results);
}
```

The above function will make use of the [node-fetch](https://www.npmjs.com/package/node-fetch) package from NPM. Then, using the package, we can make a request to a provided URL and store the results in a "creatures" collection.

You can execute this function simply by doing something like the following:

```bash
getData("https://pokeapi.co/api/v2/pokemon/pikachu");
```

If it ran successfully, your collection should have new data in it.

In regards to the NPM packages, you can either install them globally or to your current working directory. The MongoDB Shell will pick them up when you need them.

If you'd like to use your own preferred editor rather than the one that the MongoDB Shell provides you, execute the following command prior to attempting to open an editor:

```bash
config.set("editor", "vi");
```

The above command will make VI the default editor to use from within the MongoDB Shell. More information on using an external editor can be found in the [documentation](https://docs.mongodb.com/mongodb-shell/reference/editor-mode/#using-an-external-editor).

## Conclusion

You can do some neat things with the MongoDB Shell, and while it isn't for everyone, if you're a power user of the command line, it will certainly improve your productivity with MongoDB.

If you have questions, stop by the [MongoDB Community Forums](https://community.mongodb.com)!

Learn about the benefits of using the new MongoDB Shell for interacting with databases, collections, and the data inside.

Shell

All Shell Content

Designing, Developing, and Analyzing with the New MongoDB Shell

Generating MQL Shell Commands Using OpenAI and New mongosh Shell

Related Topics