Chat now with support
Chat with Support

Our Community sites will be down for maintenance on November 14 from 10 AM to 6 PM Pacific Time. We apologize in advance for the inconvenience.

Toad Data Point 5.0 - User Guide

About MongoDB Data Sources

MongoDB is one of the NoSQL data sources supported by Toad Data Point. With Toad you can create a MongoDB connection, use the Query Builder or Editor to query data, and use the Database Explorer (Viewer) to view data and object details.

MongoDB is a document database. The database is comprised of collections, which are similar to tables. Collections contain documents, which are like rows or records. Also, collections can contain embedded documents. In Toad, each MongoDB collection is mapped to a table. In addition, each embedded document is mapped to a separate table.

Toad Automatically Maps Object Metadata

When you create a new connection to a Business Intelligence or NoSQL data source, Toad automatically maps the data source objects. Toad gathers metadata from these objects which is cached (in a hidden table) and retained between sessions for one week. After one week, all objects are automatically refreshed.

During this initial mapping process, Toad maps only metadata. The actual data in each column is mapped as needed, such as when you execute a query.

Manually refresh object metadata

You can manually refresh object metadata at any time. In the Object Explorer, right-click an object and select Refresh All.

To create a new MongoDB connection, see Create MongoDB Connections.

MongoDB Terminology

Review the following MongoDB terms. To learn more about the MongoDB database, see https://docs.mongodb.org.

Collection

A MongoDB collection is similar to a table. A collection is comprised of documents (rows).

It is represented as a table (parent table) in Toad.

Document A document is a row or record.
Embedded document

An embedded document is one or more embedded rows in a field. Any field can have one (and only one) embedded document.

An assemblage of all the embedded documents for one field (column) in one object is represented as a sub-table in Toad. This sub-table is treated as a child of the table in which the field (column) is located. A sub-table is named after the field that contains it.

Explore MongoDB Objects

MongoDB collections and embedded documents are represented as tables in Toad. In the Object Explorer, the table icon is used to identify both types of objects. Use the table name to distinguish embedded tables from collections.

Embedded Document Naming Format

  • Embedded documents use the following naming format.

    <collection>_<field containing embedded document>

  • When a collection contains nested embedded documents, the nested embedded document name is similar to the following:

    <collection>_<field containing embedded doc 1>_<doc 1 field containing embedded doc 2>

View Data in Related Tables

Use the Database Explorer (Viewer) to view data in related MongoDB objects. The Database Explorer provides a master-detail type view allowing you to see data in a parent table and then drill down to the data in the immediate sub-tables.

To view data in the Database Explorer

  1. Right-click a MongoDB collection (parent table) in the Object Explorer and select View Details.
  2. Select the Data tab.
  3. Click + beside each row to expose the embedded documents for that row, if any. This action displays the data in the embedded documents. Only one level of embedded document (only the immediate sub-table) is available to view from the parent table.

    Note: The actions available through the right-click menu in the data grid apply only to the parent table currently selected in the Object Explorer. The data in any embedded documents is not included in the action.

  4. To view the data in one of the sub-tables (which is a collection of the embedded documents for one field) in a single grid, switch to that sub-table in the Object Explorer. The data for the sub-table displays in the Database Explorer (Viewer) window. If the sub-table contains embedded documents, click + to expose the data.

  5. Select the Relationships tab at any time to view a diagram of the selected object with its related objects and their relationships.

View Object Relationships

To better view the data from a collection and its embedded documents, each of these items is mapped to a separate table in Toad. To create the relationships, a foreign key column is added to each embedded document (table) and is used to reference the parent object (table). This foreign key column is included whenever columns are displayed and is also used in diagram windows to show the relationship between the embedded document and its parent object.

You can view relationships in either the Database Diagram or the Relationship tab of the Database Explorer (Viewer).

To view relationships in a Database Diagram

  1. Select Tools | Diagram to open the Database Diagram.
  2. Drag a MongoDB table from the Object Explorer to the Database Diagram window. Select a Reference Depth to determine how many levels of related tables (embedded documents) to view in the diagram. Existing relationships display.

To view relationships in the Database Explorer (Viewer)

  1. Right-click a table in the Object Explorer and select View Details.
  2. Select the Relationships tab. Select a Reference Depth to determine how many levels of related tables (embedded documents) to view in the diagram. Existing relationships display.

Query MongoDB Data

To query data in a MongoDB database, use the Query Builder or the SQL Editor as you would with most other database connections. Review the following special considerations:

  • When you drag related tables to the Query Builder Diagram pane, the existing relationships are automatically added.

To query data using the Query Builder

  1. Select Tools | Query Builder | Query Builder to open a Query Builder window.
  2. Drag tables from the Object Explorer to the Diagram pane. Existing relationships display. If you remove a foreign key relationship, you can restore it by dragging the table to the Diagram pane again.

Considerations and Limitations

The following considerations and limitations apply when working with MongoDB data sources in Toad.

Consideration/Limitation Description
Embedded documents must have the same structure All embedded documents in one field (column) must have the same structure in order to be represented as a sub-table in Toad.

  

Related Topics

Related Documents