`$sql`

On this page

Syntax

Fields
Output
Example

Note

Beta

The support for SQL format queries is available as a Beta feature. The feature and the corresponding documentation may change at any time during the Beta stage.

$sql processes an SQL query of the data in a collection. The $sql stage:

Must be the first stage in the pipeline.
Supports SELECT and UNION statements only.

Use this stage for read-only queries.

Syntax

{
  $sql: {
    statement: "<SQL-statement>",
    format: "jdbc",
    formatVersion: <version-number>,
    dialect: "mongosql"
  }
}

Fields

Field	Type	Description	Necessity
`dialect`	string	SQL dialect used by the statement. Value must be `mongosql`.	Required
`format`	string	Structure of the output documents. Value must be `jdbc`.	Required
`formatVersion`	int	Version of the requested output format. Atlas Data Federation supports the following versions for the jdbc output format: `1` `2` (latest) If omitted, defaults to the latest version of the requested output format.	Optional
`statement`	string	SQL query or command to run.	Required

Output

If the SQL statement is not a query or a command that returns a result set, the $sql stage does not return any documents.

The output differs based on the format and format version used in the query.

Example

The following example shows the $sql syntax for querying a sampleDB.egData collection:

{
  $sql: {
    statement: "select * from egData limit 2",
    format: "jdbc",
    formatVersion: 1,
    dialect: "mongosql",
  }
}

← Retrieve Federated Database Instance Query History Data Federation Limitations →