How to do a dynamic search using multiple attributes in DynamoDB?

Question

I have a user table with following attributes:

FirstName
LastName
City
CompanyName

I want to query the table with any combination of these attributes and that too partial .

For example: Input - Firstname- "bh" and lastname - "ta" Output -A Record with FirstName "aBHishek" , lastName"gupTA"

How do I do this query without using scan? Does using scanconditions with contains means the read operation is less costly?

I have tried this in C#:

var conditions = new List<ScanCondition>();

if (!string.IsNullOrEmpty(user.FirstName))
{
    var firstName = new ScanCondition("SearchFirstName", ScanOperator.Contains, user.FirstName.ToLower());
    conditions.Add(firstName);
}
 

if (!string.IsNullOrEmpty(user.LastName))
{
    var lastName = new ScanCondition("SearchLastName", ScanOperator.Contains, user.LastName.ToLower());
     conditions.Add(lastName);
}

var allUsers = await _context.ScanAsync<OpenOrderUser>(conditions).GetRemainingAsync();
return allUsers;

it does the trick but I want to move away from Scan and use Query, any suggestions? is this approach optimal?

What's the key schema of the table? Do you need partial matches on all listed attributes or just first/last name? — jarmod, May 03 '23 at 11:34
Right , I need partial matches on all the listed attributes , it could be any combination. — Abhishek Gupta, May 03 '23 at 18:08
You can't achieve that in the general case without scan and filter. — jarmod, May 03 '23 at 18:28
so the approach I have above is the only way? Do the ScanConditions limit the scan of whole table? — Abhishek Gupta, May 03 '23 at 18:40

score 0 · Accepted Answer · answered May 03 '23 at 16:31

0

Another option you have to retrieve data from an Amazon DynamoDB table when using the AWS SDK for .NET V3 is to use PartiQL. This lets you use SELECT statements to retrieve data from a table in Amazon DynamoDB. Its similiar to SQL.

You can read more here:

PartiQL select statements for DynamoDB

YOu can find full .NET Code example here:

https://github.com/awsdocs/aws-doc-sdk-examples/blob/main/dotnetv3/dynamodb/scenarios/PartiQL_Basics_Scenario/PartiQL_Basics_Scenario/PartiQLMethods.cs

answered May 03 '23 at 16:31

smac2020

9,637
4
24
38

Interesting , I am looking into this more and will try it out – Abhishek Gupta May 03 '23 at 18:41
Be aware that your PartiQL queries are simply going to be converted into the relevant DynamoDB query/scan calls under the covers. It doesn't release you from the obligation to plan your queries and table schema in advance to ensure efficient and performant queries. – jarmod May 04 '23 at 00:47
you are right , its very tricky with PartiQL as well , without a proper where condition, it still treats it as a scan. – Abhishek Gupta Jun 08 '23 at 00:01

How to do a dynamic search using multiple attributes in DynamoDB?

1 Answers1