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

• What projection queries are

• The requirements for projection queries in Stitch

• How to define a projection query in Stitch

• Some example projection queries

• How to troubleshoot projection query errors

• Additional resources for projection queries

Feature availability

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

What are projection queries?

In MongoDB, the default for queries is to return all fields in matching documents. A projection query 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 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.

Defining a projection query in Stitch

• Adding a new projection query

• Modifying an 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:

   

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:

   

   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, 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 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

• Return only specified fields

• Return all except excluded fields

• Return specified fields in an embedded document

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

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.

{ "name": 1, "is_active": 1 }

SELECT name,
       is_active
  FROM customers

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.

{ "is_active": 0, "details": 0 }

Return specified fields in an embedded document

Using 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 for more examples of dot notation for embedded documents.

{ "name": 1, "details.name": 1, "details.type": 1 }

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

Return specified fields in an embedded document in an array

Using 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 for more examples of dot notation for embedded documents and arrays.

{ "name": 1, "acquaintances.name": 1, "acquaintances.type": 1 }

SELECT name,
       "acquaintances.name",
       "acquaintances.type"
  FROM customers

Error troubleshooting

If a collection’s projection query doesn’t meet Stitch’s 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.

Resources

• MongoDB projection query documentation
• MongoDB dot notation documentation
• MongoDB Extraction Errors reference

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.