# Selecting MongoDB Fields Using Projection Queries

In Stitch’s MongoDB integration, projection queries 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 query, you can replicate only the data you need for each collection in your MongoDB integration.

In this guide, we’ll cover:

* [What versions of the MongoDB integration this feature is available for](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#feature-availability)

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

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

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

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

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

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

## Feature availability

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

| **Integration version** | **Availability** |
| --- | --- |
| **[v3](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/v3)** | the Success icon<br><br>Field selection via projection queries is supported for this integration version.<br><br><br>Supported |
| **[v2](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/v2)** | the Success icon<br><br>Field selection via projection queries is supported for this integration version.<br><br><br>Supported |
| **[v1](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/v1)** | the Success icon<br><br>Field selection via projection queries is supported for this integration version.<br><br><br>Supported |
| **[v11-01-2016](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/v11-01-2016)** | the Remove icon<br><br>Field selection via projection queries is not supported for this integration version.<br><br><br>Unsupported |

## What are projection queries?

In MongoDB, the default for queries is to return all fields in matching documents. A [projection query](https://docs.mongodb.com/v4.0/tutorial/project-fields-from-query-results/) is used to specify or restrict the data returned in query results. By specifying a projection query, you can specify the fields you want to return or exclude.

For example: Not specifying a query 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
```

## Projection query requirements for Stitch

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

Projection queries entered into Stitch must adhere to the following:

* **Cannot exclude the `_id` field.** This is equivalent to `{ "_id": 0 }`. Stitch uses this field for replication.
* **Cannot specify conditional criteria.** In SQL, this is equivalent to specifying a `WHERE` clause. For example: `{ "is_active": true }` is equal to `WHERE is_active = true`. This type of projection query is not currently supported in Stitch.
* **Cannot combine inclusion and exclusion statements.** This means that a projection query can’t both include and exclude fields. For example: `{ "name": 0, "type": 1 }`
* **Must be valid JSON.** Projection queries must be valid JSON. Keys and string values must be enclosed in double quotes (`"`). You can use [JSONFormatter](https://jsonformatter.curiousconcept.com/) to validate the projection query before entering it into Stitch.
* **Must exclude the `_acl` field** if using access control list plugins with MongoDB. If the projection query is inclusion-based, then you must remove it.

Projection queries that don’t meet the above criteria will result in [errors during extraction](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#error-troubleshooting).

## Defining a projection query in Stitch

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

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

### Adding a new projection query

When you set a collection to replicate in Stitch, you can define a projection query for the collection in the **Collection Settings** page.

1. In the MongoDB integration, click the **Collections to Replicate** tab.

2. Navigate to the desired collection.

3. Click the checkbox to the left of the collection to set it to replicate. This will also open the **Collection Details** page:

   ![The MongoDB Collection Details page in Stitch.](https://help.qlik.com/en-US/stitch/images/integrations/mongodb-collection-details.png)The MongoDB Collection Details page in Stitch.The MongoDB Collection Details page in Stitch.

4. Click the **View Collection Settings** button.

5. On the **Collection Settings** page, scroll down to the **Fields to Replicate** section.

6. Enter the projection query you want the collection to use in the **Projection query** field:

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

   **Note**: Projection queries include the `_id` field by default, so you don’t need to specify it in your query.

7. Click **Update Settings** to save your changes.

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

### Modifying an existing projection query

To modify an existing projection query, follow the steps [in the previous section](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#adding-new-projection-query), modifying the query as needed. When finished, click **Update Settings** to save your changes.

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

**Note**: Modifying a projection query won’t trigger a full re-replication of a collection. If the collection uses a type of incremental replication, you’ll need to [manually reset the collection](https://help.qlik.com/en-US/stitch/replication/replication-keys#resetting-replication-keys) to backfill the values for any new fields.

## Example projection queries

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

* [Example collection data](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#example-collection-data)

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

* [Return all except excluded fields](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#return-all-except-excluded-fields)

* [Return specified fields in an embedded document](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#return-specified-fields-in-an-embedded-document)

* [Return specified fields in an embedded document in an array](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#return-specified-fields-in-an-embedded-document-in-an-array)

### Example collection data

The examples use data from a collection named `customers`, which contains the following documents:

| **name (string)** | **is\_active (boolean)** | **details (object)** | **acquaintances (array)** |
| --- | --- | --- | --- |
| Finn | true | age: 15, type: human | <ul> <li>name: Jake, type: best_friend</li> <li>name: Ice King, type: nemesis</li></ul> |
| Jake | true | age: 6, type: dog | <ul> <li>name: Finn, type: best_friend</li> <li>name: Lady, type: spouse</li></ul> |
| Bubblegum | false | age: 16, type: princess | <ul> <li>name: Finn, type: friend</li> <li>name: Bubblegum, type: best_friend</li></ul> |
| Lady | true | age: 50, type: unicorn | <ul> <li>name: Jake, type: spouse</li> <li>name: Finn, type: friend</li></ul> |
| Ice King | false | age: 900, type: king | <ul> <li>name: Finn, type: nemesis</li> <li>name: Bubblegum, type: nemesis</li></ul> |

### Return only specified fields

Return only the specified fields (`name`, `is_active`) for documents in the `customers` collection. Fields are marked for inclusion by setting their value to `1` in the projection query.

| **Projection query** | ```<br>{ "name": 1, "is_active": 1 }<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 all except excluded fields

Return all fields except those that are excluded. Fields are marked for exclusion by setting their value to `0` in the projection query.

**Note**: The `_id` field cannot be excluded in projection queries added in Stitch, as Stitch requires it for replication.

In this example, the query would return only the `name` and `acquaintances` fields.

| **Projection query** | ```<br>{ "is_active": 0, "details": 0 }<br>``` |
| --- | --- |
| **Results** | <table><tr><td><strong>name</strong></td><td><strong>acquaintances</strong></td></tr><tr><td><p>Finn</p></td><td><ul> <li>name: Jake, type: best_friend</li> <li>name: Ice King, type: nemesis</li></ul></td></tr><tr><td><p>Jake</p></td><td><ul> <li>name: Finn, type: best_friend</li> <li>name: Lady, type: spouse</li></ul></td></tr><tr><td><p>Bubblegum</p></td><td><ul> <li>name: Finn, type: friend</li> <li>name: Bubblegum, type: best_friend</li></ul></td></tr><tr><td><p>Lady</p></td><td><ul> <li>name: Jake, type: spouse</li> <li>name: Finn, type: friend</li></ul></td></tr><tr><td><p>Ice King</p></td><td><ul> <li>name: Finn, type: nemesis</li> <li>name: Bubblegum, type: nemesis</li></ul></td></tr></table> |

### Return specified fields in an embedded document

Using [dot notation](https://docs.mongodb.com/v4.0/core/document/#dot-notation), return specified fields in an embedded document. This is formatted as `"<embedded_document_name>.<field>"`

In this example, the query would return the `name` and `name` and `type` fields from the `details` document.

Refer to [MongoDB’s documentation](https://docs.mongodb.com/v4.0/core/document/#embedded-documents) for more examples of dot notation for embedded documents.

| **Projection query** | ```<br>{ "name": 1, "details.name": 1, "details.type": 1 }<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.name",<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 an embedded document in an array

Using [dot notation](https://docs.mongodb.com/v4.0/core/document/#dot-notation), return specified fields in an embedded document contained in an array. This is formatted as `"<embedded_document_name>.<field>"`

In this example, the query would return the `name` and `name` and `type` fields from the documents in the `acquaintances` array.

Refer to [MongoDB’s documentation](https://docs.mongodb.com/v4.0/core/document/#arrays) for more examples of dot notation for embedded documents and arrays.

| **Projection query** | ```<br>{ "name": 1, "acquaintances.name": 1, "acquaintances.type": 1 }<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>"acquaintances.name",<br>"acquaintances.type"<br>FROM customers<br>``` |
| **Results** | <table><tr><td><strong>name</strong></td><td><strong>acquaintances</strong></td></tr><tr><td><p>Finn</p></td><td><ul> <li>name: Jake, type: best_friend</li> <li>name: Ice King, type: nemesis</li></ul></td></tr><tr><td><p>Jake</p></td><td><ul> <li>name: Finn, type: best_friend</li> <li>name: Lady, type: spouse</li></ul></td></tr><tr><td><p>Bubblegum</p></td><td><ul> <li>name: Finn, type: friend</li> <li>name: Bubblegum, type: best_friend</li></ul></td></tr><tr><td><p>Lady</p></td><td><ul> <li>name: Jake, type: spouse</li> <li>name: Finn, type: friend</li></ul></td></tr><tr><td><p>Ice King</p></td><td><ul> <li>name: Finn, type: nemesis</li> <li>name: Bubblegum, type: nemesis</li></ul></td></tr></table> |

## Error troubleshooting

If a collection’s projection query doesn’t meet [Stitch’s requirements](https://help.qlik.com/en-US/stitch/integrations/databases/mongodb/field-selection-using-projection-queries#projection-query-stitch-requirements), a critical error will arise during Extraction. Extractions will not be successful until the issue is resolved.

For a list of possible errors and how to resolve them, refer to the [MongoDB Extraction Errors reference](https://help.qlik.com/en-US/stitch/troubleshooting/integrations/database-extraction-error-reference#mongodb-error-reference).

## Resources

* [MongoDB projection query documentation](https://docs.mongodb.com/v4.0/tutorial/project-fields-from-query-results/)
* [MongoDB dot notation documentation](https://docs.mongodb.com/v4.0/core/document/#dot-notation)
* [MongoDB Extraction Errors reference](https://help.qlik.com/en-US/stitch/troubleshooting/integrations/database-extraction-error-reference#mongodb-error-reference)