How to query dynamoDB based on key + attribute?

Question

I am using appsync resolvers with dynamoDB. I have a table that contains some job items records, each job spans a set of items and each of those items is a task that has a specific status: ["pending", "succeed", "failed"].

I used on my table "id" as primary key and "jobId" as secondary index key.

I would like to query my table to find all items related to a given "jobId" which have succeeded but I don't know how to build such query if at all possible?

Here is a query that works but it doesn't allow to specify the item status, so it returns all items for a job:

import * as appsyncUtils from "@aws-appsync/utils"

export function request(
  ctx: appsyncUtils.Context<QueryGetJobItemsArgs>
): appsyncUtils.DynamoDBQueryRequest {
  const { limit = 100, jobId, nextToken } = ctx.arguments;
  return {
    operation: "Query",
    query: {
      expression: "#jobId = :jobId",
        expressionNames: {
          "#jobId": "jobId",
        },
        expressionValues: {
          ":jobId": util.dynamodb.toDynamoDB(jobId),
        },
    },
    index: "jobIdIndex",
    nextToken,
    limit
  }
}

I tried the following query but it doesn't work as status is not a key:

import * as appsyncUtils from "@aws-appsync/utils"

export function request(
  ctx: appsyncUtils.Context<QueryGetJobItemsArgs>
): appsyncUtils.DynamoDBQueryRequest {
  const { limit = 100, jobId, status, nextToken } = ctx.arguments;
  return {
    operation: "Query",
    query: {
      expression: "#jobId = :jobId AND #status = :status",
        expressionNames: {
          "#status": "status",
          "#jobId": "jobId",
        },
        expressionValues: {
          ":status": util.dynamodb.toDynamoDB(status),
          ":jobId": util.dynamodb.toDynamoDB(jobId),
        },
    },
    index: "jobIdIndex",
    nextToken,
    limit
  }
}

Do I need to use a scan or to modify my table?

Answer 1

You need to use filter for status as you're correct, it is not a key:

import * as appsyncUtils from "@aws-appsync/utils"

export function request(
  ctx: appsyncUtils.Context<QueryGetJobItemsArgs>
): appsyncUtils.DynamoDBQueryRequest {
  const { limit = 100, jobId, status, nextToken } = ctx.arguments;
  return {
    operation: "Query",
    query: {
      expression: "#jobId = :jobId",
        expressionNames: {
          "#jobId": "jobId"
        },
        expressionValues: {
          ":jobId": util.dynamodb.toDynamoDB(jobId),
        },
    },
    filter: {
      expression: "#status = :status",
        expressionNames: {
          "#status": "status",
        },
        expressionValues: {
          ":status": util.dynamodb.toDynamoDB(status),
        },
    }
    index: "jobIdIndex",
    nextToken,
    limit
  }
}

https://docs.aws.amazon.com/appsync/latest/devguide/js-aws-appsync-resolver-reference-dynamodb-query.html