How to query data in AWS AppSync in a specific range then sort its result by another key?

Question

I create a temple name BlogAuthor in AWS DynamoDB with following structure:

authorId | orgId | age |name

Later I need to make a query like this: get all authors from organization id = orgId123 with age between 30 and 50, then sort their name in alphabet order.

I'm not sure it's possible to perform such query in DynamoDB (later I'll apply it in AppSync), hence the first solution is to create an index (GSI) with partitionKey=orgId, sortKey=age (final name is orgId-age-index). But next, when try to query in DynamoDB, set partitionKey orgId=orgId123, sortKey age=[30;50] and no filter; then I can have a list of authors. However, there is no way to sort that list by name from above query.

I retry another solution by create new index with partitionKey=orgId and sortKey=name. Then, query (not scan) in DynamoDB with partitionKey orgId=orgId123, set empty sortKey value (because we only want to sort by name instead of getting a specific name), and filter age in range [30;50]. This solution seems works, however I notice the filter is applied on the result list - for example the result list with 100 items, but after apply filter by age, then may by 70 items remaining, or nothing. But I always hope it returns 100 items.

Could you please tell me is there anything wrong with my approaches? Or, is it possible to make such query in DynamoDB?

Another (small) question is when connect that table to an AppSync API: if it's not possible to perform such query, then it's not possible for such query in AppSync too?

Answer 1

You are not going to be able to do everything you want in a single DynamoDB query.

Option 1:

You can do what you want as long as you are ok with sorting objects on the client. This would work for organizations with a relatively small number of people.

Pros:

Allows you to efficiently query users in a particular organization between a range of users.

Cons:

Results are not sorted by name on the server.

Option 2:

Pros:

Allows you to paginate through users at an organization that are ordered by the name.

Cons:

You cannot efficiently get all users in an organization within an age range. You would effectively be scanning the index and would need multiple round trip calls.

Option 3:

A third option, would be to stream information from DynamoDB into ElasticSearch using DynamoDB streams and AWS Lambda. Once the data is in Elasticsearch, you can do much more advanced queries. You can see more information on the Elasticsearch search APIs here https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-body.html.

Pros:

Much more powerful query engine.

Cons:

More overhead w/ the DynamoDB stream and AWS Lambda function.