I am new to dynamodb. I am trying to query on GSI PlatformIndex, Where platform = "x" and it'll be sorted by tx_time. But I also need to match another attribute senderWallet = "y". I think this can be done by adding FilterExpression after making the query. I believe it is a costly read approach. Is there any better/optimized approach to obtain the result?

To put it more simple form, Is it possible to perform such query where 2 attribute(platform and senderWallet) will match with given data and it will be sorted by tx_time in asc/dsc order?

• tx_time is timestamp.
• senderWallet and platform is string data type.

Thank you. Please let me know if there is any trouble understanding the problem.

Schema:

AttributeDefinitions: [{
      AttributeName: 'txHash',
      AttributeType: 'S',
    },
    {
      AttributeName: 'senderWallet',
      AttributeType: 'S',
    },
    {
      AttributeName: 'tx_time',
      AttributeType: 'N',
    },
    {
      AttributeName: 'platform',
      AttributeType: 'S',
    }]
    
GlobalSecondaryIndexes: [{
      IndexName: 'PlatformIndex',
      KeySchema: [
        {
          AttributeName: 'platform',
          KeyType: 'HASH',
        },
        {
          AttributeName: 'tx_time',
          KeyType: 'RANGE',
        },
      ],
      Projection: {
        ProjectionType: 'ALL',
      },
    }]

QueryCode

    const query = {
      TableName: userTxHistorySchema.TableName,
      IndexName: 'PlatformIndex',
      ExpressionAttributeValues: {
        ':platform': platform,
        ':senderWallet': senderWallet,
      },
      ScanIndexForward: false,
      KeyConditionExpression: 'platform = :platform',
      Limit: limit,
      ExclusiveStartKey: lastEvaluatedKey,
      FilterExpression: 'senderWallet = :senderWallet', // Need to optimise this condition while executing the read query
    };

    const response = await queryItems(query);
    console.log('response', response);

CodePudding user response：

You have a few choices.

The easiest design is you create a GSI where the PK is the two values concatenated together (platform#sender) and the SK is your timestamp. No need to filter.

This value concatenation approach is extremely common with DynamoDB.