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

Step 4: Query and Scan the Data

You can use the Query method to retrieve data from a table. You must specify a partition key value; the sort key is optional.

The primary key for the Movies table is composed of the following:

  • year – The partition key. The attribute type is number. 

  • title – The sort key. The attribute type is string.

To find all movies released during a year, you need to specify only the year partition-key attribute. You can add the title sort-key attribute to retrieve a subset of movies based on some condition (on the sort-key attribute), such as finding movies released in 2014 that have a title starting with the letter "A".

In addition to Query, there is also a Scan method that can retrieve all of the table data.

To learn more about querying and scanning data, see Query and Scan in the Amazon DynamoDB Developer Guide.

Step 4.1: Query

The C# code included in this step performs the following queries:

  • Retrieves all movies release in year 1985.

  • Retrieves all movies released in year 1992, with title beginning with the letter "A" through the letter "L".

  1. Copy the following program into the Program.cs file, replacing its current contents.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    using Amazon;
    using Amazon.DynamoDBv2;
    using Amazon.DynamoDBv2.Model;
    using Amazon.DynamoDBv2.DocumentModel;
    
    namespace DynamoDB_intro
    {
      class Program
      {
        static string commaSep = ", ";
        static string movieFormatString = "    \"{0}\", lead actor: {1}, genres: {2}";
    
        static void Main( string[ ] args )
        {
          // Get an AmazonDynamoDBClient for the local DynamoDB database
          AmazonDynamoDBClient client = GetLocalClient( );
    
          // Get a Table object for the table that you created in Step 1
          Table table = GetTableObject( client, "Movies" );
          if( table == null )
            goto PauseForDebugWindow;
    
          /*-----------------------------------------------------------------------
           *  4.1.1:  Call Table.Query to initiate a query for all movies with
           *          year == 1985, using an empty filter expression.
           *-----------------------------------------------------------------------*/
          Search search;
          try { search = table.Query( 1985, new Expression( ) ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: 1985 query failed because: " + ex.Message );
            goto PauseForDebugWindow;
          }
    
          // Display the titles of the movies returned by this query
          List<Document> docList = new List<Document>( );
          Console.WriteLine( "\n All movies released in 1985:" +
                             "\n-----------------------------------------------" );
          do
          {
            try { docList = search.GetNextSet( ); }
            catch( Exception ex )
            {
              Console.WriteLine( "\n Error: Search.GetNextStep failed because: " + ex.Message );
              break;
            }
            foreach( var doc in docList )
              Console.WriteLine( "    " + doc["title"] );
          } while( !search.IsDone );
    
    
          /*-----------------------------------------------------------------------
           *  4.1.2a:  Call Table.Query to initiate a query for all movies where
           *           year equals 1992 AND title is between "B" and "Hzzz",
           *           returning the lead actor and genres of each.
           *-----------------------------------------------------------------------*/
          Primitive y_1992  = new Primitive( "1992", true );
          QueryOperationConfig config = new QueryOperationConfig( );
          config.Filter = new QueryFilter( );
          config.Filter.AddCondition( "year", QueryOperator.Equal, new DynamoDBEntry[ ] { 1992 } );
          config.Filter.AddCondition( "title", QueryOperator.Between, new DynamoDBEntry[ ] { "B", "Hzz" } );
          config.AttributesToGet = new List<string> { "title", "info" };
          config.Select = SelectValues.SpecificAttributes;
    
          try { search = table.Query( config ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: 1992 query failed because: " + ex.Message );
            goto PauseForDebugWindow;
          }
    
          // Display the movie information returned by this query
          Console.WriteLine( "\n\n Movies released in 1992 with titles between \"B\" and \"Hzz\" (Document Model):" +
                               "\n-----------------------------------------------------------------------------" );
          docList = new List<Document>( );
          Document infoDoc;
          do
          {
            try { docList = search.GetNextSet( ); }
            catch( Exception ex )
            {
              Console.WriteLine( "\n Error: Search.GetNextStep failed because: " + ex.Message );
              break;
            }
            foreach( var doc in docList )
            {
              infoDoc = doc["info"].AsDocument( );
              Console.WriteLine( movieFormatString,
                                 doc["title"],
                                 infoDoc["actors"].AsArrayOfString( )[0],
                                 string.Join( commaSep, infoDoc["genres"].AsArrayOfString( ) ) );
            }
          } while( !search.IsDone );
    
    
          /*-----------------------------------------------------------------------
           *  4.1.2b:  Call AmazonDynamoDBClient.Query to initiate a query for all
           *           movies where year equals 1992 AND title is between M and Tzz,
           *           returning the genres and the lead actor of each.
           *-----------------------------------------------------------------------*/
          QueryRequest qRequest = new QueryRequest
          {
            TableName = "Movies",
            ExpressionAttributeNames = new Dictionary<string,string>
            {
              { "#yr", "year" }
            },
            ExpressionAttributeValues = new Dictionary<string,AttributeValue>
            {
              { ":y_1992",  new AttributeValue { N = "1992" } },
              { ":M",       new AttributeValue { S = "M"    } },
              { ":Tzz",     new AttributeValue { S = "Tzz"  } }
            },
            KeyConditionExpression = "#yr = :y_1992 and title between :M and :Tzz",
            ProjectionExpression = "title, info.actors[0], info.genres"
          };
    
          QueryResponse qResponse;
          try { qResponse = client.Query( qRequest ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: Low-level query failed, because: " + ex.Message );
            goto PauseForDebugWindow;
          }
    
          // Display the movie information returned by this query
          Console.WriteLine( "\n\n Movies released in 1992 with titles between \"M\" and \"Tzz\" (low-level):" +
                               "\n-------------------------------------------------------------------------" );
          foreach( Dictionary<string, AttributeValue> item in qResponse.Items)
          {
            Dictionary<string,AttributeValue> info = item["info"].M;
            Console.WriteLine( movieFormatString,
                               item["title"].S,
                               info["actors"].L[0].S,
                               GetDdbListAsString( info["genres"].L ) );
          }
    
          // Keep the console open if in Debug mode...
    PauseForDebugWindow:
          Console.Write( "\n\n ...Press any key to continue" );
          Console.ReadKey( );
          Console.WriteLine( );
        }
    
        public static string GetDdbListAsString( List<AttributeValue> strList )
        {
          StringBuilder sb = new StringBuilder( );
          string str = null;
          AttributeValue av;
          for( int i = 0; i < strList.Count; i++ )
          {
            av = strList[i];
            if( av.S != null )
              str = av.S;
            else if( av.N != null )
              str = av.N;
            else if( av.SS != null )
              str = string.Join( commaSep, av.SS.ToArray( ) );
            else if( av.NS != null )
              str = string.Join( commaSep, av.NS.ToArray( ) );
            if( str != null )
            {
              if( i > 0 )
                sb.Append( commaSep );
              sb.Append( str );
            }
          }
          return( sb.ToString( ) );
        }
    
        public static AmazonDynamoDBClient GetLocalClient( )
        {
          // First, set up a DynamoDB client for DynamoDB Local
          AmazonDynamoDBConfig ddbConfig = new AmazonDynamoDBConfig( );
          ddbConfig.ServiceURL = "http://localhost:8000";
          AmazonDynamoDBClient client;
          try { client = new AmazonDynamoDBClient( ddbConfig ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: failed to create a DynamoDB client; " + ex.Message );
            return ( null );
          }
          return ( client );
        }
    
    
        public static Table GetTableObject( AmazonDynamoDBClient client, string tableName )
        {
          Table table = null;
          try { table = Table.LoadTable( client, tableName ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: failed to load the 'Movies' table; " + ex.Message );
            return ( null );
          }
          return ( table );
        }
      }
    }
    

    Note

    • In the first query, for all movies released in 1985, an empty expression indicates that no filtering on the sort-key part of the primary key is desired.

    • In the second query, which uses the AWS SDK for .NET Document Model to query for all movies released in 1992 with titles starting with the letters A through L, we can only query for top-level attributes, and so must retrieve the entire info attribute. Our display code then accesses the nested attributes we're interested in.

    • In the third query, we use the low-level AWS SDK for .NET API, which gives more control over what is returned. Here, we are able to retrieve only those nested attributes within the info attribute that we are interested in, namely info.genres and info.actors[0].

  2. Compile and run the program.

Note

The preceding program shows how to query a table by its primary key attributes. In DynamoDB, you can also optionally create one or more secondary indexes on a table, and query those indexes in the same way that you query a table. Secondary indexes give your applications additional flexibility by allowing queries on non-key attributes. For more information about secondary indexes, see Secondary Indexes in the Amazon DynamoDB Developer Guide.

Step 4.2: Scan

The Scan method reads every item in the entire table, and returns all of the data in the table. You can provide an optional filter_expression, so that only the items matching your criteria are returned. However, note that the filter is only applied after the entire table has been scanned.

The following program scans the entire Movies table, which contains approximately 5,000 items. The scan specifies the optional filter to retrieve only the movies from the 1950s (approximately 100 items), and discard all of the others.

  1. Copy the following program into the Program.cs file, replacing its current contents.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    using Amazon;
    using Amazon.DynamoDBv2;
    using Amazon.DynamoDBv2.Model;
    using Amazon.DynamoDBv2.DocumentModel;
    
    namespace DynamoDB_intro
    {
      class Program
      {
        static void Main( string[ ] args )
        {
          // Get an AmazonDynamoDBClient for the local DynamoDB database
          AmazonDynamoDBClient client = GetLocalClient( );
    
          // Get a Table object for the table that you created in Step 1
          Table table = GetTableObject( client, "Movies" );
          if( table == null )
            goto PauseForDebugWindow;
    
    
          /*-----------------------------------------------------------------------
           *  4.2a:  Call Table.Scan to return the movies released in the 1950's,
           *         displaying title, year, lead actor and lead director.
           *-----------------------------------------------------------------------*/
          ScanFilter filter = new ScanFilter( );
          filter.AddCondition( "year", ScanOperator.Between, new DynamoDBEntry[ ] { 1950, 1959 } );
          ScanOperationConfig config = new ScanOperationConfig
          {
            AttributesToGet = new List<string> { "year, title, info" },
            Filter = filter
          };
          Search search = table.Scan( filter );
    
          // Display the movie information returned by this query
          Console.WriteLine( "\n\n Movies released in the 1950's (Document Model):" +
                             "\n--------------------------------------------------" );
          List<Document> docList = new List<Document>( );
          Document infoDoc;
          string movieFormatString = "    \"{0}\" ({1})-- lead actor: {2}, lead director: {3}";
          do
          {
            try { docList = search.GetNextSet( ); }
            catch( Exception ex )
            {
              Console.WriteLine( "\n Error: Search.GetNextStep failed because: " + ex.Message );
              break;
            }
            foreach( var doc in docList )
            {
              infoDoc = doc["info"].AsDocument( );
              Console.WriteLine( movieFormatString,
                                 doc["title"],
                                 doc["year"],
                                 infoDoc["actors"].AsArrayOfString( )[0],
                                 infoDoc["directors"].AsArrayOfString( )[0] );
            }
          } while( !search.IsDone );
    
    
          /*-----------------------------------------------------------------------
           *  4.2b:  Call AmazonDynamoDBClient.Scan to return all movies released
           *         in the 1960's, only downloading the title, year, lead
           *         actor and lead director attributes.
           *-----------------------------------------------------------------------*/
          ScanRequest sRequest = new ScanRequest
          {
            TableName = "Movies",
            ExpressionAttributeNames = new Dictionary<string, string>
            {
              { "#yr", "year" }
            },
            ExpressionAttributeValues = new Dictionary<string, AttributeValue>
            {
              { ":y_a", new AttributeValue { N = "1960" } },
              { ":y_z", new AttributeValue { N = "1969" } },
            },
            FilterExpression = "#yr between :y_a and :y_z",
            ProjectionExpression = "#yr, title, info.actors[0], info.directors[0]"
          };
    
          ScanResponse sResponse;
          try { sResponse = client.Scan( sRequest ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: Low-level scan failed, because: " + ex.Message );
            goto PauseForDebugWindow;
          }
    
          // Display the movie information returned by this scan
          Console.WriteLine( "\n\n Movies released in the 1960's (low-level):" +
                               "\n-------------------------------------------" );
          foreach( Dictionary<string, AttributeValue> item in sResponse.Items )
          {
            Dictionary<string,AttributeValue> info = item["info"].M;
            Console.WriteLine( movieFormatString,
                               item["title"].S,
                               item["year"].N,
                               info["actors"].L[0].S,
                               info["directors"].L[0].S );
          }
    // Keep the console open if in Debug mode...
    PauseForDebugWindow:
          Console.Write( "\n\n ...Press any key to continue" );
          Console.ReadKey( );
          Console.WriteLine( );
        }
    
        public static AmazonDynamoDBClient GetLocalClient( )
        {
          // First, set up a DynamoDB client for DynamoDB Local
          AmazonDynamoDBConfig ddbConfig = new AmazonDynamoDBConfig( );
          ddbConfig.ServiceURL = "http://localhost:8000";
          AmazonDynamoDBClient client;
          try { client = new AmazonDynamoDBClient( ddbConfig ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: failed to create a DynamoDB client; " + ex.Message );
            return ( null );
          }
          return ( client );
        }
    
    
        public static Table GetTableObject( AmazonDynamoDBClient client, string tableName )
        {
          Table table = null;
          try { table = Table.LoadTable( client, tableName ); }
          catch( Exception ex )
          {
            Console.WriteLine( "\n Error: failed to load the 'Movies' table; " + ex.Message );
            return ( null );
          }
          return ( table );
        }
      }
    }
    

    In the code, note the following:

    • The first scan uses the AWS SDK for .NET Document Model to scan the Movies table and return movies released in the 1950's. because the Document Model does not support nested attributes in the AttributesToGet field, we must download the entire info attribute to have access to the lead actor and director.

    • The second scan uses the AWS SDK for .NET low-level API to scan the Movies table and return movies released in the 1960's. In this case, we can download only those attribute values in info that we are interested in, namely info.actors[0] and info.directors[0].

  2. Compile and run the program.

Note

You can also use the Scan operation with any secondary indexes that you have created on the table. For more information about secondary indexes, see Secondary Indexes in the Amazon DynamoDB Developer Guide.

Next Step

Step 5: (Optional) Delete the Table