Visual Query Builder

Basics

The Visual Query Builder allows for the creation of MongoDB queries via drag-and-drop, in combination with the Table and Tree Views.

Studio 3T’s drag-and-drop MongoDB query builder is part of the greater Collection Tab, which is where users can view, query, and edit documents within the collection.

The Collection Tab houses both the Visual Query Builder:

And the Main Query Bar, which shows the mongo shell’s syntax as it is being built:

There are three ways to open the Visual Query Builder:

• Button – Click on the Visual Query Builder button

• Right-click – Right-click anywhere in the Result tab and choose Query Builder [/ht_toggle]
• Hotkey – Press Ctrl + F (⌘+ F)

Build the MongoDB query

Let’s build a query using Studio 3T’s MongoDB query builder and the Customers replica data set, which can be downloaded here.

Drag and drop fields

Select the target field by clicking on any cell (not the header), then drag the selected field(s) and drop them into the Query Builder.

In Table View, fields can only be selected one at a time.

In Tree View, multiple lines can be selected and dropped into the Visual Query Builder simultaneously. To do this, hold ‘ctrl‘ and click the fields to be used.

Customize the query

To better specify the query, drag as many fields as necessary.

Here is the query built using the Visual Query Builder from the above GIF, in MongoDB query syntax:

db.Customers.find(
{
"title" : "Ms",
"transactions" : {
"$gt" : NumberInt(46)
}
}
);

Define values and operators

To further filter queries, define the desired operators and values in the Query, Projection, and Sort sections.

Query section

State the operators as needed under the Query Section.

By default, the $and operator is automatically used to combine queries.

The $or operator is NOT selected by default and must be manually added. To ensure functionality, state the $or operator before defining the fields as seen in the example below.

The Visual Query Builder will automatically detect value type (e.g. Int32, string, regex) from added fields. Switch between field types by clicking on the Value icon.

For string values, a multiline text editor – handy for editing long strings – can be opened by clicking on the ellipsis icon.

Similar advanced editors can also be found for regex, undefined, and binary values.

Projection section

The Projection section allows users to include or exclude specific fields from the query results.

Sort section

The Sort section lets users sort query results by a specific field, in either ascending or descending order.

It’s important to note that the Sort section can only define one field at a time.

Clone field rules

With Release 2018.5.1, you can now also clone field rules in one click – a nifty feature that spares you the repetitive drag-and-drop, especially when you’re dealing with very similar field rules.

Query MongoDB arrays

It is also possible to match on array elements using the Visual Query Builder, either as a standalone query or as part of an AND/OR group.

Standalone query

To directly find array elements that match a query criteria:

1. Within the Query Section, choose Has array element(s) matching from the dropdown.
   
2. This will trigger the Array dropdown to appear. Choose the array you want to query. We will look at the array cities_visited, which contains two elements at most (see column).
   
3. Step into the array-valued column you want to query.
4. Double-click in the Query Section to choose the default option <Array Element>.

   This might seem unnatural to veterans of VQB’s dragging function. Dragging array elements will cause Studio 3T to automatically take the JSON path (e.g. cities_visited_0). For the query to work, you need to set the field to the default option <Array Element>.

5. Define your operator (e.g. equals, contains)
6. Drag or type in the value to be matched on (in this example, Berlin).
7. Run the query.

Find array elements as part of an AND/OR group

Querying in an AND/OR group follows the same steps as a standalone query.

The only difference is, you must first state the desired operator at the top of the Query Section.

1. Select one of the following operators:- Match all of ($and)
   – Match any of ($or)
   – Match none of ($nor)In this case, there’s take the $or operator.
2. Click on Add AND/OR group.
3. From here, follow steps 1-6 of querying array elements in a standalone query.
4. Create more AND/OR groups as needed.
5. Run the query.

View the built query in the mongo shell language

The Visual Query Builder simultaneously displays the built query in mongo shell syntax under the Main Query Bar, which also shows the changes made to it as it’s being built.

Run the MongoDB query

Once the correct fields, values, and operators are in place, click the Execute button (play symbol) at the top of the screen or the Run button towards the bottom of the screen.

Generate JavaScript, Java, Python, PHP and C# code from built queries

In addition to showing the built query in its full mongo shell syntax, Query Code also translates built queries into JavaScript (Node.js), Java (2.x and 3.x driver API), Python and C#.

To see a built query’s equivalent code:

1. Execute the query
2. Click on the Query Code tab
3. Choose the target language