Query with Atlas SQL Statements

SELECT Statement

SELECT * FROM sessions;

Atlas SQL returns all documents from the Sessions collection.

LIMIT Statement

SELECT * FROM users LIMIT 2;

Atlas SQL returns two documents from the Users collection.

WHERE Statement

SELECT * FROM users WHERE name = 'Jon Snow';

Atlas SQL returns documents from the Users collection where the user's name is Jon Snow.

WITH Keyword

The WITH keyword makes it easier to create derived data sources for subsequent use.

WITH RecentSales AS (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM sales
  WHERE sale_date > '2025-01-01'
  GROUP BY customer_id
)
SELECT customer_id, total_sales
FROM RecentSales
WHERE total_sales > 1000;

Atlas SQL returns documents from the Sales collection that have customer_id with total sales greater than 1000 after January 1, 2025.

FLATTEN

FLATTEN flattens semi-structured data (name-value pairs in JSON) into separate columns. Field names become column names that hold all of the values for that field in rows.

The syntax for flattening nested documents is a FLATTEN function that can be used in the FROM clause in conjunction with a data source and options.

SELECT *
FROM FLATTEN(<data source>
 WITH DEPTH => <integer>,
 SEPARATOR => <string>
)

Flatten Example

In an example scenario, a customerInfo collection contains documents that are structured as follows:

{
  id: 1,
  location: "New York",
  customer: {
    age: 50,
    email: "customer@email.com",
    satisfaction: 5
  }
}

If you run the query SELECT * FROM customerInfo, Atlas SQL returns documents with the following top-level fields:

If you run the query SELECT * FROM FLATTEN(customerInfo), Atlas SQL returns documents with the following top-level fields:

When you use FLATTEN, each flattened field from the original document becomes a top-level field in the result set. Nested fields are concatenated with their parent field names and separated by the default delimiter, _.

UNWIND

UNWIND deconstructs an array field from the input data source to output one row for each item in that array. To learn more about unwinding, see the $unwind aggregation stage documentation.

The syntax for unwinding array fields is an UNWIND function that can be used in the FROM clause in conjunction with a data source and options.

SELECT *
FROM UNWIND(<data source>
 WITH PATH | PATHS => <array_paths>,
 INDEX => <identifier>,
 OUTER => <bool>
)

Unwind Example: Top Level Fields

In an example scenario, a customerInfo collection contains documents that are structured as follows:

{
  id: 1,
  location: "New York",
  customer: {
    age: 50,
    email: "customer@email.com",
    satisfaction: 5
  },
  visits: [
    {
      year: 2020,
      score: 10
    },
    {
      year: 2021,
      score: 8
    },
    {
      year: 2022
      score: 7
    }
  ]
}

If you run the query SELECT * FROM customerInfo, Atlas SQL returns documents with the following top-level fields:

If you run the query SELECT * FROM UNWIND(customerInfo WITH PATH => visits, INDEX => idx), Atlas SQL returns documents with the following top-level fields:

When you use UNWIND with PATH => visits, each visits object becomes a table row.

Unwind Example: Nested and Sibling Arrays

In an example scenario, a customerInfo collection contains documents that are structured as follows:

{
  "id": 1,
  "location": "New York",
  "customer": {
    "age": 50,
    "email": "customer@email.com",
    "satisfaction": 5
  },
  "visits": [
    {
      "year": 2020,
      "score": 10
    },
    {
      "year": 2021,
      "score": 8
    },
    {
      "year": 2022,
      "score": 7
    }
  ],
  "purchases": [
    {
      "transaction": "1A",
      "transactions": [
        {
          "amount": 100
        },
        {
          "amount": 150
        }
      ]
    },
    {
      "transaction": "2B",
      "transactions": [
        {
          "amount": 200
        }
      ]
    }
  ]
}

Suppose you run the following query:

SELECT v.year AS visit_year, v.score AS visit_score, p.transaction AS transaction_id, t.amount AS purchase_amount
FROM UNWIND(
  customerInfo WITH PATHS => (
    visits[INDEX => visit_idx],
    purchases[OUTER => TRUE, INDEX => purchase_idx].transactions[]
  ),
  INDEX => idx,
  OUTER => FALSE
)

The preceding query does the following:

• Unwinds the visits array and assigns an index name visit_idx to track the index position inside the unwound array.

• Unwinds the purchases array and unwinds the transactions array inside each purchases object. It also preserves any purchase with a missing or empty transactions array by using OUTER => TRUE.

Atlas SQL returns documents with the following fields:

In the results, each row contains data from a single visits entry and a single transactions entry.

Combined FLATTEN and UNWIND Example

The following example combines the FLATTEN and UNWIND functions.

In an example scenario, a customerInfo collection contains documents that are structured as follows:

{
  id: 1,
  location: "New York",
  customer: {
    age: 50,
    email: "customer@email.com",
    satisfaction: 5
  },
  visits: [
    {
      year: 2020,
      score: 10
    },
    {
      year: 2021,
      score: 8
    },
    {
      year: 2022
      score: 7
    }
  ]
}

If you run the query SELECT * FROM customerInfo, Atlas SQL returns documents with the following top-level fields:

If you run the query Select * from FLATTEN(UNWIND(customerInfo WITH PATH => visits, INDEX => idx)), Atlas SQL returns documents with the following top-level fields:

When you use both the FLATTEN and UNWIND functions, the visits array is unwound, and the resulting document is then flattened.