concatenate attributes in search expression

Question

I am trying to build Filter Expression in query for searching data in dynamodb.

var params = { 
        TableName: "ContactsTable",
        ExpressionAttributeNames: {
            "#lastName": "LastName",
            "#firstName": "FirstName",
            "#contactType": "ContactType"
        },
        FilterExpression: "contains(#lastName, :searchedName) or contains(#firstName, :searchedName)",
        ExpressionAttributeValues: {
            ":companyContactType": event.query.companyContactType,
            ":searchedName": event.query.searchedValue
        },
        KeyConditionExpression: "#contactType = :companyContactType"
    };

Users generally search for LastName, FirstName (they append comma to LastName as a common search pattern). However data is stored in separate attributes named LastName and FirstName so that they can search by that as well.

Is there a way by which I can dynamically concatenate these two fields something like contains(#lastName<append comma>#firstName, :searchedName)?

Please follow below links for possible solution : https://stackoverflow.com/questions/29187924/dynamodb-query-using-more-than-two-attributes and https://stackoverflow.com/questions/34921224/case-insensitive-query-in-dynamo-db/34928316 — Arpit Shrivastava, May 01 '19 at 06:46

score 0 · Answer 1 · answered May 02 '19 at 20:43

You should remove comma from user input, split words and, for each word, check if it is contained in both (first name and last name) and 'or' everything together, or even use begins_with instead of contains.

Ex. For "john smith" will result in

contains(#lastName, "john") or contains(#lastName, "smith" ) or contains(#firstName, "john") or contains(#firstName, "smith")

Also contains() is case sensitive from what i know,so you might want to insert first name and last name as lowercase as well as lowercase user's search term.

concatenate attributes in search expression

1 Answers1