Can we query first n rows from DynamoDB using PartiQL?

Question

I'm trying to fetch first 5 rows from DynamoDB using PartiQL but couldnot find any syntax or maybe I'm missing something on that.
My requirement is to only use it through PartiQL and not by any document model or client library.

Accepted Answer

To suit your needs you can use the `Limit` parameter for PartiQL where you can limit the results to *n* rows:

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_ExecuteStatement.html#DDB-ExecuteStatement-request-Limit

JS Example:
```
const params = {
   "Limit": 3,
   "Statement": "SELECT * FROM mytable"
}
```

```
const result = client.executeStatement(params).promise()
```

Answer

As of writing, PartiQL does not support a "LIMIT" clause. But if you'd like to use PartiQL only, you could run a """SELECT * FROM """"" that would return up to 1MB worth of items and then pick the first 5. This may not be super efficient until a LIMIT clause is supported.

PartiQL SELECT - https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html

Even when using PartiQL, you are directly interacting with DynamoDB via an AWS SDK (if not using the AWS Console). Using a Scan API on the SDK does support a LIMIT parameter which can be efficient as it would only return 5 items. Note that the behavior in terms of what items are returned are going to be same for the above mentioned PartiQL query or the Scan API.

If you use a WHERE clause in your PartiQL query with the table's partition key as a filter, then you'd get the results for that partition key sorted by the table's Sort key value (if the table has a Sort key). The non-PartiQL equivalent of this operation would be a Query API.

Query API - https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html

Scan API - https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Scan.html

Any of the ways listed above would return a maximum of 1MB worth of items. For Query/Scan APIs, there is a LIMIT parameter supported which can help be efficient in only returning the number of items you need. Hope this helps!

Can we query first n rows from DynamoDB using PartiQL?

Relevant content