Question 136817 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How to do a dynamic search using multiple attributes in DynamoDB?

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?

Tags:

2

Answers


  1. 0

    Assuming that the partition key for your user table is "City" and the sort key is "CompanyName", you can use the Query operation with a filter expression to search for records with partial attributes. Here’s an example code snippet in C#:

        var queryRequest = new QueryRequest
{
    TableName = "User",
    KeyConditionExpression = "#city = :city",
    ExpressionAttributeNames = new Dictionary<string, string>
    {
        {"#city", "City"}
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        {":city", new AttributeValue { S = "CityName" }}
    },
    FilterExpression = "contains(SearchFirstName, :firstName) and contains(SearchLastName, :lastName)",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        {":firstName", new AttributeValue { S = "bh" }},
        {":lastName", new AttributeValue { S = "ta" }}
    }
};

var queryResponse = await _context.QueryAsync(queryRequest);
var allUsers = queryResponse.Items.Select(x => new User 
{
    FirstName = x["FirstName"].S,
    LastName = x["LastName"].S,
    City = x["City"].S,
    CompanyName = x["CompanyName"].S
}).ToList();

return allUsers;

    In the above code, the QueryRequest object is created with the required parameters for querying the user table. The KeyConditionExpression specifies the partition key for the table, and the FilterExpression is used to filter records with partial attributes.

    The ExpressionAttributeValues dictionary is used to specify the attribute values for the filter expression. Note that the contains function is used in the filter expression to perform a partial search.

    Finally, the QueryAsync operation is called with the QueryRequest object to execute the query, and the response is converted into a list of user objects.

    Using a filter expression with the Query operation can be more efficient than using the Scan operation, as it reads only the items that match the filter expression. However, the performance of the query depends on the size of the data and the complexity of the filter expression.

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search