Query DynamoDB with multiple begins_with clause in AppSync

Question

I'm currently trying to create a dynamic query using AppSync and Apache Velocity Template Language (VTL).

I want to evaluate series of begins_with with "OR"

Such as:

{
    "operation": "Query",
    "query": {
        "expression": "pk = :pk and (begins_with(sk,:sk) or begins_with(sk, :sk1)",
        "expressionValues": {
      ":pk": { "S": "tenant:${context.args.tenantId}",
      ":sk": {"S": "my-sort-key-${context.args.evidenceId[0]}"},
      ":sk1": {"S": "my-sort-key-${context.args.evidenceId[1]}"}

   }

    }

But that isn't working. I've also tried using | instead of or but it hasn't worked either. I get:

Invalid KeyConditionExpression: Syntax error; token: "|", near: ") | begins_with" (Service: AmazonDynamoDBv2;

How can I achieve this using VTL?

Populate

Answer 1

Original answer

you're missing a closing parenthesis after the begins_with(sk, :sk1). That is, the third line should be:

        "expression": "pk = :pk and (begins_with(sk,:sk) or begins_with(sk, :sk1))"

I just ran the fixed expression and it worked as expected.

Revised

Actually, there are subtleties.

the or operator can be used in filter-expression but not in key-condition-expressions. For instance, a = :v1 and (b = :v2 or b = :v3) will work as long as a and b are "regular" attributes. If a and b are the table's primary key (partition key, sort key) then DDB will reject the query.

Gallnut

Thanks, Itay for the answer 😊... did you run in using a text context or did you try running it against GraphQL? I have been checking and I think this is indeed not possible. – Populate

I missed the fact the pk and sk in your table are the partition and sort key of the table. please see my revised answer. – Gallnut

That's exactly what I found! 😊 Thanks for corroborating. – Populate

Answer 2

Reading this answer seems that this isn't possible, as DynamoDB only accepts a single Sort key value and a single operation.

There's also no "OR" condition in the operation: https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html#DDB-Query-request-KeyConditionExpression

If you also want to provide a condition for the sort key, it must be combined using AND with the condition for the sort key. Following is an example, using the = comparison operator for the sort key:

I am going to be restructuring the access pattern to better match my request.

Populate