Navigation

$out (aggregation)

On this page

Definition

$out

Takes the documents returned by the aggregation pipeline and writes them to a specified collection. Starting in MongoDB 4.4, you can specify the output database.

The $out stage must be the last stage in the pipeline. The $out operator lets the aggregation framework return result sets of any size.

Syntax

The $out stage has the following syntax:

  • Starting in MongoDB 4.4, $out can take a document to specify the output database as well as the output collection:

    copy 
    { $out: { db: "<output-db>", coll: "<output-collection>" } }
    Field Description
    db

    The output database name.

    • For a replica set or a standalone, if the output database does not exist, $out also creates the database.
    • For a sharded cluster, the specified output database must already exist.
    coll

    The output collection name.

  • $out can take a string to specify only the output collection (i.e. output to a collection in the same database):

    copy 
    { $out: "<output-collection>" } // Output collection is in the same database

Important

  • You cannot specify a sharded collection as the output collection. The input collection for a pipeline can be sharded. To output to a sharded collection, see $merge (Available starting in MongoDB 4.2).
  • The $out operator cannot write results to a capped collection.

Comparison with $merge

$merge and $out Comparison

With the introduction of $merge in version 4.2, MongoDB provides two stages, $merge and $out, for writing the results of the aggregation pipeline to a collection. The following summarizes the capabilities of the two stages:

$out $merge
  • Available starting in MongoDB 2.6
  • Available starting in MongoDB 4.2
  • Can output to a collection in the same or, starting in MongoDB 4.4, different database.
  • Can output to a collection in the same or different database.
  • Creates a new collection if the output collection does not already exist.
  • Creates a new collection if the output collection does not already exist.
  • Replaces the output collection completely if it already exists.

  • Can incorporate results (insert new documents, merge documents, replace documents, keep existing documents, fail the operation, process documents with a custom update pipeline) into an existing collection.

    Can replace the content of the collection but only if the aggregation results contain a match for all existing documents in the collection.
  • Cannot output to a sharded collection. Input collection, however, can be sharded.
  • Can output to a sharded collection. Input collection can also be sharded.
  • Corresponds to SQL statements:
    • INSERT INTO T2 SELECT FROM T1.
    • SELECT INTO T2 FROM T1.
  • Corresponds to SQL statements:
    • MERGE.
    • INSERT INTO T2 SELECT FROM T1.
    • SELECT INTO T2 FROM T1.
    • Create/Refresh Materialized Views.

Behaviors

Create New Collection

The $out operation creates a new collection if one does not already exist.

The collection is not visible until the aggregation completes. If the aggregation fails, MongoDB does not create the collection.

Replace Existing Collection

If the collection specified by the $out operation already exists, then upon completion of the aggregation, the $out stage atomically replaces the existing collection with the new results collection. Specifically, the $out operation:

  1. Creates a temp collection.
  2. Copies the indexes from the existing collection to the temp collection.
  3. Inserts the documents into the temp collection.
  4. Calls the renameCollection command with dropTarget: true to rename the temp collection to the destination collection.

The $out operation does not change any indexes that existed on the previous collection. If the aggregation fails, the $out operation makes no changes to the pre-existing collection.

Index Constraints

The pipeline will fail to complete if the documents produced by the pipeline would violate any unique indexes, including the index on the _id field of the original output collection.

majority Read Concern

Starting in MongoDB 4.2, you can specify read concern level "majority" for an aggregation that includes an $out stage.

In MongoDB 4.0 and earlier, you cannot include the $out stage to use "majority" read concern for the aggregation.

Interaction with mongodump

A mongodump started with --oplog fails if a client issues an aggregation pipeline that includes $out during the dump process. See mongodump --oplog for more information.

Restrictions

Restrictions Description
Transactions An aggregation pipeline cannot use $out inside transactions.
View definition The $out stage is not allowed as part of a view definition. If the view definition includes nested pipeline (e.g. the view definition includes $lookup or $facet stage), this $out stage restriction applies to the nested pipelines as well.
$lookup stage Starting in 4.2, you cannot include the $out stage in the $lookup stage’s nested pipeline.
$facet stage $facet stage’s nested pipeline cannot include the $out stage.
$unionWith stage $unionWith stage’s nested pipeline cannot include the $out stage.
"linearizable" read concern Starting in MongoDB 4.2, the $out stage cannot be used in conjunction with read concern "linearizable". That is, if you specify "linearizable" read concern for db.collection.aggregate(), you cannot include the $out stage in the pipeline.

Examples

In the test database, create a collection books with the following documents:

copy 
db.getSiblingDB("test").books.insertMany([
   { "_id" : 8751, "title" : "The Banquet", "author" : "Dante", "copies" : 2 },
   { "_id" : 8752, "title" : "Divine Comedy", "author" : "Dante", "copies" : 1 },
   { "_id" : 8645, "title" : "Eclogues", "author" : "Dante", "copies" : 2 },
   { "_id" : 7000, "title" : "The Odyssey", "author" : "Homer", "copies" : 10 },
   { "_id" : 7020, "title" : "Iliad", "author" : "Homer", "copies" : 10 }
])

If the test database does not already exist, the insert operation creates the database as well as the books collection.

Output to Same Database

The following aggregation operation pivots the data in the books collection in the test database to have titles grouped by authors and then writes the results to the authors collection, also in the test database.

copy 
db.getSiblingDB("test").books.aggregate( [
    { $group : { _id : "$author", books: { $push: "$title" } } },
    { $out : "authors" }
] )
First Stage ($group):

The $group stage groups by the authors and uses $push to add the titles to a books array field:

{ "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }
{ "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] }
Second Stage ($out):
The $out stage outputs the documents to the authors collection in the test database.

To view the documents in the output collection, run the following operation:

copy 
db.getSiblingDB("test").authors.find()

The collection contains the following documents:

{ "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] }
{ "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }

Output to a Different Database

Note

For a replica set or a standalone, if the output database does not exist, $out also creates the database.

For a sharded cluster, the specified output database must already exist.

Starting in MongoDB 4.4, $out can output to a collection in a database different from where the aggregation is run.

The following aggregation operation pivots the data in the books collection to have titles grouped by authors and then writes the results to the authors collection in the reporting database:

copy 
db.getSiblingDB("test").books.aggregate( [
    { $group : { _id : "$author", books: { $push: "$title" } } },
    { $out : { db: "reporting", coll: "authors" } }
] )
First Stage ($group):

The $group stage groups by the authors and uses $push to add the titles to a books array field:

{ "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }
{ "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] }
Second Stage ($out):
The $out stage outputs the documents to the authors collection in the reporting database.

To view the documents in the output collection, run the following operation:

copy 
db.getSiblingDB("reporting").authors.find()

The collection contains the following documents:

{ "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] }
{ "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.