Composite sort key

[johnEthicalTechnology]

Note: If your question is regarding the AWS Amplify Console service, please log it in the
official AWS Amplify Console forum

Which Category is your question related to?
Indexing data with keys

Amplify CLI Version
4.32.1

You can use amplify -v to check the amplify cli version on your system

What AWS Services are you utilizing?
Lambda, DynamoDB, AppSync, SSM, IAM

Provide additional details e.g. code snippets
I created this composite key:

@key(
    name: "ByMappingIdAndStatus"
    fields: ["mappingDataID", "status", "createdTime"]
    queryField: "listLeadsByMappingDataIdAndStatus"
  )

But when I run a query against it no data is returned even though I'm expecting data to be return. I don't get an error just an empty array. I'm not entirely sure why this is happening.

In the docs I read that I'm meant to back fill data. When I created another field (status#createdTime) and filled the value in then tried the query it returned the expected record.

Then I create a brand new record in the table which I expect will be automatically have the new composite key applied to it but when I perform the query as before it doesn't find the new record. Does this mean I'm meant to create a field in the schema like so status#createdTime?

Here's the schema. As you can see i've got a bunch of keys and I've not had any problems with them:

type Lead
  @model
  @key(
    name: "byMappingData"
    fields: ["mappingDataID", "createdTime"]
    queryField: "listLeadsByMappingDataId"
  )
  @key(
    name: "ByStatus"
    fields: ["status", "createdTime"]
    queryField: "listLeadsByStatus"
  )
  @key(
    name: "ByMappingIdAndStatus"
    fields: ["mappingDataID", "status", "createdTime"]
    queryField: "listLeadsByMappingDataIdAndStatus"
  )
  @auth(
    rules: [
      { allow: private, operations: [update, read] }
      { allow: private, provider: iam, operations: [create, read, update] }
    ]
  ) {
  id: ID!
  formId: String!
  pageId: String!
  processedLeadData: String
  status: Status!
  deliveryStatus: DeliveryStatus!
  zohoResponse: String
  createdTime: String
  fbLeadId: String
  rawLeadData: [FieldData]
  mappingDataID: ID!
  submitDataToZoho: Boolean
  testLead: Boolean
  errors: Int
  mappingData: MappingData @connection(fields: ["mappingDataID"])
}

This is the query

listLeadsByMappingDataIdAndStatus: gql`
    query ListLeadsByMappingDataIdAndStatus(
      $mappingDataID: ID
      $statusCreatedTime: ModelLeadByMappingIdAndStatusCompositeKeyConditionInput
      $sortDirection: ModelSortDirection
      $filter: ModelLeadFilterInput
      $limit: Int
      $nextToken: String
    ) {
      listLeadsByMappingDataIdAndStatus(
        mappingDataID: $mappingDataID
        statusCreatedTime: $statusCreatedTime
        sortDirection: $sortDirection
        filter: $filter
        limit: $limit
        nextToken: $nextToken
      ) {
        items {
          id
          formId
          pageId
          processedLeadData
          status
          deliveryStatus
          zohoResponse
          createdTime
          fbLeadId
          rawLeadData {
            name
            values
          }
          mappingDataID
          submitDataToZoho
          testLead
          errors
          createdAt
          updatedAt
        }
        nextToken
      }
    }
  `

This is the use of the query in my function:

const inactiveLeads = await gateway.runQuery({
      operationName: 'ListLeadsByMappingDataIdAndStatus',
      variables: {
        mappingDataID: mappingDataId,
        statusCreatedTime: { beginsWith: { status: 'INACTIVE' } },
        sortDirection: 'DESC',
        // filter: {
        //   status: { eq: 'INACTIVE' }
        // },
        limit: 25,
        nextToken
      },
      query: print(listLeadsByMappingDataIdAndStatus)
    })

This is the query in the AppSync console:

query MyQuery {
  listLeadsByMappingDataIdAndStatus(mappingDataID: "20838c44-4aab-4d0a-9aa0-b60288972046", statusCreatedTime: {beginsWith: {status: INACTIVE}}) {
    items {
      id
    }
  }
}

[DeniferSantiago]

It is also happening to me
amplify -v
4.41.2

[frankclaassen]

This is a bug whereby composite key values are not being populated in dynamodb, see issue #6634

[frankclaassen]

further confirmation is that it seems the issue is only with creating new records, updating a record gets the composite key values set correctly

[johnEthicalTechnology]

@frankclaassen thanks for pointing this out.

From what I understanding, you are saying that if I use the composite key as I described above AND then update the records using that key the composite key will work correctly?

Do I have to do anything else to get the composite key to work correctly? Is it a particular kind of update I have to do to the records?

[frankclaassen]

@johnEthicalTechnology you can't update a record using a composite key (not that I'm aware of anyway) but what I mean is if you run the update mutation (updateLead in your schema) generated by Amplify or write one in the console, then the composite key gets populated with actual data

[frankclaassen]

@johnEthicalTechnology might want to recheck your data, I'm on amplify 4.44.0 and magically this is not happening anymore since my last push to my repo which triggered a build. update: bug only seems to have disappeared in my dev system but is still happening in my prod system for some unknown reason

[frankclaassen]

Further testing and investigation has found that the issue in fact came in when I updated the record in my trigger function, I did not specify values for the keys used in composite keys in the input passed to the update mutation. No error was thrown for some reason and my data ends up with ${ctx...} values.

IMHO, it is incorrect (and a bug in my opinion) of the resolvers to update composite keys when none of the values used in composite keys have changed

[johnEthicalTechnology]

@frankclaassen I created a new composite key. I'm on Amplify 4.45. I created a new record through my app and no composite key. I updated the same record through my app with an update query and still no composite key. When I create the MappingData record there are values for the keys that I'm using in the composite key. Yet still it's not creating the composite key. I even, just as suggested in this issue, re-generated my VTL code. Then I ran the update mutation and it still didn't generate the expected composite key for the records.

@nikhname is it possible to get a bit of help with this one?? This is severely affecting the production environment for a client's app. Am I doing something that I shouldn't? What should I expect from a composite key?

This is a screen of the index after I've used the update mutation. As you can see there's no items associated.

This is the relevant part of the schema for the composite key:

type MappingData
  @model
  @auth(
    rules: [
      { allow: private, operations: [update, read] }
      { allow: private, provider: iam, operations: [create, read, update] }
    ]
  )
  @key(
    fields: ["archived", "formCreatedTime"]
    name: "ByArchived"
    queryField: "listMappingDataByArchived"
  )
  @key(
    name: "ByFormIdAndArchive"
    fields: ["formId", "archived", "formCreatedTime"]
    queryField: "listFormsByPageIdAndArchive"
  ) {
  id: ID!
  formName: String
  formId: String
  pageId: String
  mappingInfo: MappingInfo
  rawLeadFields: [RawLeadField]
  status: Status
  formCreatedTime: String
  deliveryStatus: Int
  errors: Int
  campaignName: String
  campaignId: String
  archived: ArchivedStatus
  leads: [Lead] @connection(keyName: "byMappingData", fields: ["id"])
}

I'm not sure if this makes a difference but when a record for MappingData table is created I'm using the @crft/appsync-gateway package which is basically an implementation of suggested use of graphql from a lambda

[cjihrig]

@johnEthicalTechnology are you still facing this issue on recent versions of the CLI? I just spent some time trying to reproduce the issue, but I believe it has been fixed.

[johnEthicalTechnology]

I'm not sure. I'm not working on that project anymore. When I ran into this issue I found a workaround to get the functionality the project required so it wasn't a big issue.

Thanks for checking! I'm happy for you (or me) to close it.