Filter DynamoDB query using IN

Question

I'm trying to query a DynamoDB table using an IN filter. My query works when I pass in a single value to the IN, but when I pass in multiple, I get no matches.

Here's the initial setup of the params object.

  var params = {
    TableName: "Interactions",
    IndexName: "environment-time-index",
    KeyConditionExpression: "#environment = :environment and #time between :start and :end",
    FilterExpression: "#product = :product",
    ExpressionAttributeNames: {
      "#product": "product",
      "#environment": "environment",
      "#time": "time"
    },
    ExpressionAttributeValues: {
      ":product": product,      
      ":environment": environment,
      ":start": start,
      ":end": end
    }
  };

Next, if the user supplies a firm query parameter, I modify the params object, like so. This is where I use the IN operator.

  if (req.query.firm) {
    var firms = req.query.firm;
    console.log('debug', firms);
    params.FilterExpression += " AND #firmCode IN (:firms)";
    params.ExpressionAttributeNames["#firmCode"] = "firmCode";
    params.ExpressionAttributeValues[":firms"] = firms;
  }

Finally, I run the query, like this.

  docClient.query(params, function(err, data) {
    if (err) {
      log.error(`Unable to scan. Error: ${JSON.stringify(err, null, 2)}`);
      res.status(500).json({ error: "Oh, snap!" });
    } else {
      data.Parameters = params.ExpressionAttributeValues;
      log.info(`Scan succeeded. Received ${data.Count} items.`)
      res.send(data);
    }
  });

When the firm parameter contains a single value, I get results back.

Level="INFO", Date="2016-09-19 14:26:03,373", Message="batchinsight received GET for /api/history/interactions2", Product="Exhaust", Service="batchinsight", AppDomain="Exhaust_batchinsight"
debug TW7ZN
Level="INFO", Date="2016-09-19 14:26:03,623", Message="Scan succeeded. Received 19 items.", Product="Exhaust", Service="batchinsight", AppDomain="Exhaust_batchinsight"

But when it contains multiple values, I get no results back.

Level="INFO", Date="2016-09-19 14:35:16,896", Message="batchinsight received GET for /api/history/interactions2", Product="Exhaust", Service="batchinsight", AppDomain="Exhaust_batchinsight"
debug TW7ZN,TEXK4
Level="INFO", Date="2016-09-19 14:35:16,991", Message="Scan succeeded. Received 0 items.", Product="Exhaust", Service="batchinsight", AppDomain="Exhaust_batchinsight"

The DynamoDB documentation suggests the IN operator can accept a comma separated list of values, but I can't get it to work. Please help! http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.SpecifyingConditions.html#ConditionExpressionReference

Answer 1

It's easy to assume that the IN operator takes a list of strings, but remember, this is DynamoDB, so it expects a list of variables. i.e. (:string1, :string2)

I wrote a gist here.

const listToObjectMappings = (list) => {
    let x = {}
    for (var i=0; i<list.length; i++){
        x[':' + i.toString()] = list[i]
    }
    return x
}

let statuses = ['available', 'in-transit', 'delivered']
let mappings = listToObjectMappings(statuses)

let type = 'delivery' // type is just to demonstrate the use of Object.assign to build the 'ExpressionAttributeValues'
let joined = Object.keys(mappings).join(); // string: ":available, :in-transit, :delivered"

let query = {
    FilterExpression: '#type = :type AND #stat IN (' + joined + ')',
    ExpressionAttributeNames: {
        '#stat' : 'status',
        '#type' : 'type'
    },
    ExpressionAttributeValues: Object.assign( { ':type': type }, mappings )
}