How to return all records with a single AWS AppSync List Query?

Question

Hi all, my AWS AppSync list query is not returning all the records from DynamoDB table after applying a filter.

For context, I have a table with all major Cities with approximately 250k records. When I filter the table to show only cities in, for example, United Kingdom, the query does not return all records because AppSync will limit the scan operations to 10 items. If I then add a limit variable of 1,000 to the query I get less than 1,000 cities in the UK. It appears that the query first applies the limit before the filter and not the other way round for some reason...

I know I could use the `next token` as outline in [AppSync Query Returns Wrong Item Numbers](https://aws.amazon.com/premiumsupport/knowledge-center/appsync-wrong-query-item-number-dynamodb/) AWS Blog. However this means I'll have to perform multiple calls. **Is there a way to return all filtered cities with a single AppSync query?** Thanks in advance!

Accepted Answer

I thought of sharing my approach to this, I used a [Global Secondary Key (GSI)](https://docs.amplify.aws/cli/graphql/data-modeling/#configure-a-secondary-index) in the CITY table so I could query the cities by country_code (see below). I then used pagination to gather the remaining cities since the API does not return more than 1MB of data. I really like this video from YouTube that explains the [difference between Scan and Query](https://www.youtube.com/watch?v=U-yApJ2_FCE&ab_channel=BeABetterDev) for those that are just getting started.

```
type CITY @model {
id: ID!
country_code: String! @index(name: "byCountry", queryField: "listCitiesbyCountry")
name: String!
code: String!
state: String
...
}
```

How to return all records with a single AWS AppSync List Query?

Relevant content