Using IN operator in FilterExpression on numeric array

Question

I'm trying to make an AWS Lambda interact with a DynamoDB table but I can't seem to generate correctly the scan() query:
In my scenario we have 2 tables, one contains the teachers and one contains their lectures. Each teacher has 1+ subject to teach and each lesson is referred to a single subject of the teacher.
I'd like to obtain, from the lectures table, every lecture of a specific teacher (given its identificator).
So far I've tried this:

exports.handler = async(event, context, callback) => {
   
    var courses_ids = "";
    event.courses.forEach(function (course) {
       courses_ids+=''+course.id+ ', ';
    });
    courses_ids = courses_ids.substring(0, courses_ids.length -2);
    console.log("Courses IDs: " + courses_ids); // -> 1, 2, 3
  
    await retrieveLecturesOfTeacher(courses_ids).then(data => {
       console.log("Resulting data: "+JSON.stringify(data.Item))
       // TODO
    });
};

function retrieveLecturesOfTeacher(courses_ids) {
    var filter_expression = 'course_id IN ( '+courses_ids+' ) ';
    console.log("Filter Expression: "+filter_expression);
    const params = {
        TableName: 'lectures',
        FilterExpression: 'course_id IN ( :courses_ids )' ,
        ExpressionAttributeValues: {
            ':courses_ids' : courses_ids
        }
    }
    console.log("Params: "+JSON.stringify(params))
    return ddb.scan(params).promise();
}

But no lecture is found for courses 1,2 and 3 (despite their existence in the database).

Function Logs:
   INFO    Params: {"TableName":"lezioni","FilterExpression":"id_corso IN ( :courses_ids )","ExpressionAttributeValues":{":courses_ids":"undefined, undefined"}} 
   INFO    Resulting data: null

However replacing the parameters as follows solves the problem:

const params = {
    TableName: 'lectures',
    FilterExpression: 'course_id IN ( :course_1, :course_2, :course_3) ',
    ExpressionAttributeValues: {
        ':course_1' : 1,
        ':course_2' : 2,
        ':course_3' : 3
    }
}

I already checked this and this questions but my problem seems to be related to the data type as the course_id in the lecture table is a number, while my courses_ids variable is a string. Is there a solution (also considering that a teacher can have an arbitrary number of exams)?

You say no lecture is found, but what gets returned from the scan? Can you print out the `params` hash so we can see what is getting sent to scan? — Seth Geoghegan, Sep 02 '21 at 19:12
@SethGeoghegan I added the function input and output, however I was able to found a solution, so I'll post an answer later today — LucaBonadia, Sep 02 '21 at 19:45

score 0 · Accepted Answer · answered Sep 02 '21 at 20:47

As it's possible to see from the question, the main problem seems to be the type difference, in particular changing the test as follows will result into a failure (as it will produce Resulting data: undefined):

const params = {
    TableName: 'lezioni',
    FilterExpression: 'id_corso IN ( :course_1, :course_2, :course_3) ',
    ExpressionAttributeValues: {
        ':course_1' : "1",
        ':course_2' : "2",
        ':course_3' : "3"
    }
}

Thus, as it is not possible to generate a string that correctly retrieves the lectures I had to find a workarournd to preserve the data type: in particular dynamically creating the FilterExpression string and the ExpressionAttributeValues maps. I hereby report the corrected code.

exports.handler = async(event, context, callback) => {
    
    var lectures_map = {};
    var i = 0;
    var query_string = "";
    
    event.corsi.forEach(function (course) {
       lectures_map[":value_"+i] = course.course_id;
       query_string += ":value_"+i+", ";
       i+=1;
    });
    
    query_string = query_string.substring(0, query_string.length -2);
  
    await retrieveLecturesOfTeacher(query_string, lectures_map).then(data => {
        event.lectures = data;
        callback(null, event);
    });
};

function retrieveLecturesOfTeacher(query_string, attribute_values) {
    var filterExpression = 'course_id IN ( '+query_string+' ) '
    const params = {
        TableName: 'lectures',
        FilterExpression: filterExpression,
        ExpressionAttributeValues: attribute_values
    }
    console.log("Params: "+JSON.stringify(params))
    return ddb.scan(params).promise();
}

Here is the scan parameters

Function Logs:
    INFO    Params: {"TableName":"lezioni","FilterExpression":"id_corso IN ( :value_0, :value_1 ) ","ExpressionAttributeValues":{":value_0":1,":value_1":2}}

Using IN operator in FilterExpression on numeric array

1 Answers1