Menu
Amazon DynamoDB
Getting Started Guide (API Version 2012-08-10)

Step 6: Work with a Secondary Index

Without an index, you can query for items based on primary key. You can add indexes to your table depending on your query patterns. DynamoDB supports two different kinds of indexes:

  • Global secondary index — an index with a partition key and sort key that can be different from those on the table. You can create or delete a global secondary index on a table at any time.

  • Local secondary index — an index that has the same partition key as the primary key of the table, but a different sort key. You can only create a local secondary index when you create a table; when you delete the table, the local secondary index is also deleted.

For more information about these indexes, go to Improving Data Access with Secondary Indexes in DynamoDB in the Amazon DynamoDB Developer Guide.

In this step, you add a secondary index to the Music table. Then, you then query and scan the index, in the same way as you would query or scan a table. In this tutorial, you create and use a global secondary index.

Step 6.1: Create a Global Secondary Index

The Music table has a primary key made of Artist (partition key) and SongTitle (sort key). Now suppose you want to query this table by Genre and find all of the Country songs. Searching on the primary key does not help in this case. To do this, we build a secondary index with Genre as the partition key.

To make this interesting, we use the Price attribute as the sort key. So you can now run a query to find all Country songs with Price less than 0.99.

You can add an index at the time that you create a table or later using the UpdateTable operation.

  1. Replace everything in the left side of the DynamoDB JavaScript shell window with the following code:

    
    var params = {
        TableName: "Music",
        AttributeDefinitions:[
            {AttributeName: "Genre", AttributeType: "S"},
            {AttributeName: "Price", AttributeType: "N"}
        ],
        GlobalSecondaryIndexUpdates: [
            {
                Create: {
                    IndexName: "GenreAndPriceIndex",
                    KeySchema: [
                        {AttributeName: "Genre", KeyType: "HASH"},  //Partition key
                        {AttributeName: "Price", KeyType: "RANGE"},  //Sort key
                    ],
                    Projection: {
                        "ProjectionType": "ALL"
                    },
                    ProvisionedThroughput: {
                        "ReadCapacityUnits": 1,"WriteCapacityUnits": 1
                    }
                }
            }
        ]
    };
    
    dynamodb.updateTable(params, function(err, data) {
        if (err)
            console.log(JSON.stringify(err, null, 2));
        else
            console.log(JSON.stringify(data, null, 2));
    });
    
    

    In the code:

    • AttributeDefinitions lists data types of attributes that are later defined as the partition key and sort key of the index.

    • GlobalSecondaryIndexUpdates specifies the index operations. You can create index, update index, or delete an index.

    • The ProvisionedThroughput parameter is required, but the downloadable version of DynamoDB ignores it.

  2. Click the play button arrow to run the code.

    In the response, take note of the IndexStatus. Its value should be CREATING, which indicates that the index is being built. The new should be available for use within a few seconds.

Step 6.2: Query the Index

Now we use the index to query for all Country songs. The index has all of the data you need, so you query the index and not the table.

You use the same Query operation to query a table (see Step 5: Query and Scan the Table) or an index on the table. When you query an index you specify both the table name and the index name.

  1. Replace everything in the left side of the DynamoDB JavaScript shell window with the following code:

    
    var params = {
        TableName: "Music",
        IndexName: "GenreAndPriceIndex",
        KeyConditionExpression: "Genre = :genre",
        ExpressionAttributeValues: {
            ":genre": "Country"
        },
        ProjectionExpression: "SongTitle, Price"
    };
    
    docClient.query(params, function(err, data) {
        if (err)
            console.log(JSON.stringify(err, null, 2));
        else
            console.log(JSON.stringify(data, null, 2));
    });
    
    
  2. Click the play button arrow to run the code. Only the Country songs are returned.

  3. Now let us query for Country songs that cost more than two dollars. Here you specify both the partition key and sort key values for index. Modify the params object so that it looks like this:

    
    var params = {
        TableName: "Music",
        IndexName: "GenreAndPriceIndex",
        KeyConditionExpression: "Genre = :genre and Price > :price",
        ExpressionAttributeValues: {
            ":genre": "Country",
            ":price": 2.00
        },
        ProjectionExpression: "SongTitle, Price"
    };
    
  4. Click the play button arrow to run the code. This query uses both of the index key attributes (Genre and Price), returning only the Country songs that cost more than 2.00.

Step 6.3: Scan the Index

You can scan an index (using the Scan operation) in the same way that you scan a table. When scanning an index, you provide both the table name and index name.

In this example, we scan the entire global secondary index you created, but we'll retrieve specific attributes only.

  1. Replace everything in the left side of the DynamoDB JavaScript shell window with the following code:

    
    var params = {
        TableName: "Music",
        IndexName: "GenreAndPriceIndex",
        ProjectionExpression: "Genre, Price, SongTitle, Artist, AlbumTitle"
    };
    
    docClient.scan(params, function(err, data) {
        if (err)
            console.log(JSON.stringify(err, null, 2));
        else
            console.log(JSON.stringify(data, null, 2));
    });
    
    
  2. Click the play button arrow to run the code. All of the items in the index are returned.

  3. (Optional) Note that there are only four items in the index (Count), but there are five items in the table. The reason is that one of the items does not have a Price attribute, so that item was not included in GenreAndPriceIndex.

    Which of the songs in Music items does not have a Price attribute? Can you determine which one it is?