miruky

Posted on Sep 15

Query a DynamoDB Table by an Alternate Key with a Global Secondary Index

#aws #dynamodb #database #tutorial

Introduction

Hi, I'm miruky.

An Amazon DynamoDB table is optimized around its primary key. When an application also needs an efficient query by another attribute, a global secondary index can provide a second key schema without requiring an application-managed copy of the table.

This Console run stores three orders by order_id and adds one index keyed by customer_id plus numeric order_rank. A query for customer-blue returns exactly two orders in rank order, even though neither value is the table partition key.

The table uses on-demand capacity, stores three small items, and performs a small scan and query. These operations and the index storage can be billable, so check the current DynamoDB pricing before running it.

1. Create the table and alternate index

This run uses the English AWS Console in United States (N. Virginia). The table and index are created in us-east-1, and every write and query targets that Region.

The header confirms United States (N. Virginia) while the DynamoDB Console is in English. This fixes the Region for the table, index, writes, and queries.

Open Amazon DynamoDB, search for miruky-dqtwglfgorkfznmw, and confirm no exact table name exists. Choose Create table and enter that generated name with order_id as a String partition key.

The exact filter for miruky-dqtwglfgorkfznmw returns no table. That empty result establishes the resource boundary before creation.

Choose Customize settings and keep On-demand capacity.

The table crop shows miruky-dqtwglfgorkfznmw, table key order_id as String, and on-demand capacity. It ends before the index editor so all four values remain readable at article width.

Add a global secondary index. Use miruky-rohwbyfggspfvnjr as the index name, customer_id as a String partition key, and order_rank as a Number sort key; project All attributes.

The GSI crop shows the generated index name, customer_id as String, order_rank as Number, and All projection. Keeping the base-table and index controls in separate images prevents the full creation form from becoming unreadable at DEV width.

Create the table, then continue only after its state becomes Active.

The table-status crop shows miruky-dqtwglfgorkfznmw as Active without the table ARN or creation metadata.

Check the generated index separately, then continue only after it is Active. An active table with an index still being created is not ready for the query below.

The index row shows miruky-rohwbyfggspfvnjr as Active. The table and GSI states remain in separate rectangles so no intervening ARN or metadata is required for the readiness proof.

The index key attributes are top-level scalar values. DynamoDB cannot use a document or set as an index key, and GSI reads are eventually consistent rather than strongly consistent.

2. Add three fixed items

Open Explore table items and create these three items in JSON view. The current Console expects DynamoDB JSON here, so each attribute carries an explicit S or N type wrapper.

{
  "order_id": { "S": "order-blue-1" },
  "customer_id": { "S": "customer-blue" },
  "order_rank": { "N": "10" },
  "status": { "S": "ready" }
}

{
  "order_id": { "S": "order-blue-2" },
  "customer_id": { "S": "customer-blue" },
  "order_rank": { "N": "20" },
  "status": { "S": "packed" }
}

{
  "order_id": { "S": "order-green-1" },
  "customer_id": { "S": "customer-green" },
  "order_rank": { "N": "15" },
  "status": { "S": "ready" }
}

The editor shows order_rank with an N wrapper and the identifiers with S wrappers. DynamoDB JSON writes a Number's lexical value as a string, so { "N": "10" } still stores a DynamoDB Number rather than a String. That distinction makes the index order numeric.

After all three writes, scan only this controlled table and verify the three order IDs are present. If the GSI is still catching up, wait briefly before running the index query rather than rewriting the items.

The controlled table view contains the three planned order IDs and no unrelated row. Two items use customer-blue; the control item uses customer-green.

3. See why the table key is not enough

In Scan or query items, choose Query and keep the target set to the base table. The required partition-key field is order_id, so the base table can efficiently retrieve an order when its order ID is known.

With the base table selected, the query form exposes order_id as the required partition key. customer_id is not available as a replacement key in this query target.

It cannot use customer_id as the base-table partition key because that attribute is not part of the table key schema. A scan with a filter could inspect items, but it would read items before filtering and is not the access pattern being demonstrated.

4. Query through the GSI

Change the query target from the table to index miruky-rohwbyfggspfvnjr. The partition-key field should change to customer_id, and the optional sort-key condition should use order_rank.

Selecting miruky-rohwbyfggspfvnjr changes the required partition-key field to customer_id and exposes order_rank as its sort key. The base-table data remains unchanged.

Enter customer-blue for the partition key and run the query. Do not add a sort-key filter; the numeric index sort key should order the two matching items as 10 and then 20 in the default ascending direction.

The query-control crop shows customer-blue, no sort-key condition, and an unchecked Sort descending control. That unchecked control keeps the default ascending order. The crop does not extend into the result rows.

Verify that the results are order-blue-1 at rank 10 and order-blue-2 at rank 20. The customer-green item should not appear because its index partition-key value is different.

The result crop shows two returned items: order-blue-1 at rank 10, followed by order-blue-2 at rank 20. No row with customer-green appears in the query result.

The All projection also returns the non-key status attribute. A narrower projection can reduce index storage and write cost when a real query needs fewer attributes.

Wrap-up

The base table remained keyed by unique order_id, while the GSI added an independent query path by customer_id. Querying customer-blue returned two matching orders in numeric order_rank order without scanning the table.

A GSI should follow a real access pattern, not be added speculatively. Its alternate key makes reads efficient, but each write to an item represented in the index also updates that index and contributes to index write-request and storage usage.

Thanks for reading this far.

See you in the next one.

Disclosure: This article was written with AI assistance and independently verified against the linked primary sources and observed results.

DEV Community