AWS DynamoDB Scan and FilterExpression using array of hash values

Question

I am having a hard time finding a useful example for a scan with FilterExpression on a DynamoDB table. I am using the javascript SDK in the browser.

I would like to scan my table and return only those records that have HASH field "UID" values within an array I pass to the Scan

Lets say I have an array of unique ids that are the hash field of my table I would like to query these records from my DynamoDB table.

Something like below

var idsToSearch=['123','456','789'] //array of the HASH values I would like to retrieve
var tableToSearch = new AWS.DynamoDB();
var scanParams = {
  "TableName":"myAwsTable",  
  "AttributesToGet":['ID','COMMENTS','DATE'],  
  "FilterExpression":"'ID' in "+idsToSearch+"" 

}
tableToSearch.scan(scanParams), function(err,data){
    if (err) console.log(err, err.stack); //error handler
    else console.log(data); //success response
})

mkobit · Accepted Answer · 2015-05-13 16:09:35Z

15

You should make use of the IN operator. It is also easier to use Placeholders for attribute names and attribute values. I would, however, advise against using a Scan in this case. It sounds like you already have the hash key attribute values that you want to find, so it would make more sense to use BatchGetItem.

Anyways, here is how you would do it in Java:

ScanSpec scanSpec = new ScanSpec()
    .withFilterExpression("#idname in (:val1, :val2, :val3)")
    .withNameMap(ImmutableMap.of("#idname", "ID"))
    .withValueMap(ImmutableMap.of(":val1", "123", ":val2", "456", ":val23", "789"));
ItemCollection<ScanOutcome> = table.scan(scanSpec);

I would imagine using the Javascript SDK it would be something like this:

var scanParams = {
  "TableName":"myAwsTable",
  "AttributesToGet": ['ID','COMMENTS','DATE'],
  "FilterExpression": '#idname in (:val1, :val2, :val3)',
  "ExpressionAttributeNames": {
    '#idname': 'ID'
  },
  "ExpressionAttributeValues": {
    ':val1': '123',
    ':val2': '456',
    ':val3': '789'
  }
}

answered May 13, 2015 at 16:09

mkobit

47.8k12 gold badges162 silver badges159 bronze badges

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

6 Comments

jotamon Over a year ago

This is helpful. I am wondering if there is a way to pass my array of HASH values into the query parameters

mkobit Over a year ago

No, for Query, you must provide a single hash key value to query on. You then narrow your results down on that hash key (with a range key condition or other attribute values).

jotamon Over a year ago

Performance-wise, are you suggesting that BatchGetItem will more sense than a scan? In realty I could be requesting 1,000 items from table containing 50,000 item

mkobit Over a year ago

Yes. Scan reads every item in a table or a secondary index. Also, In the case of a Scan operation, DynamoDB considers the size of the items that are evaluated, not the size of the items returned by the scan.You should look at the Guidelines for Query and Scan.

mkobit Over a year ago

You will still need to batch those calls up since If you request more than 100 items BatchGetItem will return a ValidationException with the message "Too many items requested for the BatchGetItem call". It may be a better idea to evaluate your table design and see if their is a better access pattern to get your data.

|

Taku · Accepted Answer · 2015-12-14 06:56:58Z

I had this issue and figured it out by using contains parameter

// Object stored in the DB looks like this: 
// [
//     'name' => 'myName',
//     'age' => '24',
//     'gender' => 'Male',
//     'visited' => [
//          'countries': ['Canada', 'USA', 'Japan', 'Australia'],
//          'last_trip': '2015/12/13',
//          'reviews_written': 20
//     ]
// 
// ];

$countries = ['Canada', 'USA', 'Japan', 'Australia'];

$paramToMatch = '24';

$client->query([
        'TableName'     => 'MyDyanmoDB',
        'KeyConditions' => [
            'age' => [
                'AttributeValueList' => [
                    $marshaler->marshalValue($paramToMatch)
                ],
                'ComparisonOperator' => 'EQ'
            ]
        ],
        'ExpressionAttributeNames' => [
            '#visited'   => 'visited',
            '#countries' => 'countries'
        ],
        'ExpressionAttributeValues' => [
            ':countries' => $marshaler->marshalValue($countries)
        ],
        'FilterExpression' => 'contains(:countries, #visited.#countries)',
]);

Spencer D · Accepted Answer · 2020-10-05 02:19:10Z

Here's how I was able to use "scan" to get the items with a particular ID ("ContentID") in below example:

var params = {
    TableName: environment.ddbContentTableName,
    ProjectionExpression: "Title, ContentId, Link",
    FilterExpression: "ContentId in (:contentId1, :contentId2, :contentId3, :contentId4)",
    ExpressionAttributeValues: {":contentId1":102,":contentId2":104,":contentId3":103,":contentId4":101}
};

var docClient = new AWS.DynamoDB.DocumentClient();
docClient.scan(params, onQuery);

I can then programmatically construct the FilterExpression and ExpressionAttributeValues based on known values e.g.

    // Create the FilterExpression and ExpressionAttributeValues
    var filterExpression =  "ContentId in ("; 

    var expressionAttributeValues = {};

    for (var i = 0; i < currentFavorites.length; i++) { 
        var contentIdName = ":contentId"+(i+1);
        
        if (i==0) {
            filterExpression = filterExpression + contentIdName;
        } else {
            filterExpression = filterExpression + ", " + contentIdName;
        }

        expressionAttributeValues[contentIdName] = currentFavorites[i];
    }

    filterExpression = filterExpression + ")";

    var params = {
        TableName: environment.ddbContentTableName,
        ProjectionExpression: "Title, ContentId, Link",
        FilterExpression: filterExpression,
        ExpressionAttributeValues: expressionAttributeValues
    };

    var docClient = new AWS.DynamoDB.DocumentClient();
    docClient.scan(params, onQuery);

javatogo · Accepted Answer · 2017-10-30 06:59:38Z

I was also looking for a dynamic solution, than having to manually put each of the parameter names into the conditional expression. Below is a solution:

List<String> valList= new ArrayList<String>(); // Populate the Values in a List    
StringJoiner valMap =  new StringJoiner(","); // This will be the dynamic Value Map

int i=1;

        table = dynamoDB.getTable(myTable);
        StringBuilder filterExpression = new StringBuilder();
        Map<String, Object> eav = new HashMap<String, Object>();

        if(!valList.isEmpty())
        {
            for(String attrVal: valList)
             {
                eav.put(":val"+i, attrVal);
                valMap.add(":val"+i);
                i++;
            }   
            filterExpression.append("attrColName in ("+valMap.toString()+")"); //here attrColName is the DB attribute
        }

        ItemCollection<ScanOutcome> items;
            items = table.scan(
                filterExpression.toString(), //FilterExpression
                null, //ProjectionExpression - choose all columns
                null, //ExpressionAttributeNames - not used in this example
                eav);//ExpressionAttributeValues

Gaurao Burghate · Accepted Answer · 2019-05-13 13:40:57Z

-1

var params = {
TableName: "tableOne",
ProjectionExpression: "Title, ContentId, Link",
FilterExpression: "ContentId in (:contentIds)",
ExpressionAttributeValues: {":contentIds":[11,22,33,44,55]}
};

var docClient = new AWS.DynamoDB.DocumentClient();
docClient.scan(params);

answered May 13, 2019 at 13:40

Gaurao Burghate

3071 silver badge3 bronze badges

1 Comment

mburesh Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

Collectives™ on Stack Overflow

AWS DynamoDB Scan and FilterExpression using array of hash values

5 Answers 5

6 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related