IN operator won't work with array variable in FilterExpression?

[austencollins]

First off, great library. Thank you!

I've got this array: var client_ids = ["data_8109"]; and I'm putting it into the FilterExpression function with an IN operator, but the query is not locating the record. If I don't use the array variable and hardcode the single client_id in, it shows up. Is my syntax missing something?

        var params = {
        TableName: 'apps',
        IndexName: 'state-index',
        KeyConditions: [
            DynamoDBInstance.Condition("state", "EQ", "active")
        ],
        FilterExpression: "#a IN (:client_ids)",
        ExpressionAttributeNames: {
            "#a": "client_id"
        },
        ExpressionAttributeValues: {
            ":client_ids": client_ids
        }
    };

    console.time('DynamoDB: ShowApp By Client IDs');
    DynamoDBInstance.query(params, function(error, response) {

[raymolin]

Hey there, I'm glad you are enjoying the library! At the moment, DynamoDB doesn't iterate over your list server side. If you want to search your list for an item, you'll need to do one of two things.

1. Load the items into memory and do a manual filter, since the consumed capacity is the same, it just requires a little more logic
2. If you know the size of your list ahead of time, you can build the full enumeration of the list of expression (i.e. #a EQ :client_ids[0] ... AND size(:client_ids) = :expected_list_size)

Feel free to ask more questions otherwise you can just close out the issue, thanks!

[raymolin]

Please re open this issue, if you have any other problems. Thanks!

[k1master]

Is it still not supporting iterating over the list in server side?

[praserocking]

Does this mean that I can't query or scan on the Array of objects which I have stored in my DynamoDB?

[nirmalgoswami]

@praserocking same problem here,did you solve that using array ?

[divyeshdhokiya]

@raymolin, is it still not supporting iterating over list in dynamo ?
@praserocking @nirmalgoswami got any solution for the issue?

[nirmalgoswami]

@divyeshdhokiya yup,here it is

Stackoverflow

[divyeshdhokiya]

@nirmalgoswami thank you for this. This is first half of my requirement.
One more question is can we query on list field in dynamo?
e.g. I have below list field in table,

GroupOfUsers
['abc','xyz']
['mno','xyz']

Now I need all the rows which contains 'xyz' user.
Is there any way to query like this?

Thanks.

,

[nirmalgoswami]

i am not sure about it

[divyeshdhokiya]

@nirmalgoswami thanks man for your quick response.

[divyeshdhokiya]

@nirmalgoswami Hi, I tried the query you suggested in previous comment but I am getting error while fetching data, query and error you can see here

Thanks.

[amcp]

You can use filter expressions to do server side filtering before the scan/query result comes back to your client. For example, if the GroupOfUsers attribute in your items is a StringSet, the filter expression contains(GroupOfUsers, :user) combined with ExpressionAttributeValues={":user":"xyz"} will only return items that have xyz in the GroupOfUsers attribute. This may require scanning a large part of the table, depending on your use case, so you might want to consider denormalizing schema so that you can use the Query operation to lessen the number of items you need to read.

[CoryShiraishi]

@raymolin in 2015 you said: "At the moment, DynamoDB doesn't iterate over your list server side."
Is there any plan to support this functionality?

Alternatively, is there any plan to update the aws docs to point out that this doesn't work? IMO, it seems like a fairly obvious use case. "#foo IN (:someSet)" where :someSet is actually a set looks like it should work. At this point in time, there's nothing in the official docs warning people away from this approach.

[Loag]

Has this been added yet? I'm having a difficult time understanding why this functionality wouldn't be in a db half way through 2017. I think this is a pretty common use case.

[anusree-mmlab]

Hi I want to query that contains a List Map object
in db the item is in the format:
{
id:'1323234',
item:[
{text:'abcd',author:'x'},
{text:'efgh',author:'y'}
]
created_at:'somedate'

}

i want to make a search where item text matches 'cd'

Can you please help me out?

[Roriz]

Any update about this feature?

[bwaters57]

January 2019 check-in: does anyone have a way to do this?

[gloced]

so, I spent half the day to getting this working. I'm trying to reuse @ac360 's example

var params = {
        TableName: 'apps',
        IndexName: 'state-index',
        KeyConditions: [
            DynamoDBInstance.Condition("state", "EQ", "active")
        ],
        FilterExpression: "#a IN (:client_id0, :client_id1, :client_id2)",
        ExpressionAttributeNames: {
            "#a": "client_id"
        },
        ExpressionAttributeValues: {
            ":client_id0": 'data_8109'
            ":client_id1": ...
        }
   };

I only got it working with declaring every element in the "IN" clause independently. Now you want to automate this, so I came up with this.

let filterExpression = `#a in (${client_ids.map((id, i) => `:client_id${i}`).join(', ')}))`;
params.ExpressionAttributeValues = Object.assign(params.ExpressionAttributeValues, client_ids.reduce((obj, id, i) => {
            obj[`:client_id${i}`] = id;
            return obj;
        }, {}));

hope that helps

[husseinadell]

Hi, i made a simple workaround for the IN operator with arrays

function prepareFilterExpressionForArray(colName, values) {
  let FilterExpression = `${colName} IN (`;
  let ExpressionAttributeValues = {};
  let filter = "";
  values.forEach((element, index) => {
    filter += `:val${index}, `;
    ExpressionAttributeValues[`:val${index}`] = element;
  });
  FilterExpression += filter.substring(0, filter.length - 2) + ")";
  return [FilterExpression, ExpressionAttributeValues];
}

this method will return the FilterExpression and ExpressionAttributeValues for an array and you can extend it with some edits to include more filter options.

Hope it helps anyone stuck in handling arrays with IN operator.

[felipedominguez]

2023 and this still not working....
It is an obvious use case!!!
is it really worth to work with this library???