Working with the Finder

The Finder allows you to compose queries and execute queries to access data from the database without having to write code. Underneath the covers a MongoDB find command is used to query the database, but you need never compose these yourself.

The Finder supports two querying metaphors: a point-and-click metaphor and a facet metaphor. The point-and-click metaphor allows you to point at data that you see in the collection to specify the query, thereby following a patten of “show me data like this”. The facet metaphor allows you to write search expressions in a search widget while the system is auto-completing your typing with what you can search on.

The Finder is composed of four panes as shown in the following image (in addition to the standard control pane):

_images/finder01.jpg

In order to query the database you first need to select a collection to query. Pane #1 shows you all the collections in the database to which you are connected to. You can scroll through the collections using the controls and select the one you want to query. The Count column shows you how many documents are in each collection. If the Studio is not running in a read-only mode then you can also remove all documents in the collection (Rm / trash icon) or drop the collection completely (Drop / down arrow icon). A refresh button at the top of the Collection Name column allows you to rebuild the list in case a new collection was added to the database while you were working.

When you select a collection, a tab opens with a collection cursor for that collection in Pane #2. You may have more than one such tab open but only one collection serves for the query. If you have multiple tabs open on different collections you control which collection to use using the Primary Collection pull-down.

For every collection you select in Pane #1 a collection viewer tab is

created in Pane #2. A collection viewer allows you to view JSON documents and cursor through the documents. It also allows you to navigate to other applications within the context of the selected collection.

There are two sets of controls in Pane #2. On the left (in two lines) are controls affecting cursoring through the collection. On the right are navigation controls to other applications. Starting from the top left of Pane #2 in the image above the following control the cursor:

  • The location in the cursor is shown (in the image above the cursor is currently at location 10 and that there are 33,745 documents in the collection). The first ten documents are already shown in the JSON viewer hence the cursor is at location 10.
  • Click the Reset Cursor button to reset the cursor to the start of the collection.
  • Click on Next to move to the next document or next batch of documents (how many documents are brought back each time Next is pressed is controlled by the Show pull down).
  • The Skip pull down allows you to skip a number of documents and progress the cursor without reading documents. Select how many documents you want to skip, then click on Skip.
  • Click on the Skip cursor button to skip the selected number of documents.
  • Use the Show pull-down to specify how many documents to show in the JSON viewer beneath the toolbar.

On the right of the toolbar are controls that navigate to other applications using the context of the currently viewed document(s):

  • Set the left-hand-side of the Differ to the JSON being shown (see Working with differs).
  • Set the right-hand-side of the Differ to the JSON being shown (see Working with differs).
  • Navigate to the Schema Analyzer to view the schema of the selected collection (see Working with the Schema Analyzer).
  • Navigate to the Spreadsheet Bridge to use document data in Excel (see Working with the Spreadsheet Bridge).
  • Navigate to the Visualizer to visualize the data selected in the viewer (see Working with the Visualizer). Note that the Visualizer will show data from the selected number of documents controlled by the Show pull-down. Thus, if you want to view data from 100 documents, select 100 from the pull down and manage the cursor to the documents whose data you want to visualize.

Pane #3 is the main pane of the Finder and where you control your search/query. There are three methods to search for data – each supported by one of the three tabs on Pane #3. You can use a combination of the three - e.g. include some conditions using facets and some conditions using point-and-click.

Point-and-click

Use the point-and-click tab when you can point at the data that you want to search for in Pane #2 (or can point at data that looks similar and then edit it). If you double-click a data item (i.e. an element to the right of a ”:”) it adds a condition to the Additional where area. As an example, if you double click on the word “Chelsea” in Pane #2 of the image above, a “where condition” will be added as shown below:

_images/finder2.jpg

If you keep double clicking on values they are concatenated. Each such addition further limits what will return as the result of your search - i.e. these conditions are searched as an “AND”. However, if you double click on values for the same field (for example, in multiple documents) they are added as an “OR” as shown below:

_images/finder3.jpg

At any point in time feel free to manually edit the conditions if you want to use more complex operators, operands and conditions. To clear all conditions either manually select-all and clear the contents or click the clear button to the right of the text area.

You can choose among a set of the most common operators and the condition is added based on this operator. For example, click the EXISTS radio button and the condition added will take the form of:

<field name>: {$exists: true}

As you are adding conditions you are limiting which documents will be returned within the result set but all fields in the documents are returned. If you want to limit which fields are returned, use the Additional select area. If you Alt-double-click (hold down the Alt button and double click on a field or value in the JSON viewer of Pane #2), a selection condition is added to the Additional select area as shown below:

_images/finder4.jpg

To control sorting on the query add a condition to the Additional sort area using ctrl-double-click on Windows and command-double-click on Macs. In release 1.0 of the Studio the sorting condition is always added as a ”:1” meaning ascending. In version 1.1 you can select whether the sort condition will be ascending or descending (1 or -1).

Finally, if you’re ready to run the query hit the Execute search (play) button. When you hit the execute button the run-time is displayed and the Run-time Viewer logs the run-time. Use the clock icon to open the Run-time Viewer and view run-time information. Alternatively, you can click on the Preferences link and set the “Search on any change to facet/condition” to one as shown below. In this case, any change you make will immediately cause the search to be re-sent to the database and the result set to be shown.

_images/finder5.jpg

Note: If you have URLs in your data you can toggle the display to show them as hyperlinks. When you do so, all double-clicks are disabled. Toggle out of hyperlink mode to select.

Subqueries for $in, $nin and $all

While MongoDB itself does not support subqueries, the Studio performs a two-stage pass (and sometimes even a three-stage pass) to provide this functionality.

An example of a subquery is when you have a field F1 in a collection called COL1 and a field called F2 in a collection called COL2. You want to return all documents in COL1 where the value for F1 is one of all possible values of F2 in COL2. In SQL this would equate to a query such as:

SELECT * from COL1 where F1 in (SELECT DISTINCT F2 from COL2)

Such a query is possible in JSON Studio version 1.2 and up as show below.

_images/finder15.jpg

In MongoDB the $in, $nin and $all operators must be followed with an array. However, if you include a subdocument rather than an array in JSON Studio it will first run this as a separate query to produce an array and then run the original query transplanting the resulting array.

Two syntaxes are supported for a subquery array.

The first syntax specifies a collection, a query document and a projection field in dot notation, e.g.:

"F1" : {
   $in: {
      $ns: "COL2",
      $q:{"year": 2014},
      $p: "F2"
   }
}

In this case the Studio will first run a distinct on COL2, get all values for F2 and then create an array to query COL1.

The second syntax allows you to run an aggregation pipeline and use its results for populating the values for the main queries, e.g.:

"F1" : {
   $in: {
      $ns: "COL2",
      $pipeline:"pipeline1",
      $p: "_id"
   }
}

In this case the Studio will first run the aggregation, get all values using dot notation from the aggregation result, and then run the main query.

In all cases you will see both queries on the Run-time Viewer

Note that the generated query shown on the Query tab will be the one that MongoDB understands (i.e. after processing the subqueries as shown below):

_images/finder16.jpg

Note that subqueries are available in the aggregation builder application as well.

Result Sets

Use Pane #4 to view the resulting data. The result set is also viewed in a collection viewer and has similar controls to that of Pane #2. You can cursor through the results, show a number of documents etc.

Pane #4 maintains it’s size across sessions. When you resize this pane, it is persisted to the preferences collection so that the next time you use the Finder it has the same size.

If you are not running the Studio in read-only mode you can save the results into a collection in the database. Specify the collection name where to save the results into, determine whether or not you want to truncate the collection before inserting the values, whether to save all the result set or only what is currently showing, and whether you want to save or insert when using All. When you are only saving the showing documents, they are saved one by one and will overwrite a document with the same key if it exists. When you are saving all the result set this can be a lengthy operation - especially if you are traversing a slow network (e.g. your Studio is on your machine but the database is in the cloud). Therefore, you have two choices in terms of how to proceed. You can save the documents one at a time if you select the Upsert when all checkbox or batch the documents into insert operation (1000 at a time). The later option will be faster but you may get duplicate key errors.

Once you make all your selections click the Save button. This could be a long-running operation. You can cancel the operation using the cancel button of the control pane. See Canceling long running operations: for more information and limitations.

If you do not select All then you are saving the documents currently being viewed. If you try to click the save button twice you will get an error as shown below. Once you click on the save button and insert the documents you need to progress the cursor (or reset it) in order to save more documents. The only exception is when you select the All option since then a separate cursor is used to traverse the collection and save (or insert) all documents.

Important note: MongoDB has a 10 minute lifetime limit on cursors. This means that when you a query to populate another collection you should not use queries that will take more than 10 minutes to extract data from the cursor. Use a parameter-based range query and run multiple such calls if needed.

_images/finder13.jpg

Use last set of buttons to the right of the toolbar of Pane #4 to further work with documents of the result set:

  • Set the left-hand-side of the Differ to the JSON being shown (see Working with differs).
  • Set the right-hand-side of the Differ to the JSON being shown (see Working with differs).
  • Open a tabular display of the data (see image below). The number of rows and columns being shown is controlled through the Preferences. For example, if in the Preferences the number of rows to be shown are at most 200 then the tabular display will show 200 rows if they are available starting with the current position of the cursor (i.e. the first document currently shown). Each top-level field is included as a column. If a field is a sub-document then a JSON representation of the sub-document is shown. Note that for this tabular display to work you must be connected to the Internet since Google libraries are being used. If you are not connected and still need tabular data use the Spreadsheet Bridge (see Working with the Spreadsheet Bridge).
  • Navigate to the Spreadsheet Bridge to use document data in Excel (see Working with the Spreadsheet Bridge).
_images/finder14.jpg

Table Of Contents

Previous topic

Working with JSON Studio

Next topic

Working with the Aggregation Builder

Copyright © 2013-2016 jSonar, Inc
MongoDB is a registered trademark of MongoDB Inc. Excel is a trademark of Microsoft Inc. JSON Studio is a registered trademark of jSonar Inc. All trademarks and service marks are the property of their respective owners.