CODEWITHSUNDEEP
amazon-dynamodb
Ranj
Ranj

Reputation: 553

Filter DynamoDB query using IN

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

Upvotes: 8

Views: 12512

Answers (3)

Richard Dunn
Richard Dunn

Reputation: 6780

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 )
}

Upvotes: 9

Dan Berindei
Dan Berindei

Reputation: 7204

IN requires the possible values to be passed separately, a single set parameter does not work.

But filter conditions also support the contains function, which does work with a set parameter:

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

Upvotes: 0

notionquest
notionquest

Reputation: 39226

Actually, you are referring to the ConditionExpression reference document which is different from the FilterExpression.

A condition expression represents restrictions to put in place when you read (please don't get confused this with querying / scanning) and write items in a table.

You are actually querying the table. FilterExpression is used for querying and scanning data.

IN : Checks for matching elements within two sets. AttributeValueList can contain one or more AttributeValue elements of type String, Number, or Binary (not a set type). These attributes are compared against an existing set type attribute of an item. If any elements of the input set are present in the item attribute, the expression evaluates to true.

Please note that IN is used to check whether the single value present in the SET data type.

You can refer the similar post here where OR condition is used to compare the multiple values in SET datatype.

Similarly, if the firmCode is STRING or SET datatype, you should use OR condition.

Upvotes: 2

Related Questions