MongoDB

MongoDB Datasource Connector Details

Overview

MongoDB is a document database and does not have a direct SQL interface. ChartSQL uses a local SQLite database to convert documents into SQL tables that ChartSQL can use for charting.

See SQLite datasource documentation for working with SQLite's flavor of SQL

Installing MongoDB

Install a MongoDB datasource in ChartSQL studio following the typical datasource instructions

Understanding MongoDB Charts

ChartSQL visualizations for MongoDB have a two step process:

1. Write a MongoDB collection or aggregation pipeline query

2. Shape the final data with SQL

The ChartSQL Editor has an additional tab, "MongoDB Query" for writing the Mongo collection query

Screenshot of the MongoDB collection query editor

Write a MongoDB Collection Query

To query a MongoDB colleciton, you will specify a collection, and a find structure, which maps to a mongodb.collection.find method call

When find() is executed against the collection, the projected result set will be stored in a local SQLite database for further querying. The name of the table is the same as the collection.

Example MongoDB Collection Data

You should not find* an entire collection, because it will necessarily be copied to your local database. Match and limit the items of your collection to just those you need to visualize

{
	"collection":"sales",
	"find":{
		"match":{
			"Status":"Lost" 
		},
		"projection":{
			"Amount":1,
			"DateClosed":1,
			"Name":1
		},
		"sort":{
			
		},
		"limit":10,
		"skip":0
	}
}

Shape the Final Data with SQL

The result of find() is transferred to a local SQLite database. You can then query the result with SQL.

Select all records returned from the mongodb find:

-- @chart: column
-- @mongodb-query: {"collection":"sales","find":{"match":{"Status":"Lost" },"projection":{"Amount":1,"DateClosed":1,"Name":1},"sort":{},"limit":0,"skip":0}}
SELECT *
FROM sales

If necessary you can further shape the data with SQL:

-- @chart: column
-- @mongodb-query: {"collection":"sales","find":{"match":{"Status":"Lost" },"projection":{"Amount":1,"DateClosed":1,"Name":1},"sort":{},"limit":0,"skip":0}}
SELECT
TRUNC(DateClosed, 'MONTH') AS DateClosed,
sum(Amount) as Amount
FROM sales
GROUP BY TRUNC(DateClosed, 'MONTH')
ORDER BY TRUNC(DateClosed, 'MONTH') asc;

ChartSQL SQL Dialect

ChartSQL uses SQLite as the local database to query collections. See the SQLite datasource for syntax to query with.

MongoDB Atlas SQL

MongoDB has a new SQL interface. However, as of 1/14/2024 we could not get it to work reliably, and it is not very documented. In the future, we may be able to provide direct support for MongoDB Atlas SQL.