[Atlas SQL] Unwind a portion of a collection

Working with Data Connectors & Integrations
,
1

Example Document

The following is an example of the documents that I’m working with in my collection

{
  "product_id": {
    "$numberLong": "4"
  },
  "timestamps": [
    {
      "$date": "2025-01-28T14:50:26.706Z"
    },
    {
      "$date": "2025-01-28T22:49:24.845Z"
    }
  ],
  "prices": [
    10.75,
    10.75
  ],
  "stock_counts": [
    24,
    24
  ],
  "cumulative_sale_amounts": [
    0,
    0,
  ],
  "amount_sold": {
    "$numberLong": "0"
  },
}

Background

I’m working with Atlas SQL and Tableau and I have an aggregation that I would like to translate into an Atlas SQL query but have struggled to get it to work. The aggregation is as follows (a sentence explaining the aggregation is provided right after):

[
  // Sort in descending order according to the amount sold.
  {
    $sort: {
      amount_sold: -1
    }
  },
  // Limit to 10 items - this means that now we have the 10
  // most sold items
  {
    $limit: 10
  },
  // Zip and then unwind the various "measurements" so that
  // we can diagram this out.
  {
    $project: {
      product_name: 1,
      points: {
        $zip: {
          inputs: [
            "$timestamps",
            "$prices",
            "$stock_counts",
            "$cumulative_sale_amounts"
          ]
        }
      }
    }
  },
  {
    $unwind: {
      path: "$points",
    }
  },
  {
    $project: {
      product_id: 1,
      timestamp: { $arrayElemAt: ["$points", 0] },
      price: { $arrayElemAt: ["$points", 1] },
      stock_counts: { $arrayElemAt: ["$points", 2] },
      cumulative_sale_amount: { $arrayElemAt: ["$points", 3] },
    }
  }
]

This aggregation gets the 10 most sold products from the database and then does a Zip+Unwind for the timestamps, prices, stock_counts, cumulative_sale_amounts. The result of this is expected to be X entries for 10 products where X is the total amount of timeseries data that we have.

I’ve been trying to translate the above into Atlas SQL, and have failed to do it and would appreciate any help I can get in this Atlas SQL query.

Failed Attempts

Below is some of my failed attempts at translating this to Atlas SQL which kind of highlights the issue that I’m running into. Please pretend as if the UNWIND(derived_product_timeseries) is the complete unwind statement. I do not want to repeat it so I’m opting to use “pseudo” syntax

I first started with the following simple query:

SELECT *
FROM UNWIND(derived_product_timeseries WITH path => keepingSnippetSimple)
ORDER BY amount_sold DESC
LIMIT 10

But this doesn’t work for me for two reasons:

  1. It performs an UNWIND over the whole table, which is pretty big, so it’s quite inefficient.
  2. It doesn’t give me the thing that I was looking for which is the top 10 products sold. Rather, it gives me just 10 data points which is not quite what I’m looking for.
  3. I could use a WHERE clause here to get it to do what I want but it would still be a full table unwind which is pretty inefficient in my case.

This took me down a rabbit hole of trying to have an SQL expression or a “data source” inside of the unwind. Syntax correctness aside, the following is conceptually what I’m trying to achieve:

SELECT *
FROM UNWIND(
    (
        SELECT * 
        FROM derived_product_timeseries
        ORDER BY amount_sold DESC
        LIMIT 10
    ) 
    WITH path => keepingSnippetSimple
)

The above, as well as various variations of it that included aliases in various positions did not work

Question

How can I unwind just a portion of a collection/table in Atlas SQL in a way similar to the aggregation provided above?