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.

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 => <array_path>,
 INDEX => <identifier>,
 OUTER => <bool>
)

Unwind 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
  },
  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.

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.