MongoDB

Note: This driver is available in Lite, Enterprise, Ultimate and Team editions only.

Overview

This guide provides instructions on how to set up and use MongoDB with DBeaver.

One of the standout features of DBeaver's MongoDB support is its flexibility in presentation. You can view MongoDB collections as standard relational tables, JSON documents, or even in chart presentations.

DBeaver interacts with MongoDB servers using a specific driver, supporting versions from 2.x to the current version. DBeaver also supports MongoDB extension such as CosmosDB, you can find more information about this driver in our article.

Before you start, you must create a connection in DBeaver and select MongoDB. If you have not done this, please refer to our Database Connection article.

Setting Up

This section provides an overview of DBeaver's settings for establishing a direct connection and the configuration of secure connections using SSH, proxies and SSL.

MongoDB connection settings

In this subsection, we will outline the settings for establishing a direct connection to a MongoDB database using DBeaver. Correctly configuring your connection ensures seamless interaction between DBeaver and your MongoDB database.

The page of the connection settings requires you to fill in specific fields to establish the initial connection.

Field	Description
Connect by (Host/URL)	Choose whether you want to connect using a host or a URL.
URL	If you are connecting via URL, enter the URL of your MongoDB database here. This field is disabled if you're connecting via the host.
Host	If you are connecting via host, enter the host address of your MongoDB database here.
Database	Enter the name of the MongoDB database you want to connect to.
Replica Set	Specify the name of the replica set if your MongoDB instance is a part of a replica set configuration.
Port	Enter the port number for your MongoDB database. The default MongoDB port is 27017.
Authentication	Choose the type of authentication you want to use for the connection. For detailed guides on authentication types, please refer to the following articles: - MongoDB Authentication - DBeaver Profile Authentication You can also read about security in DBeaver PRO.
Connection Details	Provide if necessary.
Driver Name	This field will be auto-filled based on your selected driver type.
Driver Settings	If there are any , configure them here.

Connection details

The Connection Details section in DBeaver allows you to customize your experience while working with MongoDB database. This includes options for adjusting the Navigator View, setting up Security measures, applying Filters, configuring Connection Initialization settings, and setting up Shell Commands. Each of these settings can significantly impact your database operations and workflow. For detailed guides on these settings, please refer to the following articles:

• Connection Details Configuration
• Database Navigator
• Security Settings Guide
• Filters Settings Guide
• Connection Initialization Settings Guide

• Shell Commands Guide

MongoDB driver properties

The settings for MongoDB Driver properties enable you to adjust the performance of the MongoDB JDBC driver. These adjustments can influence the efficiency, compatibility, and features of your MongoDB database.

For a complete walkthrough on setting up MongoDB JDBC driver properties, you can refer to the official MongoDB JDBC documentation. This guide detail driver's properties and how they can be used to optimize MongoDB database connections.

You can customize the MongoDB driver in DBeaver via the Edit Driver page, accessible by clicking on the Driver Settings button on the first page of the driver settings. This page offers a range of settings that can influence your MongoDB database connections. For a comprehensive guide on these settings, please refer to our Database drivers article.

Secure Connection Configurations

DBeaver supports secure connections to your MongoDB database. Guidance on configuring such connections, specifically SSH, Proxy, and SSL connections, can be found in various referenced articles. For a comprehensive understanding, please refer to these articles:

• SSH Configuration.

• Proxy Configuration.

• SSL Configuration.

• Kubernetes Configuration

Powering MongoDB with DBeaver

DBeaver provides a host of features designed for MongoDB databases. This includes the ability to view and manage collections, along with numerous unique capabilities aimed at optimizing database operations.

MongoDB database objects

DBeaver lets you view and manipulate a wide range of MongoDB database objects. DBeaver has extensive support for various MongoDB metadata types, allowing you to interact with a wide variety of database objects, such as:

• Databases
  • Collections
  • Java Script
  • Users

• Administration

  • Active Operations

MongoDB Features in DBeaver

DBeaver is not confined to handling typical SQL tasks. It also embraces the NoSQL database spectrum, offering numerous unique features specifically designed for MongoDB. Beyond standard SQL operations, DBeaver facilitates a plethora of MongoDB-specific capabilities, such as:

Category	Feature
Data Types	BSON Data Types (e.g., Object ID, ISODate)
Security	User-Based Access Control
Database Management	MongoDB Stored Procedures (JavaScript Functions)

Additional features compatible with MongoDB, but not exclusive to it:

Category	Feature
Data Transfer	Data Import
	Data Export
Session Management	Session Manager
Schema Management	Schema Compare

Browsing MongoDB collections

You can view or edit MongoDB collection content as standard relational tables (in grid/plain text presentations/chart) or as JSON documents. You can switch between these presentations using the toolbar of the Data Editor.

Database operations

Executing JavaScript

Execute JavaScript statements in the SQL editor as usual. DBeaver supports all JavaScript queries for MongoDB versions 2 and 3, as well as a subset of the mongo shell queries.

Here is an example that creates a user in the current database:

db.createUser({
    user: 'testuser',
    pwd: 'test',
    roles: []
})

This example returns all documents in the collection 'test_col':

db.test_col.find().toArray()

Note: Scripts will be executed in the current database. You can not set an explicit database name in your query. The current database can be changed on the SQL Editor toolbar or on the Database Navigator.

Executing SQL

You can use standard SQL statements (SELECT, INSERT, UPDATE, DELETE) to manipulate data in MongoDB.

SELECT queries

SELECT queries can include WHERE, ORDER BY, GROUP BY, JOIN and HAVING clauses.

SELECT * FROM test_col 
WHERE propName.subProp='value'

UPDATE FROM test_col 
SET propsName.val1=123
WHERE propName.subProp='value'

Note: The MongoDB dialect does not support SQL sub-queries.

Conditions

SELECT queries with WHERE clauses support AND, OR, <, <=, >, >=, = and != operators:

SELECT * FROM Employees
WHERE (Country = 'CA' OR Country = 'RU') AND Age > 20;

TIP: Be aware that AND has a higher precedence than OR and will be evaluated first; enclose it with parentheses to maintain the correct order.

Nested fields

You can differentiate nested JSON fields using a dot. Enclose fields containing special characters (like spaces or dashes) with double quotes, as demonstrated below:

SELECT title FROM movies WHERE info."imdb-details".rating > 6

Working with object IDs

To find a document by ID, use the ObjectId function:

SELECT * FROM documents
WHERE _id = ObjectId('5f9c458018e3c69d0adc0fbd')
ORDER BY value DESC

Working with JOINs

The SQL dialect for MongoDB supports LEFT JOIN and INNER JOIN currently:

SELECT
    ar.Name as Artist,
    al.Title as Album,
    SUM(tr.Milliseconds) as Duration
FROM Track tr
INNER JOIN Album al ON tr.AlbumId = al.AlbumId
INNER JOIN Artist ar ON al.ArtistId = ar.ArtistId
GROUP BY Artist, Album
ORDER BY Duration DESC

Remember to specify aliases for both the source and target tables in a defined order, as shown:

SELECT *
FROM <source> <source-alias>
INNER JOIN <target> <target-alias> ON <source-alias>.column = <target-alias>.column

Executing the script below does not yield a merged document but produces separate documents for Track and Album:

SELECT *
FROM Track tr
INNER JOIN Album al ON tr.AlbumId = al.AlbumId

Aggregate functions

In version 22.x only COUNT function is supported.

INSERT statement

You cannot use conditions in INSERT statements, only the basic form is supported:

    INSERT INTO <collection-name> (field1, field2) VALUES (val1, val2);

UPDATE statement

While you can use various expressions in the WHERE clause, sub-selects or joins are not permissible.

    UPDATE <collection-name> SET field2=val3 WHERE field1=val1;

DELETE statement

You may use any expression in the WHERE clause, but sub-selects or joins are not allowed.

    DELETE FROM <collection-name> WHERE field1=val1;

CREATE TABLE statement

In the CREATE TABLE statement, only the collection name can be specified, column lists are not allowed.

    CREATE TABLE  <collection-name>;

DROP TABLE statement

    DROP TABLE  <collection-name>;

Working with dates

When working with dates, ensure to specify them in ISO format. This is applicable in both JavaScript and SQL dialects:

db.dates.insert([
    { value: new Date('2016-05-18T16:00:00Z') },
    { value: new Date('2017-05-18T16:00:00Z') },
    { value: new Date('2018-05-18T16:00:00Z') },
    { value: new Date('2019-05-18T16:00:00Z') },
    { value: new Date('2020-05-18T16:00:00Z') }	
])

To query data in JavaScript, follow this example:

db.dates.find({
    value: { $gte: new Date('2018-05-18T16:00:00Z') }
}).toArray()

When querying data in the SQL dialect, you can use either the ISO format or UNIX timestamp (in milliseconds):

SELECT value FROM dates
WHERE value > ISODate('2018-05-18T16:00:00.000Z')
ORDER BY value DESC

SELECT value FROM dates
WHERE value > ISODate(1526659200000)
ORDER BY value DESC