dynamodb filter with GSI

Question

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.

user2950716 · Accepted Answer · 2016-06-07 02:54:24Z

2

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);

answered Jun 7, 2016 at 2:54

user2950716

451 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2950716 Over a year ago

line which was missing, request.FilterExpression = "lastname = :lastname";

notionquest · Accepted Answer · 2016-06-03 08:53:52Z

0

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());
    }

answered Jun 3, 2016 at 8:53

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

2 Comments

user2950716 Over a year ago

is the indexname the gsi index name? which in my case is firstname.

user2950716 Over a year ago

your answer will work with Java, i found my solution on C#, i will post c# solution shortly, thanks for your reply!

Collectives™ on Stack Overflow

dynamodb filter with GSI

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related