Docs Menu


On this page

  • Description
  • Usage
  • Command Line Options
  • Examples

mongotranslate is a learning tool designed to help users understand how SQL queries can be expressed in the MongoDB aggregation language. The BI Connector's SQL-to-aggregation translation engine provides the translations, which can also be used to troubleshoot specific BI Connector translation issues.

mongotranslate is a standalone program. It requires a drdl file generated by mongodrdl, but does not require mongosqld or mongod to be running at the time of execution.

mongotranslate has the following syntax:

mongotranslate [--query | --queryFile] <query | queryfile> [options]

Specifies a SQL query to translate into a MongoDB aggregation pipeline. Either --query or --queryFile is required.


Specifies a path to a file containing a SQL query to translate into a MongoDB aggregation pipeline. Either --query or --queryFile is required.


Required. Specifies a .drdl schema file or a directory containing one or more .drdl schema files created by the mongodrdl program to use when translating a SQL query into an aggregation pipeline.


Default: test

The database name to use for unqualified table names in the SQL query.

The following example uses a collection named fruit and the --dbName option to specify that fruit is in the groceries database:

mongotranslate "SELECT * FROM fruit WHERE _id > 100;" \
--schema schema.drdl --dbName groceries

If you do not use the --dbName option to specify a database, mongotranslate assumes that fruit is in the test database. If the schema does not contain a database named test, or a table name fruit in the test database, mongotranslate returns an error.

The following example uses a fully-qualified table name, so it does not need the --dbName option.

mongotranslate "SELECT * FROM groceries.fruit WHERE _id > 100;" \
--schema schema.drdl

If you specify a database with each table name in your SQL query, the --dbName option is ignored if it is used.


Optional. Returns the explain output for the query plan instead of the translated aggregation pipeline. Using --explain returns similar output to running EXPLAIN <query> on a BI tool connected to BI Connector.


Default: multiline

Optional. Valid options are none and multiline. The default multiline option displays results in a more easily readable format.

Command Type
Without the --explain option
--format none
Returns an aggregation pipeline all on one line.
Without the --explain option
--format multiline
Returns an aggregation pipeline with one pipeline stage per line.
With the --explain option
--format none
Returns all fields on one line.
With the --explain option
--format multiline
Returns one field per line, with additional formatting for arrays and object subfields.

Default: latest

Optional. Returns an aggregation pipeline compatible with the specified MongoDB version. Acceptable values include:

  • 3.2
  • 3.4
  • 3.6
  • 4.0
  • latest

The default value is latest.

The following example specifies an inline query for translation:

mongotranslate --query=“select from restaurants where name like 'Brooklyn%'” \

The above command returns the following results:

{"$match": {"name": {"$regex": "^Brooklyn.*$","$options": "i"}}},
{"$project": {"test_DOT_restaurants_DOT_name": "$name","_id": {"$numberInt":"0"}}},

If you need to quote a string inside the --query parameter, be sure to use single quotes. If you must use double quotes or backticks in your query, use the --queryFile option.

The following example specifies a query file and returns a one-line result:

mongotranslate --queryFile=query.txt --schema=schema.drdl --format=none

The following example uses the --explain option:

mongotranslate --query="select count(name) from restaurants;" \
--schema=schema.drdl --explain

The above command returns the following results:

"ID": 1,
"StageType": "MongoSourceStage",
"Columns": "[{name: 'count(name)', type: 'int'}]",
"Sources": null,
"Database": {},
"Tables": {},
"Aliases": {},
"Collections": {},
"Pipeline": {},
"PipelineExplain": {},
"PushdownFailures": null
Give Feedback
MongoDB logo
© 2021 MongoDB, Inc.


  • Careers
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.