DynamoDB Query with partition key and sort key giving me Query condition missed key schema element error

Question

I'm using boto3 in Python to make queries to a DynamoDB. I'm using a Local Secondary Index to query my results sorting by an attribute (start_date). However, when connecting two Key( ) elements with &, it gives me an error, even though the first Key() is based on the partition key.

I want to retrieve all values with PK ra that the Sort Key (LSI) is greater than start

def get_associated_actions_by_ra(self, ra: str, amount: int, start: Optional[int] = None, end: Optional[int] = None, exclusive_start_key: Optional[str] = None) -> List[AssociatedAction]:
        '''
        Retrieves all associated actions of a member, filtered by an optional time range specified by start and end parameters. The method allows for pagination using the exclusive_start_key parameter to determine the starting point of the action list, and the amount parameter to determine the maximum number of actions to be retrieved.
        If no actions are found, returns []
        '''
        query_string = Key(self.dynamo.partition_key).eq(self.associated_action_partition_key_format(member_ra=ra))

        if start:
            query_string = query_string & Key(self.dynamo.sort_key).gte(self.associated_action_lsi1_sort_key_format(start_date=start))
            
        resp = self.dynamo.query(IndexName="LSI1", key_condition_expression=query_string, Select='ALL_ATTRIBUTES')
        associated_actions = []
        for item in resp.get("Items"):
            if item.get("entity") == "associated_action":
                associated_actions.append(AssociatedActionDynamoDTO.from_dynamo(item).to_entity())
        
        return associated_actions

I tried to change the query string, use it directly inside the query method and didn't get it to work.

Edit: As requested, here is my create_table:

dynamo_client.create_table(
            TableName=table_name,
            KeySchema=[
                {
                    'AttributeName': 'PK',
                    'KeyType': 'HASH'
                },
                {
                    'AttributeName': 'SK',
                    'KeyType': 'RANGE'
                }
            ],
            LocalSecondaryIndexes=[
                {
                    'IndexName': 'LSI1',
                    'KeySchema': [
                        {
                            'KeyType': 'HASH',
                            'AttributeName': 'PK'
                        },
                        {
                            'KeyType': 'RANGE',
                            'AttributeName': 'start_date'
                        }
                    ],
                    'Projection': {
                        'ProjectionType': 'ALL',
                    }
                }
            ],
            GlobalSecondaryIndexes=[
                {
                    'IndexName': 'GSI1',
                    'KeySchema': [
                        {
                            'KeyType': 'HASH',
                            'AttributeName': 'GSI1-PK'
                        },
                        {
                            'KeyType': 'RANGE',
                            'AttributeName': 'GSI1-SK'
                        }
                    ],
                    'Projection': {
                        'ProjectionType': 'ALL',
                    }
                }
                ],
            AttributeDefinitions=[
                {
                    'AttributeName': 'PK',
                    'AttributeType': 'S'
                },
                {
                    'AttributeName': 'SK',
                    'AttributeType': 'S'
                },
                {
                    'AttributeName': 'start_date',
                    'AttributeType': 'N'
                },
                {
                    'AttributeName': 'GSI1-PK',
                    'AttributeType': 'S'
                },
                {
                    'AttributeName': 'GSI1-SK',
                    'AttributeType': 'S'
                }
            ],
            BillingMode='PAY_PER_REQUEST',

        )

I don't understand exactly what is "printing out the query string", so I debugged the code and got what value the query string holds at the moment of the query. https://i.stack.imgur.com/3S3d3.png

Answer 1

If I was to make an assumption before you provide the requested information I would say this value for LSI sort key is wrong based on your variable naming:

Key(self.dynamo.sort_key)

As your sort key differs for your base table and index, I'm guessing you overlooked changing it to match your LSI sort key which is start_date

---

Your print out of the query string isn't resolved to the string and doesn't help me help you. Without seeing your entire code which you haven't shared it's hard to help you. My suggestion is to try the query with hard coded values like below:

def get_associated_actions_by_ra(self, ra: str, amount: int, start: Optional[int] = None, end: Optional[int] = None, exclusive_start_key: Optional[str] = None) -> List[AssociatedAction]:
        '''
        Retrieves all associated actions of a member, filtered by an optional time range specified by start and end parameters. The method allows for pagination using the exclusive_start_key parameter to determine the starting point of the action list, and the amount parameter to determine the maximum number of actions to be retrieved.
        If no actions are found, returns []
        '''
        query_string = Key('PK').eq('HA')

        if start:
            query_string = query_string & Key('start_date').gte(123456))
            
        resp = self.dynamo.query(IndexName="LSI1", key_condition_expression=query_string, Select='ALL_ATTRIBUTES')
        associated_actions = []
        for item in resp.get("Items"):
            if item.get("entity") == "associated_action":
                associated_actions.append(AssociatedActionDynamoDTO.from_dynamo(item).to_entity())
        
        return associated_actions