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:


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
v1.0 Supported
v11-01-2016 Unsupported

What are projection queries?

In MongoDB, the default for queries is to return all fields in matching documents. Projection queries are 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: The default query behavior in MongoDB 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.

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

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.

  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.

    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

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

  • name: Jake, type: best_friend
  • name: Ice King, type: nemesis

Jake

true

age: 6, type: dog

  • name: Finn, type: best_friend
  • name: Lady, type: spouse

Bubblegum

false

age: 16, type: princess

  • name: Finn, type: friend
  • name: Bubblegum, type: best_friend

Lady

true

age: 50, type: unicorn

  • name: Jake, type: spouse
  • name: Finn, type: friend

Ice King

false

age: 900, type: king

  • name: Finn, type: nemesis
  • name: Bubblegum, type: nemesis

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
{ "name": 1, "is_active": 1 }
SQL
SELECT name,
       is_active
  FROM customers
Results
name is_active

Finn

true

Jake

true

Bubblegum

false

Lady

true

Ice King

false

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
{ "is_active": 0, "details": 0 }
Results
name acquaintances

Finn

  • name: Jake, type: best_friend
  • name: Ice King, type: nemesis

Jake

  • name: Finn, type: best_friend
  • name: Lady, type: spouse

Bubblegum

  • name: Finn, type: friend
  • name: Bubblegum, type: best_friend

Lady

  • name: Jake, type: spouse
  • name: Finn, type: friend

Ice King

  • name: Finn, type: nemesis
  • name: Bubblegum, type: nemesis

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.

Projection query
{ "name": 1, "details.name": 1, "details.type": 1 }
SQL

In destinations - like Snowflake - that also use dot notation to query nested data, the query might look like this:

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

Finn

age: 15, type: human

Jake

age: 6, type: dog

Bubblegum

age: 16, type: princess

Lady

age: 50, type: unicorn

Ice King

age: 900, type: king

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.

Projection query
{ "name": 1, "acquaintances.name": 1, "acquaintances.type": 1 }
SQL

In destinations - like Snowflake - that also use dot notation to query nested data, the query might look like this:

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

Finn

  • name: Jake, type: best_friend
  • name: Ice King, type: nemesis

Jake

  • name: Finn, type: best_friend
  • name: Lady, type: spouse

Bubblegum

  • name: Finn, type: friend
  • name: Bubblegum, type: best_friend

Lady

  • name: Jake, type: spouse
  • name: Finn, type: friend

Ice King

  • name: Finn, type: nemesis
  • name: Bubblegum, type: nemesis

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



Questions? Feedback?

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.