# 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](https://help.qlik.com/en-US/stitch/replication/extractions/data-selection/setting-tables-and-columns-to-replicate) 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](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#feature-availability)

* [What projection expressions are](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#what-are-projection-queries)

* [The requirements for projection expressions in Stitch](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#projection-query-stitch-requirements)

* [How to define a projection expression in Stitch](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#defining-projection-query-in-stitch)

* [Some example projection expressions](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#example-projection-queries)

* [How to troubleshoot projection expression errors](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#error-troubleshooting)

* [Additional resources for projection expressions](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#projection-query-resources)

## Feature availability

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

| **Integration version** | **Availability** |
| --- | --- |
| **[v1](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb)** | the Success icon<br><br>Field selection via projection queries is supported for this integration version.<br><br><br>Supported |

## What are projection expressions?

In DynamoDB, the default for queries is to return all fields in matching tables. A [projection expression](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ProjectionExpressions.html) 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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ExpressionAttributeNames.html)

## 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](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#error-troubleshooting).

## Defining a projection expression in Stitch

* [Adding a new projection expression](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#adding-new-projection-query)

* [Modifying an existing projection expression](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#modifying-existing-projection-query)

### 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:

   ![The DynamoDB Table Details page in Stitch.](https://help.qlik.com/en-US/stitch/images/integrations/dynamodb-table-details.png)The DynamoDB Table Details page in Stitch.The DynamoDB Table Details page in Stitch.

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:

   ![The Projection expression field in the Table Settings page in Stitch.](https://help.qlik.com/en-US/stitch/images/integrations/dynamodb-projection-expression.png)The Projection expression field in the Table Settings page in Stitch.The Projection expression field in the Table Settings page in Stitch.

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](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#adding-new-projection-query), 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](https://help.qlik.com/en-US/stitch/replication/replication-keys#resetting-replication-keys) 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](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#example-table-data)

* [Return only specified fields](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#return-only-specified-fields)

* [Return specified fields in a map element](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#return-specified-fields-in-a-map-element)

* [Return specified fields in a list element](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#return-specified-fields-in-a-list-element)

* [Return a field using an expression attribute](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#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:

| **name [pk] (string)** | **is\_active (boolean)** | **details (object)** | **acquaintances (array)** | **comment (string)** |
| --- | --- | --- | --- | --- |
| Finn | true | age: 15, type: human | <ul> <li>Jake</li> <li>Ice King</li></ul> | Also known as Finn the Human |
| Jake | true | age: 6, type: dog | <ul> <li>Finn</li> <li>Lady</li></ul> | Magical dog |
| Bubblegum | false | age: 16, type: princess | <ul> <li>Finn</li> <li>Bubblegum</li></ul> | Rules over the Candy Kingdom |
| Lady | true | age: 50, type: unicorn | <ul> <li>Jake</li> <li>Finn</li></ul> |  |
| Ice King | false | age: 900, type: king | <ul> <li>Finn</li> <li>Bubblegum</li></ul> |  |

### 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.

| **Projection query** | ```<br>name, is_active<br>``` |
| --- | --- |
| **SQL** | ```<br>SELECT name,<br>is_active<br>FROM customers<br>``` |
| **Results** | <table><tr><td><strong>name</strong></td><td><strong>is_active</strong></td></tr><tr><td><p>Finn</p></td><td><p>true</p></td></tr><tr><td><p>Jake</p></td><td><p>true</p></td></tr><tr><td><p>Bubblegum</p></td><td><p>false</p></td></tr><tr><td><p>Lady</p></td><td><p>true</p></td></tr><tr><td><p>Ice King</p></td><td><p>false</p></td></tr></table> |

### Return specified fields in a map element

Using [dot notation](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.Attributes.html#Expressions.Attributes.NestedElements.AccessingListElements), 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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.Attributes.html) for more examples of dot notation for map elements.

| **Projection query** | ```<br>name, details.age, details.type<br>``` |
| --- | --- |
| **SQL** | In destinations - like Snowflake - that also use dot notation to query nested data, the query might look like this:<br><br>```<br>SELECT name,<br>"details.age",<br>"details.type"<br>FROM customers<br>``` |
| **Results** | <table><tr><td><strong>name</strong></td><td><strong>details</strong></td></tr><tr><td><p>Finn</p></td><td><p>age: 15, type: human</p></td></tr><tr><td><p>Jake</p></td><td><p>age: 6, type: dog</p></td></tr><tr><td><p>Bubblegum</p></td><td><p>age: 16, type: princess</p></td></tr><tr><td><p>Lady</p></td><td><p>age: 50, type: unicorn</p></td></tr><tr><td><p>Ice King</p></td><td><p>age: 900, type: king</p></td></tr></table> |

### Return specified fields in a list element

To access an element in a list, use [the dereference operator](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.Attributes.html#Expressions.Attributes.NestedElements.AccessingListElements) (`[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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.Attributes.html) for more examples of accessing fields in lists.

| **Projection query** | ```<br>name, acquaintances[1]<br>``` |
| --- | --- |
| **Results** | <table><tr><td><strong>name</strong></td><td><strong>acquaintances</strong></td></tr><tr><td><p>Finn</p></td><td><ul> <li>Ice King</li></ul></td></tr><tr><td><p>Jake</p></td><td><ul> <li>Lady</li></ul></td></tr><tr><td><p>Bubblegum</p></td><td><ul> <li>Bubblegum</li></ul></td></tr><tr><td><p>Lady</p></td><td><ul> <li>Finn</li></ul></td></tr><tr><td><p>Ice King</p></td><td><ul> <li>Bubblegum</li></ul></td></tr></table> |

### 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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ExpressionAttributeNames.html) for more examples of expression attributes.

| **Projection query** | ```<br>name, is_active, #c<br>``` |
| --- | --- |
| **Expression attributes** | ```<br>"#c":"comment"<br>``` |
| **Results** | <table><tr><td><strong>name</strong></td><td><strong>is_active</strong></td><td><strong>comment</strong></td></tr><tr><td><p>Finn</p></td><td><p>true</p></td><td><p>Also known as Finn the Human</p></td></tr><tr><td><p>Jake</p></td><td><p>true</p></td><td><p>Magical dog</p></td></tr><tr><td><p>Bubblegum</p></td><td><p>false</p></td><td><p>Rules over the Candy Kingdom</p></td></tr><tr><td><p>Lady</p></td><td><p>true</p></td><td></td></tr><tr><td><p>Ice King</p></td><td><p>false</p></td><td></td></tr></table> |

## Error troubleshooting

If a table’s projection expression doesn’t meet [Stitch’s requirements](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions#projection-query-stitch-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](https://help.qlik.com/en-US/stitch/troubleshooting/integrations/database-extraction-error-reference#amazon-dynamodb-server-error-reference) for DynamoDB extraction errors and help resolving them.

## Resources

* [DynamoDB projection expression documentation](https://help.qlik.com/en-US/stitch/integrations/databases/amazon-dynamodb/field-selection-using-projection-expressions)