Navigation

$sql

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 a SQL query of the data in a collection. It must be the first stage in the pipeline.

Syntax

{
  $sql: {
    statement: "<SQL-statement>",
    format: "jdbc",
    formatVersion: 1,
    dialect: "mysql"
  }
}

Fields

FieldTypeDescriptionNecessity
dialectstringSQL dialect used by the statement. Value must be mysql.Required
formatstringStructure of the output documents. Value must be jdbc.Required
formatVersionintVersion of the requested schema format. If omitted, defaults to the latest version of the requested schema format.Optional
statementstringSQL 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.

If the SQL statement is a query, the $sql stage returns one document per row in the SQL result set. Each document includes a single array field named values that contains documents representing the columns in the result set and their values. For example:

{
  "values": [
    {
      "database": "<databaseName>",
      "table": "<tableName>",
      "tableAlias": "<tableAlias>",
      "column": "<columnName>",
      "columnAlias": "<columnAlias>",
      "bsonType": "<bsonType>",
      "value": "<columnValue>"
    },
    ...
  ]
}
FieldTypeDescription
databasestringName of the database. For queries against DUAL, the field has a null value.
tablestringName of the table. For computed columns, the field has a null value.
tableAliasstringAlias for the table. If the query provides no alias, value is the same as the table name. For computed columns, the field has a null value.
columnstringName of the column. For computed columns, the field has a null value.
columnAliasstringAlias for the column. If the query provides no alias, value is the same as the column name. For computed columns, the field contains the column name.
bsonTypestringThe type of value. See Supported BSON Types for more information.
valuestringThe column value.

If the result set of the query is empty, the output includes a field emptyResultSet whose value is true. The values field has all of the column metadata, but the value field value is null. For example:

{
  "emptyResultSet": true,
  "values": [
    {
      "database": "<databaseName>",
      "table": "<tableName>",
      "tableAlias": "<tableAlias>",
      "column": "<columnName>",
      "columnAlias": "<columnAlias>",
      "bsonType": "<bsonType>",
      "value": "null"
    },
    ...
  ]
}

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: "mysql",
  }
}