CODEWITHSUNDEEP
swiftnosqlamazon-dynamodbswift4aws-sdk
Rolthar
Rolthar

Reputation: 161

DynamoDB Swift4 complex query

I am trying to make a complex query in swift to get data from DynamoDB. I am able to get all information by using the userID. However there are times that I may not know the entirety of the userID and need to make a more complex query.

For instance, if I know the first name and the last name, and the user id format is "firstname:lastname:email", I need to be able to query all userID's that include the first and last name, then add a where for another column.

I am very new to dynamo and want to accomplish something like the sql query below.

SQL example:

SELECT * FROM mytable
WHERE column2 LIKE '%OtherInformation%'
  AND (column1 LIKE '%lastname%' OR column1 LIKE '%firstname%')

Here is the code I have in swift4 for getting the userID if I know it exaclty, not entirely sure how to modify this for complex queries.

func queryDBForUser(Fname: String, Lname: String) {

 let userId = Fname + "." + Lname + ":" + (UIDevice.current.identifierForVendor?.uuidString)!

self.UserId = userId

let objectMapper = AWSDynamoDBObjectMapper.default()

let queryExpression = AWSDynamoDBQueryExpression()

queryExpression.keyConditionExpression = "#userId = :userId"
queryExpression.expressionAttributeNames = ["#userId": "userId",]
queryExpression.expressionAttributeValues = [":userId": userId,]

objectMapper.query(CheckaraUsers.self, expression: queryExpression, completionHandler: {(response: AWSDynamoDBPaginatedOutput? ,error: Error?) -> Void in
    if let error = error {
        print("Amazon DynamoDB Error: \(error)")

        return
    }

I have also tried many variations along the lines of the following code, with no luck:

queryExpression.keyConditionExpression = "#FirstName = :firstName and #LastName = :lastName,"
queryExpression.expressionAttributeNames = ["#FirstName": "FirstName" , "#LastName": "LastName"]
queryExpression.expressionAttributeValues = [":FirstName": Fname,":LastName": Lname]

Any help would be greatly appreciated, thanks in advance!

Upvotes: 0

Views: 235

Answers (2)

Rolthar
Rolthar

Reputation: 161

Got the query working by adding a secondary index to my DynamoDB table, although this is not what I initially wanted, it still works as now I can query for a value that exists in both columns I needed, without doing a table scan and filtering after.

query code:

queryExpression.indexName = "Index-Name" queryExpression.keyConditionExpression = "#LastName = :LastName and #otherValue = :otherValue" 
queryExpression.expressionAttributeNames = ["#LastName": "LastName" , "#otherValue": "otherValue"] 
queryExpression.expressionAttributeValues = [":LastName": Lname,":otherValue": self.otherValue!]

Upvotes: 0

Brian Winant
Brian Winant

Reputation: 3035

You won't be able to do this with a DynamoDB query. When you query a table (or index) in DynamoDB you must always specify the complete primary key. In your case that would mean the full value of "firstname:lastname:email".

You could sort of do this with a DynamoDB scan and a filter expression, but that will look at every item in your table, so it could be slow and expensive. Amazon will charge you for the read capacity necessary to look at every item in the table.

So if you really wanted to, the filter expression for the scan operation would be something like:

"contains (#FirstName, :firstName) and contains (#LastName, : lastName)"

Note that contains looks for an exact substring match, so if you want case insensitive matches (like ILIKE in SQL) it won't work.

If you need to do these types of queries then you need to evaluate whether or not DynamoDB is the right choice for you. DynamoDB is a NoSQL key/value store basically. It trades limited querying functionality for scalability and performance. If you are coming at DynamoDB from a SQL background and are expecting to be able to do freeform queries of anything in your table, you will be disappointed.

Upvotes: 1

Related Questions