Skip to main content

Manage MongoDB with SQL Console

Use NineData SQL Console to work with MongoDB data sources from a shared browser-based workspace. Browse databases and collections, edit documents in visual modes, run MongoDB commands, create indexes, and review execution results without switching between multiple tools.

Background​

MongoDB stores data as documents and is often used by applications that need flexible schemas, high throughput, or large volumes of semi-structured data.

NineData SQL Console combines a visual MongoDB workspace with a command-line editor. For teams, it also works with NineData controls such as permission management, audit logs, single sign-on (SSO), SQL development standards, and approval processes.

Feature overview​

SQL Console provides these capabilities for MongoDB data sources:

Feature
Description
Visual managementBrowse MongoDB databases and collections, then create, view, update, copy, clone, or delete documents from the visual interface.
Command editorRun supported MongoDB commands in a command-line editor and inspect the returned results.
Workspace recoveryReopen the SQL Console after a browser exit or crash and restore previously opened data sources and executed commands. The page state is stored in the cloud, so recovery is not tied to one browser or host.
Execution historyHistory records commands executed by the current account in the NineData console. Search by data source, execution time, command keyword, or other fields, then copy and rerun commands from history.
SQL standards checkingIn organizational modeOrganizational mode supports assigning different roles and resource management permissions to each user in the organization. It is suitable for collaborative development with multiple users within the same organization, ensuring data security and improving overall productivity., NineData checks every command executed in the SQL Console against the SQL development standards configured by Administrator. Commands that do not comply with the standards are blocked.

Use cases​

Use SQL Console for MongoDB to:

  • Inspect MongoDB databases, collections, documents, indexes, and views from the NineData console.
  • Edit documents during development, testing, troubleshooting, or controlled production maintenance.
  • Run MongoDB commands and keep command history available for review or reuse.
  • Apply team-level access controls, auditability, and approval processes to MongoDB operations.

Before you begin​

  • Add the target MongoDB data source to NineData. For details, see Manage data sources.

  • The data source must be MongoDB version 3.2 or higher.

  • In organizational mode (DevOps Enterprise), your account must have read-only, DML, or DDL permissions for the target data source.

    tip

    Read-only permission allows query operations only.

  • In the commercial versions (DevOps Pro, DevOps Enterprise), make sure your monthly or yearly subscription has not expired. An expired subscription may prevent you from using the Database DevOps service. Check the remaining quota and expiration date in the NineData Console header.

    check_quota

Procedure​

  1. Sign in to the NineData Console.

  2. Go to DevOps > SQL Console.

    tip

    If a data source session is still open, NineData redirects you to that data source page.

  3. Select the box under SQL Console. In the data source list, select the target MongoDB data source, and then click Query.

    tip
    If no Data Source exists yet, the page is empty. Click Create Datasource on the page.

    If you have many data sources, enter a full or partial keyword to search by:

    • Data source name
    • IP address

  4. After the SQL Console opens, use the visual editor or command-line editor to work with the MongoDB data source. For details, see Interface reference.

Result​

The SQL Console opens for the selected MongoDB data source. Browse databases and collections, switch between visual and command-line modes, edit documents, run commands, and inspect result sets.

Next steps​

Interface reference​

Visual editor​

mongo_interface

Number
Function
Description
1SQL Console TabDisplays the currently open data source SQL Console. Use the tabs to switch between SQL Consoles. Click X on a tab to close the SQL Console. The right-click menu supports these actions:
  • Instance Details: Open the current data source details window.
  • New Query: Open a new data source SQL Console.
  • Close Tab: Close the current tab.
2Open Data SourceChoose and open a new data source SQL Console.
3Data Source InformationDisplays the data source information of the current SQL Console, including environment, IP address, and port. Click the info icon on the right to view details of the current data source.
Right-click menu:
  • Instance Details: View detailed information about the current data source, including username, connection address, server version, etc.
  • Refresh: Refresh data source information.
4Favorite|Refresh
  • Favorite: Click the favorite icon to add the current data source to My Favorites for quick access when opening a new SQL Console.
  • Refresh: Refresh the data in the current data source.
5SearchEnter the collection name to quickly find the target collection. Supports searching by the complete collection name for precise search or by a partial collection name with an asterisk (*) for fuzzy search.
6Function Buttons
  • thread: View Threads Display information about active connections on the current database server and the queries running in those connections.
  • table: Visualize Switch to visualization mode.
  • codefill: Editor Switch to editor mode.
  • expend: Full Screen Display SQL Console in full-screen browser mode. In full-screen mode, click compress to restore the original SQL Console size.
7Database ListLists databases, collections, indexes, and related information in the current data source in a tree structure. Hover over the info icon next to a target database, collection, index, or view to display detailed information.
Common Right-Click Menu
  • New Query: Open a new SQL Console tab.
  • Refresh: Refresh the database list.
Database Right-Click Menu
  • Database Details: View detailed information about the target database, including the number of collections, views, indexes, documents, and the sizes of each object.
  • Create Database: Create a database. Enter both the database name and collection name when creating it.
  • Delete Database: Delete the target database.
  • Create Collection: Create a collection in the target database.
Collection Right-Click Menu
  • Create Collection: Create a collection in the target database.
  • Rename Collection: Rename the target collection.
  • Clear Collection: Clear all documents in the target collection.
  • Delete: Delete the target collection.
  • Create Index: Create an index in the target collection. For more information, see Create Index.
Index Right-Click Menu
  • Create Index: Create an index in the target collection. For more information, see Create Index.
  • Delete Index: Delete the target index.
View Right-Click Menu
  • Rename View: Rename the target view.
  • Delete View: Delete the target view.
8Select DatabaseVisually select the target database to operate on.
9View/Edit DataSelect a collection in the left-side database list to view its documents here.
Available actions include:
  • Search Documents: Search documents in the collection from the search box at the top of the page. Precise and fuzzy search are supported.
    • Precise search: {<field_name>:<value>}
    • Fuzzy search: {<field_name>:{$regex:<value>}}
    String values must be enclosed in double quotes ("").
  • Advanced: Click Advanced to add custom search conditions next to the search box. For details, see Advanced Search.
  • Execution Plan: Generate a MongoDB query execution plan based on the content in the search box. The execution plan describes how MongoDB executes the query and accesses data.
  • Add Document: Insert one or more documents into the current collection. During insertion, use the <field_name>:<value> format within curly braces ({}), and separate multiple fields with commas (,). When inserting a String value, wrap the value in double quotes (""), for example: {name:"zhangsan"}.
  • Switch Data Display: Click the display-mode icon to switch the data display in real time.
    • table: Show data in tabular form.
    • list02: Show data in list form.
    • json02: Show data in JSON format.
  • Edit Document: Edit documents in a visual format. For details, see Edit Documents.
  • Copy Document: Click ellipsis in the Actions column of the target document, and then click Copy (or click Copy directly in JSON mode) to copy the document as a JSON array.
  • Clone Document: Click ellipsis in the Actions column of the target document, and then click Clone (or click Clone directly in JSON mode) to clone a document identical to the target document.
  • Delete Document: Click ellipsis in the Actions column of the target document, and then click Delete (or click Delete directly in JSON mode) to delete the target document.
  • Refresh Page: Click Refresh in the page toolbar to refresh the page.
  • Delete: Click Delete in the page toolbar to delete the current collection.

Command-line editor​

mongo_command_line

Number
Function
Description
1SQL Console TabDisplays the currently open data source SQL Console. Use the tabs to switch between SQL Consoles. Click X on a tab to close the SQL Console. The right-click menu supports these actions:
  • Instance Details: Open the current data source details window.
  • New Query: Open a new data source SQL Console.
  • Close Tab: Close the current tab.
2Open Data SourceChoose and open a new data source SQL Console.
3Data Source InformationDisplays the data source information of the current SQL Console, including environment, IP address, and port. Click the info icon (info) on the right to view details of the current data source.
Right-click menu:
  • Instance Details: View detailed information about the current data source, including username, connection address, server version, etc.
  • Refresh: Refresh data source information.
4Favorite|Refresh
  • Favorite: Click the favorite icon to add the current data source to My Favorites for quick access when opening a new SQL Console.
  • Refresh: Refresh the data in the current data source.
5Select DatabaseVisually select the target database to operate on.
6Function Buttons
  • execute: Execute Execute the selected SQL statement.
  • plan: Execution Plan: View the execution plan of the selected SQL statement.
  • collect: Favorite this SQL: Save all commands in the current SQL Console to favorites for later use.
  • sqlfile: Favorites: View saved commands, insert the target command with Insert, or use Edit and Delete.
  • sqlhistory: History: View the command execution history of the target data source. Filter command execution history by time period, or search by keyword, such as command, database name, or error message.
  • setting02: Settings
    • Results: Choose Overwrite or Append.
      • Overwrite (default): The new result set overwrites the previous execution result.
      • Append: Open the new result set in a new tab.
    • Theme: Change the SQL Console theme. Choose Light or Dark.
    • Font Size: Drag the slider to adjust the font size of SQL statements in the SQL Console. Default values:
      • Windows: 16
      • MacOS: 13
  • thread: View Threads: Displays active connections on the current database server and the queries running in those connections.
  • admin: Enable Admin Mode|Disable Admin Mode Only Administrator can use this mode. When enabled, commands executed in SQL Console bypass standardization and approval flows.
  • table: Visualize Switch to visual mode.
  • codefill: Editor Switch to editor mode.
  • expend: Full Screen Open SQL Console in full-screen browser mode. In full-screen mode, click compress Exit to restore the original SQL Console size.
7SearchEnter the collection name to quickly find the target collection. Supports searching by the complete collection name for precise search or by a partial collection name with an asterisk (*) for fuzzy search.
8Database ListLists databases, collections, indexes, and related information in the current data source in a tree structure. Hover over the info icon (info) next to a target database, collection, index, or view to display detailed information.
Common Right-Click Menu
  • New Query: Open a new SQL Console tab.
  • Refresh: Refresh the database list.
Database Right-Click Menu
  • Database Details: View detailed information about the target database, including the number of collections, views, indexes, documents, and the sizes of each object.
  • Create Database: Create a database. Enter both the database name and collection name when creating it.
  • Delete Database: Delete the target database.
  • Create Collection: Create a collection in the target database.
Collection Right-Click Menu
  • Create Collection: Create a collection in the target database.
  • Rename Collection: Rename the target collection.
  • Clear Collection: Clear all documents in the target collection.
  • Delete: Delete the target collection.
  • Create Index: Create an index in the target collection. For more information, see Create Index.
Index Right-Click Menu
  • Create Index: Create an index in the target collection. For more information, see Create Index.
  • Delete Index: Delete the target index.
View Right-Click Menu
  • Rename View: Rename the target view.
  • Delete View: Delete the target view.
9Command Line WindowThe window for executing MongoDB commands. The command line window supports these features:
  • Execute MongoDB commands: Run a single command or multiple commands.
  • Syntax highlighting: Display different syntax elements in different colors for readability.
  • Interrupt execution: Click Stop Query to interrupt command execution.
  • Command auto-suggestions: Show suggestions based on command context as you type.
  • Right-click menu:
    • Execute: Execute the SQL statement at the cursor position.
    • Execution Plan: View the execution plan of the selected SQL statement.
    • Favorite this SQL: Save all commands in the current SQL Console to favorites for later use.
    • Favorites: View saved commands, insert the target command with Insert, or use Edit and Delete.
    • History: View command execution history for the target data source. Filter execution history by time period or search by keyword, such as SQL statement, database name, or error message.
    • View Threads: Display information about active connections on the current database server and the queries running in those connections.
    • General operations: Use Cut, Copy, Paste, and Select All.
    • Select Current SQL: Select the SQL statement at the cursor position.
10Execution Information, Result SetDisplays command execution information and result sets.
  • Execution Information: Includes execution time, executed command, execution status, execution details, and related fields.
  • Result Set: Displays the returned command results. Available actions:
    • Search the result set with Fuzzy or Exact.
    • Click the icons below to switch the result set display mode:
      • json02 (default): Display data in JSON format.
      • table: Display data in a table format.
      • list02: Display data in a list format.
    • In the JSON result set, click Copy to copy the entire result set.
    • Hover over a target result set tab to view the command for that result set.

SQL Console for MongoDB supports these advanced search parameters for collections:

  • Project: Controls projection for the returned documents. Return selected fields or calculated results, rename fields, add fields, remove fields, and customize the output structure. For example, {name:1} returns only the name field. For more information, see Aggregation Pipeline Optimization.
  • Sort: Sorts results in ascending or descending order by one or more fields. MongoDB can use indexes to improve sort performance. For example, {age:1} sorts by age in ascending order, and {age:-1} sorts by age in descending order. For more information, see Aggregation Pipeline Optimization.
  • Collation: Defines string comparison rules, such as case sensitivity, multilingual sorting, and locale-specific sorting. For example, {locale:"en"} sorts results by English collation rules. For more information, see Collation.
  • Skip: Skips a specified number of documents and returns the remaining documents. Use this option for pagination or to omit earlier results. For example, 10 skips the first 10 results and starts from the 11th result.
  • Limit: Limits the number of returned documents. For example, 10 returns only 10 results.

Edit documents​

Edit MongoDB documents visually in these modes:

  • table Table mode: In the Actions column of the target document, click Edit to edit field values directly. Also click tableedit next to a field, edit the value in the Value box, and click OK. After editing, click Submit at the bottom of the page.
  • list02 List mode: In the Actions column of the target document, click Edit. Then click tableedit next to the Value column, edit the value, change field names, or add fields, and click OK. After editing, click Submit at the bottom of the page.
  • json02 JSON mode: Edit the JSON string directly. Change field names and values or add fields. After editing, click Submit at the bottom of the page.

Create an index​

  1. In the database list on the left side of the SQL Console, find the target collection, right-click the collection, and click Create Index.

  2. Configure the index.

    Parameter
    		Description
    NameEnter the index name. Follow your index naming conventions.
    FieldSelect the field(s) to index. Click Add to add a row and index multiple fields.
    TypeSelect the index type. Supported types are:
    • Ascending Index: Sort the indexed field in ascending order.
    • Descending Index: Sort the indexed field in descending order.
    • Hashed Index: Hash the values of the indexed field and index by the hash value. This index is useful for quick searches in evenly distributed data but does not support range queries.
    • Text Index: Used for full-text searches on text data. Supports keyword searches and sorting on text fields.
    • 2dsphere Index: Used for storing geospatial data (longitude, latitude). Supports geospatial queries and indexing, such as distance calculation and range queries.
    • 2d Index: Used for storing geospatial data on a plane (x, y coordinates). Supports queries and indexing on the plane's geospatial data.
    • GeoHaystack Index: Used to optimize nearby geospatial location queries. Suitable for scenarios with a large amount of geospatial location data.
    Additional ConfigurationsChoose additional index configurations:
    • Background (selected by default): Specify whether to create the index in the background. For large collections, not selecting this option may block the instance.
    • Unique: Specify whether the index requires unique values. Selecting this option ensures that the values of the indexed field are unique in the collection.
    • Sparse: Specify whether to create a sparse index. When selected, the index is created only for documents that contain the target field; documents without this field are ignored. Using a sparse index can save index storage space.
    • Expire after: Used to create an index with a TTL (Time to Live). This option allows you to specify an automatic deletion time for documents in the collection, in seconds.
    PreviewDisplays the MongoDB commands used to create the index.

  3. Click OK.

Appendix: MongoDB command-line methods​

MethodSyntax
aggregate()db.<collection name>.aggregate()
bulkWrite()db.<collection name>.bulkWrite()
compactStructuredEncryptionData()db.<collection name>.compactStructuredEncryptionData()
countDocuments()db.<collection name>.countDocuments()
createIndex()db.<collection name>.createIndex()
createIndexes()db.<collection name>.createIndexes()
dataSize()db.<collection name>.dataSize()
deleteOne()db.<collection name>.deleteOne()
deleteMany()db.<collection name>.deleteMany()
distinct()db.<collection name>.distinct()
drop()db.<collection name>.drop()
dropIndex()db.<collection name>.dropIndex()
dropIndexes()db.<collection name>.dropIndexes()
estimatedDocumentCount()db.<collection name>.estimatedDocumentCount()
explain()db.<collection name>.explain()
find()db.<collection name>.find()
findAndModify()db.<collection name>.findAndModify()
findOne()db.<collection name>.findOne()
findOneAndDelete()db.<collection name>.findOneAndDelete()
findOneAndReplace()db.<collection name>.findOneAndReplace()
findOneAndUpdate()db.<collection name>.findOneAndUpdate()
getIndexes()db.<collection name>.getIndexes()
getShardDistribution()db.<collection name>.getShardDistribution()
getShardVersion()db.<collection name>.getShardVersion()
hideIndex()db.<collection name>.hideIndex()
insertOne()db.<collection name>.insertOne()
insertMany()db.<collection name>.insertMany()
isCapped()db.<collection name>.isCapped()
latencyStats()db.<collection name>.latencyStats()
mapReduce()db.<collection name>.mapReduce()
reIndex()db.<collection name>.reIndex()
remove()db.<collection name>.remove()
renameCollection()db.<collection name>.renameCollection()
replaceOne()db.<collection name>.replaceOne()
stats()db.<collection name>.stats()
storageSize()db.<collection name>.storageSize()
totalIndexSize()db.<collection name>.totalIndexSize()
totalSize()db.<collection name>.totalSize()
unhideIndex()db.<collection name>.unhideIndex()
updateOne()db.<collection name>.updateOne()
updateMany()db.<collection name>.updateMany()
validate()db.<collection name>.validate()