- [Morgan] Hi, Morgan here. When writing queries for DynamoDB, you can do simple value matching against keys and attributes using key condition expressions and filter expressions. But comparing if two values are equal is not the only operation you can do. Key condition expressions and filter expressions both support other types of comparisons, like greater than or equal to; condition expressions, like and, or, in, and between; as well as functions, like begins with, size, and others that we are going to look at in this video. We're going to be using the Music table that we've already created and populated with data. We are then going to run some queries using the built-in comparison operators and functions to write better and more complex queries. Let's first go look at the current items in the table as a refresher. You can see here I'm already logged in to the AWS Management Console, and I'm viewing the Music table, viewing the Items tab. You can see here we have multiple artists with the different songs in the table. And if I scroll down, we can see we have multiple for U2, Steely Dan, David Bowie. And if I scroll over, you can also see that some of them have album titles, some of them have notes, while others do not. So now, we are going to hop over to Cloud9, where we're going to actually run our queries. The first query we are going to write is going to use the begins_with function that is native to condition expressions. For the query, we are going to use an existing JSON file that contains the values we are going to be passing into the condition expression. Let's take a look at that. This is the values.json file we are going to be using. We can see that the first value is David Bowie and the second one is H. Provided this JSON file and the key condition expression using the begins_with functionality, it will return all items in the table that have the artist name as David Bowie and a song title that begins with the letter H. So, this command is going to read aws dynamodb query, providing the --table-name, which is Music, and then we will provide the --key-condition-expression, which is a string, and we are going to say Artist = :v1, which is going to be our partition key, and begins_with (SongTitle comma :v2) and then we'll provide the --expression-attribute-values, which is coming from the file in the folder inputValues, and it is values.json. So now, we'll go ahead and hit Enter. And if we scroll up here, we can see the items that were returned. It returned two of David Bowie's songs - "Here Today and Gone Tomorrow" as well as "Heroes." A similar function to this is the function contains, which would return data based on if an attribute value contained a specific string you were looking for. Other functions you can use with DynamoDB include the functions attribute exists and attribute not exist. This would allow you to return items only in cases where you were looking for the existence or non-existence of an attribute. For our existing Music table, I went through and added some notes to songs, so I can keep track of comments and ratings. I want to write a query that will return the items in the table if a note exists for the song. Our query for that is going to look something like this - aws dynamodb query, providing the --table-name Music, and then the --key-condition-expression. This key condition expression will be Artist = :v1. And the filter-expression will be attribute_exists, which is the function we're calling, and the attribute we're looking for is called Note. And then, we will pass it into value, which is going to be David Bowie, which is coming from another file, values2, which is again in that inputValues section, values2.json. Now, we'll go ahead and hit Enter. And if we scroll up here, we can see we have one David Bowie song with a note. This is "Space Oddity," which was the opening track for David Bowie's second studio album. Notice how I had to include a filter expression with this query. That's because Note is not the partition key or the sort key. So without a secondary index, I cannot query directly on notes using a key condition expression. DynamoDB supports similar functions to check the type of an attribute, which is called the attribute_type function. Another kind of comparison you can do is using the condition expression IN. For example, I want to write a query that returns all of the songs released by David Bowie in the years 1972, 1984, or 1990, while excluding other songs released in other years. We're going to use another JSON file for this, values3, where we will define the values, David Bowie, for the partition key, and then 1972, 1984, and 1990. So, let's go ahead and write that query. And, this again will start off very similar to the last one, where we say dynamodb query, and then providing the --table-name Music. Then, we will provide a --key-condition-expression, which again is our string. We want to check to see if the artist is David Bowie, and then we will provide a filter-expression, which is Released, which is the attribute we are looking for, IN, then we will provide a list of values: v2, v3, and v4. And then, we will provide the --expression-attribute-values, which is coming from the file in the inputValues folder, and this is values3.json. We'll go ahead and hit Enter. And here, we can see we are returning only songs from those three years. I could've also used the IN keyword and the key condition expression, if needed. The next comparison to talk about is the between condition expression. I'm going to write a query that returns all the songs written by the artist David Bowie with song titles that start between the letters A and M. Those values are going to be coming from values4 here. Let's go ahead and change that to M. Save this. And then, we'll go ahead and write our query. Starting our query the same aws dynamodb query, providing the --table-name, and then the --key-condition-expression, which again will be Artist = :v1 AND SongTitle BETWEEN, that's the keyword we're looking at, :v2, which will be A, AND :v3, which will be M. We then have to provide those - -expression-attribute-values coming from the file, inputValues, and this time, values4.json. And then, I will go ahead and hit Enter, which we can see returns all of the songs that are released by David Bowie starting with the letters between A and M. The next query I want to write uses a comparison operator, a simple greater than or less than. But this time, we're going to apply it to an attribute that is not a key, and therefore, we will have to run a query and then a filter. Let's take a look at the JSON file for that query. That is in values5, and here we're going to be using U2 as the artist and 2000 as the release year. This query is going to return all the songs released by U2 after the year 2000. So, this query looks like this - aws dynamodb query, providing that --table-name of Music, and then we provide the --key-condition-expression, where again we are comparing the artist's name is = :v1, and then we'll provide the filter, which is going to be looking at the release year. So filter-expression, this is looking at Released, which is the attribute name, which is > :v2. And then, we'll provide the --expression-attribute-values coming from, again, a file in the inputValues folder, this time pointing at values5.json. We can see here that it only returned the songs from U2 that were released after the year 2000. There was also support for doing comparisons for less than, greater than or equal to, or less than or equal to. In this video, to keep it simple, we were writing queries against our table without secondary indexes, which means we had to do scans and filters, which are less performant than a query. So to make our table more performant in the future, we would have to design the table to make use of secondary indexes, and we could then use those same comparison operators and functions that we used in this video, but hopefully more efficiently by avoiding so much filtering and scanning. Alright, that's it for now, and we'll see you next time.
