.. _agg-out: ================== $out (aggregation) ================== .. default-domain:: mongodb .. contents:: On this page :local: :backlinks: none :depth: 1 :class: singlecol Definition ---------- .. pipeline:: $out Takes the documents returned by the aggregation pipeline and writes them to a specified collection. The :pipeline:`$out` operator must be *the last stage* in the pipeline. The :pipeline:`$out` operator lets the aggregation pipeline return result sets of any size. .. versionchanged:: 3.2.0 The :pipeline:`$out` stage has the following prototype form: .. code-block:: javascript { $out: "" } :pipeline:`$out` takes a string that specifies the output collection name. .. 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 :pipeline:`$merge` (Available starting in MongoDB 4.2). - The :pipeline:`$out` operator cannot write results to a :doc:`capped collection `. .. _out-merge-comparison: Comparison with ``$merge`` ~~~~~~~~~~~~~~~~~~~~~~~~~~ With the introduction of :pipeline:`$merge` in version 4.2, MongoDB provides two stages, :pipeline:`$merge` and :pipeline:`$out`, for writing the results of the aggregation pipeline to a collection. The following summarizes the capabilities of the two stages: .. list-table:: :header-rows: 1 * - :pipeline:`$out` - :pipeline:`$merge` * - - 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 the SQL statements: - .. code-block:: sql :copyable: false INSERT INTO T2 SELECT * FROM T1 - .. code-block:: sql :copyable: false SELECT * INTO T2 FROM T1 - - Corresponds to the SQL statement: - .. code-block:: sql :copyable: false MERGE T2 AS TARGET USING (SELECT * FROM T1) AS SOURCE ON MATCH (T2.ID = SOURCE.ID) WHEN MATCHED THEN UPDATE SET TARGET.FIELDX = SOURCE.FIELDY WHEN NOT MATCHED THEN INSERT (FIELDX) VALUES (SOURCE.FIELDY) - Create/Refresh Materialized Views Behaviors --------- Create New Collection ~~~~~~~~~~~~~~~~~~~~~ The :pipeline:`$out` operation creates a new collection in the current database 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 :pipeline:`$out` operation already exists, then upon completion of the aggregation, the :pipeline:`$out` stage atomically replaces the existing collection with the new results collection. Specifically, the :pipeline:`$out` operation: #. Creates a temp collection. #. Copies the indexes from the existing collection to the temp collection. #. Inserts the documents into the temp collection. #. Calls :method:`db.collection.renameCollection` with ``dropTarget: true`` to rename the temp collection to the destination collection. The :pipeline:`$out` operation does not change any indexes that existed on the previous collection. If the aggregation fails, the :pipeline:`$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. If the :pipeline:`$out` operation modifies a collection with an :atlas:`Atlas Search ` index, you must delete and re-create the search index. Consider using :pipeline:`$merge` instead. Transactions ~~~~~~~~~~~~ ``$out`` is not allowed in :doc:`transactions `. Views ~~~~~ .. versionadded:: 4.2 The :pipeline:`$out` stage is not allowed as part of a :doc:`view definition `. If the view definition includes nested pipeline (e.g. the view definition includes :pipeline:`$lookup` or :pipeline:`$facet` stage), this :pipeline:`$out` stage restriction applies to the nested pipelines as well. [#lookup]_ .. [#lookup] Starting in 4.2, you cannot include the :pipeline:`$out` stage in the :pipeline:`$lookup` stage's :ref:`nested pipeline `, regardless of whether the :pipeline:`$lookup` is part of a view definition. ``linearizable`` Read Concern ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. include:: /includes/extracts/4.2-changes-out-linearizable.rst ``majority`` Read Concern ~~~~~~~~~~~~~~~~~~~~~~~~~ .. include:: /includes/fact-aggregate-readConcern.rst Interaction with ``mongodump`` ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A :binary:`~bin.mongodump` started with :option:`--oplog ` fails if a client issues an aggregation pipeline that includes :pipeline:`$out` during the dump process. See :option:`mongodump --oplog` for more information. Example ------- A collection ``books`` contains the following documents: .. code-block:: javascript { "_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 } 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. .. code-block:: javascript db.books.aggregate( [ { $group : { _id : "$author", books: { $push: "$title" } } }, { $out : "authors" } ] ) After the operation, the ``authors`` collection contains the following documents: .. code-block:: javascript { "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] } { "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }