Setup

To setup a MongoDB connection, you need to at least provide a Connection String and a DB Name.

Connection String

• The simplest: mongodb://username:password@hostname:port/dbname

• With SSL enabled: mongodb://username:password@hostname:port/dbname?ssl=true

• With SSL enabled and self-signed certificates (disables certificate verification): mongodb://username:password@hostname:port/dbname?ssl=true&ssl_cert_reqs=CERT_NONE

If needed, you can pass additional connection options in the query string. See full details in [MongoDB’s documentation](https://docs.mongodb.com/manual/reference/connection-string /#connection-options).

You might notice that there is a separate field for the DB Name in the data source configuration and we also include it in the connection string. This is usually required on shared hosts like MLab.

MongoDB Atlas

We’ve had issues with users connecting to MongoDB Atlas free tier accounts because they are on a shared environment. For best results, use a connection string of the format:

mongodb+srv://<user>:<password>@<subdomain>.mongodb.net/<database>?retryWrites=true

Troubleshooting

Error: “SSL handshake failed: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed”

This usually happens when your MongoDB server is using self signed certificates. You can either switch to a properly signed certificate or just add the ssl_cert_reqs=CERT_NONE option to your Connection String.

Write your MongoDB query as a JSON object. During execution, Redash will convert it into either a db.collection.find() call or a db.collection.aggregate() call. Here’s how your JSON object is mapped and sent to MongoDB:

The values you use for each key are passed unmodified as as parameters to MongoDB.

Querying

Query Examples

Simple Query Example

{
	"collection": "my_collection",
	"query": {
		"type": 1
	},
	"fields": {
		"_id": 1,
		"name": 2
	},
	"sort": [{
		"name": "date",
		"direction": -1
	}]
}

An equivalent query in Javascript would be written: db.my_collection.find({"type": 1}, {"_id": 1, "name": 2}).sort([{"name": "date","direction": -1}])

Count Query Example

{
	"collection": "my_collection",
	"count": true
}

Aggregation

Aggregation uses a syntax similar to the one used in PyMongo. However, to support the correct order of sorting, it uses a regular list for the “$sort” operation that converts into a SON (sorted dictionary) object before execution.

Aggregation query example:

{
	"collection": "things",
	"aggregate": [{
		"$unwind": "$tags"
	}, {
		"$group": {
			"_id": "$tags",
			"count": {
				"$sum": 1
			}
		}
	}, {
		"$sort": [{
			"name": "count",
			"direction": -1
		}, {
			"name": "_id",
			"direction": -1
		}]
	}]
}

MongoDB Extended JSON Support

We support MongoDB Extended JSON along with our own extension - $humanTime:

{
	"collection": "date_test",
	"query": {
		"lastModified": {
			"$gt": {
				"$humanTime": "3 years ago"
			}
		}
	},
	"limit": 100
}

It accepts a human-readable string like the above (“3 years ago”, “yesterday”, etc) or timestamps.

MongoDB Filtering

You can add filters to Mongo queries by projecting a column with the ‘::filter’ keyword added on to the end.

{
	"collection": "zipcodes",
	"aggregate": [{
		"$project": {
			"_id": "$_id",
			"city": "$city",
			"loc": "$loc",
			"pop": "$pop",
			"state::filter": "$state"
		}
	}]
}

The above example will show a ‘State’ column, and allow you to filter on this column.

Troubleshooting

Sort exceeded memory limit of 104857600 bytes

Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.

In MongoDB, the in-memory sorting have a limit of 100M, to perform a large sort, you need enable allowDiskUse option to write data to a temporary files for sorting.

To enable the allowDiskUse option, just add the option to your query:

{
  ...
  "allowDiskUse": true
}