Selecting Amazon DynamoDB Fields Using Projection Expressions

In Stitch’s Amazon DynamoDB integration, projection expressions serve as a method for selecting individual fields for replication. This is equivalent to column selection in other integrations.

By specifying a projection expression, you can replicate only the data you need for each table in your DynamoDB integration.

In this guide, we’ll cover:

• What versions of the DynamoDB integration this feature is available for

• What projection expressions are

• The requirements for projection expressions in Stitch

• How to define a projection expression in Stitch

• Some example projection expressions

• How to troubleshoot projection expression errors

• Additional resources for projection expressions

Feature availability

The following table indicates the availability of Stitch’s DynamoDB projection expression feature for each version of the DynamoDB integration.

What are projection expressions?

In DynamoDB, the default for queries is to return all fields in matching tables. A projection expression is used to specify or restrict the data returned in query results. By specifying a projection expression, you can specify the fields you want to return or exclude.

For example: Not specifying a expression in Stitch is similar to SELECT * in SQL. If you wanted to only return a subset of fields, you’d specify them in the SELECT clause:

SELECT name,
     is_active
FROM customers

The DynamoDB integration supports the use of expression attributes in projection expressions. For more information, refer to DynamoDB’s documentation

Projection expression requirements for Stitch

Projection expressions are compatible with any of Stitch’s Replication Methods, including Log-based Incremental.

Projection expressions must adhere to the following:

• Include the table’s hash/partition key (Primary Key). If you’re unsure which field is the partition key for a table, sign into your Amazon Web Services (AWS) account and look at the Table Details section for the table. The field in the Primary partition key field is the table’s Primary Key.

• Cannot include conditional (condition) expressions. Stitch’s DynamoDB integration doesn’t currently support using conditions in projection expressions.

Projection expressions that don’t meet the above criteria will result in errors during extraction.

Defining a projection expression in Stitch

• Adding a new projection expression

• Modifying an existing projection expression

Adding a new projection expression

When you set a table to replicate in Stitch, you can define a projection expression for the table in the Table Settings page.

1. In the DynamoDB integration, click the Tables to Replicate tab.
2. Navigate to the desired table.

3. Click the checkbox to the left of the table to set it to replicate. This will also open the Table Details page:

   

4. Click the View Table Settings button.
5. On the Table Settings page, scroll down to the Fields to Replicate section.

6. Enter the projection expression you want the table to use in the Projection expression field:

   

7. Click Update Settings to save your changes.

8. If your projection expression requires expression attributes, enter them in the Expression Attributes field.

Stitch will use the table’s projection expression during the next scheduled replication job, even if a job is currently in progress.

Modifying an existing projection expression

To modify an existing projection expression, follow the steps in the previous section, modifying the expression as needed. When finished, click Update Settings to save your changes.

Stitch will use the table’s projection expression during the next scheduled replication job, even if a job is currently in progress.

Note: Modifying a projection expression won’t trigger a full re-replication of a table. If the table uses a type of incremental replication, you’ll need to manually reset the table to backfill the values for any new fields.

Example projection expressions

In this section, we’ll look at some example projection expressions and their SQL equivalents.

• Example table data

• Return only specified fields

• Return specified fields in a map element

• Return specified fields in a list element

• Return a field using an expression attribute

Example table data

The examples use data from a table named customers, which uses the name field as a Primary Key. This table contains the following records:

Return only specified fields

Return only the specified fields (name, is_active) in the customers table. If including multiple fields, separate them with a comma.

name, is_active

SELECT name,
       is_active
  FROM customers

Return specified fields in a map element

Using dot notation, return specified fields in a map element. This is formatted as <map_element_name>.<field>.

In this example, the expression would return the top-level name field and age and type fields from the details map.

Refer to DynamoDB’s documentation for more examples of dot notation for map elements.

name, details.age, details.type

SELECT name,
       "details.age",
       "details.type"
  FROM customers

Return specified fields in a list element

To access an element in a list, use the dereference operator ([n]), where n is the number of the element in the list. This is formatted as <list_element_name>[n].

In this example, the expression would return the top-level name field and second element from the acquaintances list.

Refer to DynamoDB’s documentation for more examples of accessing fields in lists.

name, acquaintances[1]

Return a field using an expression attribute

Use an expression attribute to reference a field when the field name cannot be used in a projection expression. In this example, comment is a reserved word and cannot be used in a query. You can define an expression attribute name, starting with a #, that will be used as a placeholder for the field name.

Refer to DynamoDB’s documentation for more examples of expression attributes.

name, is_active, #c

"#c":"comment"

Error troubleshooting

If a table’s projection expression doesn’t meet Stitch’s requirements, a critical error will arise during Extraction. Extractions will not be successful until the issue is resolved.

Refer to the Database integration extraction error reference for DynamoDB extraction errors and help resolving them.

Resources

• DynamoDB projection expression documentation

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.