CODEWITHSUNDEEP
javac#amazon-web-servicesamazon-dynamodb
user2950716
user2950716

Reputation: 45

dynamodb filter with GSI

So i have a dynamo table for employee with the following fields

employeeid (primary index/hash key)
someotherid (GSI)
firstname (GSI)
lastname
email
dob
secretkey
address
phone
email (GSI)

I cannot create a gsi on any more fields besides what i have above.

what i need to do is search for employees with the same
firstname,
lastname,
dob
and secretkey,

if i dont have one then i need to insert a new employee if i do i need to perform an action.

is there a way in dynamodb using c# where i could search on those 4 fields?

i have a gsi on firstname, i do not want to use a scan which allows me to use filters, is there a way to query or filter using the firstname gsi i have without doing a scan on the table.

Any advice would be appreciated.

Thanks for reading.

update

this is what i tried in c#

var request = new QueryRequest()
{
   TableName = "employee",
   IndexName = "firstName-employeeId-index",
   Select = Select.ALL_ATTRIBUTES,
   ScanIndexForward = false
};
String keyConditionExpression;
Dictionary<string, AttributeValue> expressionAttributeValues = new Dictionary<string, AttributeValue>();
keyConditionExpression = "firstName = :firstName and lastName = :lastName ";
expressionAttributeValues.Add(":firstName", new AttributeValue { S = firstName });
expressionAttributeValues.Add(":lastName", new AttributeValue { S = lastName });

request.KeyConditionExpression = keyConditionExpression;
request.ExpressionAttributeValues = expressionAttributeValues;
var result = await Client.QueryAsync(request);

I get an error when i run the above as it looks for my hashkey.

Upvotes: 1

Views: 3798

Answers (2)

user2950716
user2950716

Reputation: 45

the code i posted with the question has one line missing which i figured out, below is what i was looking for.

var request = new QueryRequest()
{
   TableName = "employee",
   IndexName = "firstName-employeeId-index",
   Select = Select.ALL_ATTRIBUTES,
   ScanIndexForward = false
};
String keyConditionExpression;
Dictionary<string, AttributeValue> expressionAttributeValues = new Dictionary<string, AttributeValue>();
keyConditionExpression = "firstname = :firstname";
expressionAttributeValues.Add(":firstname", new AttributeValue { S = firstname });
expressionAttributeValues.Add(":lastName", new AttributeValue { S = lastname });

request.KeyConditionExpression = keyConditionExpression;
request.FilterExpression = "lastname = :lastname";
request.ExpressionAttributeValues = expressionAttributeValues;

var result = await Query<EmployeeDataModel>(Client, request);

Upvotes: 2

notionquest
notionquest

Reputation: 39186

You can query the secondary index directly. Please see the below sample Java code.

Also, I suggest you to consider to keep all the fields required as part of query result in GSI itself as it would give you good performance. It all depends on your use case. This is just a suggestion considering one scenario you outlined in this thread.

Refer this link

    List<String> queryResultJson = new ArrayList<>();
    DynamoDB dynamoDB = new DynamoDB(dynamoDBClient);
    Table table = dynamoDB.getTable("table_name");
    Index index = table.getIndex("indexname");
    ItemCollection<QueryOutcome> items = null;

    QuerySpec querySpec = new QuerySpec();
    querySpec.withKeyConditionExpression("firstname = :val1")
            .withValueMap(new ValueMap()                        
                    .withString(":val1", fname));
    items = index.query(querySpec);
    Iterator<Item> itemIterator = items.iterator();
    while (itemIterator.hasNext()) {            
        queryResultJson.add(itemIterator.next().toJSON());
    }

Upvotes: 0

Related Questions