The Atlas SQL interface lets SQL-based tools query and analyze MongoDB Atlas data without first moving it through an extract, transform, load (ETL) process. It uses Atlas Data Federation as its query engine and mongosql, an SQL-92–compatible dialect designed for the document model. The interface is read-only: queries cannot write back to an Atlas cluster. section 18.0 section 18.1
A federated database instance exposes virtual databases and collections mapped to underlying data stores. Atlas Data Federation samples collections or views to generate JSON schemas for SQL compilation and type inference, including nested fields. This makes document data accessible to SQL tools, but querying the federated instance incurs data transfer charges. section 18.1
To get started:
- In the Atlas UI, open the cluster’s Connect options, select Atlas SQL, and use Quick Start to create a data federation and activate the interface. Alternatively, configure a federated database instance. Enabling Atlas SQL is free. section 18.2 section 18.2.1
- Copy the connection string for your cluster. Connect with mongosh using TLS and a username, or choose a recommended Power BI, Tableau, JDBC, or ODBC connector or driver. section 18.2.2
- In mongosh, issue SQL through the $sql aggregation pipeline stage or the shorter db.sql() method. Treat db.sql() as experimental and subject to change. section 18.3 section 18.4
Query an array as rows
For an accounts collection with a products array, `db.sql(`SELECT account_id, products AS product FROM UNWIND(accounts WITH PATH => products) WHERE account_id = 198100`)` returns one row per product for that account. UNWIND is useful when an array needs a relational-style result; FLATTEN instead turns nested document fields into columns. section 18.3.3
db.sql(` SELECT account_id, products AS product FROM UNWIND(accounts WITH PATH => products) WHERE account_id = 198100 `); Note: this is an experimental feature that ➥may be subject to ➥change in future releases. [ { '': { account_id: 198100, product: 'Derivatives' } }, { '': { account_id: 198100, product: 'CurrencyService' } }, { '': { account_id: 198100, product: 'InvestmentStock' } } ]
Inspect the complete UNWIND query and its three returned product rows. section 18.3.3
