I’m currently diving into DynamoDB and I’m struggling with a conceptual problem. The problem, simplified in SQL terms, is as follows: I have a table with two columns. The first column contains guaranteed unique entries and should therefore ideally be used for the partition key. I want to perform a begins_with search on the two columns. The rows that meet the condition should be returned to me as a list.

In SQL language, the query would be something like this:

Search string: AAB

SELECT * FROM my_table WHERE column1 LIKE 'AAB%' OR column2 LIKE 'AAB%';

How should I structure my DynamoDB table (partition key? sort key? possibly additional GSI?) to implement this query (and thats the only query I will do on this table!) efficiently and without scanning?”

My current research told me I can not execute begins_with queries on the partition key. So this key is pretty useless for my usecase. And it also looks like I can not do a begins_with operation on the sort key without giving a pk.
So it looks like I need to specify two GSIs and set column1 and column2 as GSIPK on each of this indexes, to be able to do begins_with operations on that. And even then it looks like I need to execute two queries. One for the column1 and one for the column2.

In my opinion, it seems like I’m using DynamoDB incorrectly or at least in a way its not made for.

Is there a better way to achieve that?