Filter expression based on a nested attribute in an array - DynamoDB

Question

I have a DynamoDB table where I store this data;

where "device" is the partition key and "datetime" the sort key. I'm able to query data based on PartitionKey and range of dates (sort key). But now, I would need to filter based on "endpoint" which is inside "data" which is an array. For example, I would like to retrieve the data only if endpoint 1 exist in data array, or for example if endpoint IN (1,3) exist. I 'm using following code;

var dev = event.params.querystring.device;    
var from = event.params.querystring.from;
var to  = event.params.querystring.to;
var ascord = event.params.querystring.ascord;
var limit = event.params.querystring.limit;

  
var qryParams = {
  TableName : "mytable",
  KeyConditionExpression: "#id = :dddd and #tm between :time1 and :time2",
  FilterExpression: "data.endpoint = :ep",
  ExpressionAttributeNames:{
      "#id": "device",
      "#tm": "datetime"
  },
  ExpressionAttributeValues: {
      ":dddd": dev,
      ":time1": from, 
      ":time2": to, 
      ":ep": 1
  }
};

docClient.query(qryParams, function(err, data){
    if (err){
        callback(err, null);
    }else {
        callback(null, data);
    }
})

But it doesn't retrieve any record. I also tried to use placeholders (#data.#endpoint) but didn't work. What would be the right way to do it? Also, should I create an index in the table for the endpoints?

Thanks for your help. Regards; Gus

notionquest · Accepted Answer · 2017-08-25 15:25:19Z

3

The attribute data is a list of objects. So, DynamoDB can't find the item if you don't provide the array index of the list attribute. You can use contains function, if you know the value of endpoint and value.

Option 1 - Using contains:-

var params = {
    TableName: table,
    KeyConditionExpression: "#id = :dddd and #tm between :time1 and :time2",
    FilterExpression: "contains (#data, :ep)",
    ExpressionAttributeNames: {
        "#id": "deviceid",
        "#tm": "datetime",
        "#data" : "data"
    },
    ExpressionAttributeValues: {
        ":dddd": "1",
        ":time1": "2017-08-22T00:44:11:424Z",
        ":time2": "2017-08-22T23:44:11:424Z",
        ":ep": {
            "endpoint": 1,
            "value": "23"
        }
    }
};

Option 2 - Using Index :-

var params = {
    TableName: table,
    KeyConditionExpression: "#id = :dddd and #tm between :time1 and :time2",
    FilterExpression: "#data"+"[0].endpoint = :ep",
    ExpressionAttributeNames: {
        "#id": "deviceid",
        "#tm": "datetime",
        "#data" : "data"
    },
    ExpressionAttributeValues: {
        ":dddd": "1",
        ":time1": "2017-08-22T00:44:11:424Z",
        ":time2": "2017-08-22T23:44:11:424Z",
        ":ep": 1
    }
};

answered Aug 25, 2017 at 15:25

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

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

1 Comment

Palsri Over a year ago

Not working for me { ExpressionAttributeValues: { //":templateType": useCase, ":deliveryStatus": { eventType: deliveryStatus } }, FilterExpression: "contains (timestampList, :deliveryStatus)", Select: "COUNT", TableName: "waas-messageDetails-"+env.stage, };

Collectives™ on Stack Overflow

Filter expression based on a nested attribute in an array - DynamoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related