AWS Lambda function to scan/query DynamoDB table using array values as FilterExpression

Question

here's my case: I'm trying to make a query on a table (table name HCI.LocCatApp) using a value sent by API as KeyConditionExpression, and I'm storing the results (which must be numbers not strings) in an array, and I want to use each value from this array as a FilterExpression to scan another table (table name HCI.Category) .. So what I need is to loop on the array values, take each of them as FilterExpression and perform the scan operation. I'm currently trying to use IN but I'm not sure if it's even supported or not. And keep in mind that the array is being filled during the runtime. And the callback can be performed only once.

here's my code:

'use strict'

var AWS = require('aws-sdk');
var mydocumentClient = new AWS.DynamoDB.DocumentClient();

exports.handler = function (event, context, callback) {

    var params = {
        TableName: 'HCI.LocCatApp',
        KeyConditionExpression : 'LocID = :lid',
            ExpressionAttributeValues: {
            ":lid": event.LocID
        },
        ProjectionExpression: 'CatID'

    };
    var catIDs = [];
    var catIDsObject = {};
    var index = 0;

    mydocumentClient.query(params, function (err, data){
        if (err) {
            callback(err, null);
        }else{
          data.Items.forEach(function(item){catIDs.push(item.CatID)});
          //callback(null, catIDs);

        }
    })

    catIDs.forEach(function(value){
      index ++;
      var catIDsKey = ":catID"+index;
      catIDsObject[catIDsKey] = value;
    })


      var params2 = {
        TableName: 'HCI.Category',
        FilterExpression : "CatID IN (:cIDs)",
        ExpressionAttributeValues : {
          ':cIDs' : catIDs
        }

      };
      mydocumentClient.scan(params2, function (err, data){
        if (err) {
            callback(err, null);
        }else{
          callback(null, data);
        }
    })

}

For some reason, the current code runs successfully but it doesn't find any matches, even if I fill in the values manually in the array, there's still no results, the IN operation doesn't seem to work. And many thanks in advance

kaskelotti kaskelotti · Accepted Answer · 2019-07-04T13:08:51

In your code catIds is an array of IDs (strings probably).

When you pass it to FilterExpression, you are assuming that it will be converted to a) string b) to a string in correct format.

FilterExpression : "CatID IN (:cIDs)",
        ExpressionAttributeValues : {
          ':cIDs' : catIDs
        }

I cannot try this myself at the moment, but I'm assuming this is where the query fails. IN operator expects a comma separated list of values to compare to, in parenthesis. So, after the array is inserted to query, it should be like this

FilterExpression : "CatID IN (cat1, cat2, cat2)",

But most probably it contains extra set of [ and ], and maybe even the array to string conversion causes it to something like [Object object] etc.

One solution would be to use Array.join to concatenate all the elements from the array to single string before passing it to FilterExperession. Something like this

 FilterExpression : "CatID IN (:cIDs)",
            ExpressionAttributeValues : {
              ':cIDs' : catIDs.join()
            }

AWS Lambda function to scan/query DynamoDB table using array values as FilterExpression

1 Answers