Save Subset of MongoDB Collection to Another Collection

Question

I have a set like so

{date: 20120101}
{date: 20120103}
{date: 20120104}
{date: 20120005}
{date: 20120105}

How do I save a subset of those documents with the date '20120105' to another collection?

i.e db.subset.save(db.full_set.find({date: "20120105"}));

Answer 1

I would advise using the aggregation framework:

db.full_set.aggregate([ { $match: { date: "20120105" } }, { $out: "subset" } ])

It works about 100 times faster than forEach at least in my case. This is because the entire aggregation pipeline runs in the mongod process, whereas a solution based on find() and insert() has to send all of the documents from the server to the client and then back. This has a performance penalty, even if the server and client are on the same machine.

Answer 2

Here's the shell version:

db.full_set.find({date:"20120105"}).forEach(function(doc){
   db.subset.insert(doc);
});

Note: As of MongoDB 2.6, the aggregation framework makes it possible to do this faster; see melan's answer for details.

Answer 3

Actually, there is an equivalent of SQL's insert into ... select from in MongoDB. First, you convert multiple documents into an array of documents; then you insert the array into the target collection

db.subset.insert(db.full_set.find({date:"20120105"}).toArray())

Answer 4

The most general solution is this:

Make use of the aggregation (answer given by @melan):

db.full_set.aggregate({$match:{your query here...}},{$out:"sample"})
db.sample.copyTo("subset")

This works even when there are documents in "subset" before the operation and you want to preserve those "old" documents and just insert a new subset into it.

Care must be taken, because the copyTo() command replaces the documents with the same _id.

Answer 5

Mongodb has aggregate along with $out operator which allow to save subset into new collection. Following are the details :

$out Takes the documents returned by the aggregation pipeline and writes them to a specified collection.

• The $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.

Syntax :

{ $out: "<output-collection>" }

Example A collection books contains the following documents:

{ "_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.

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

After the operation, the authors collection contains the following documents:

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

In the asked question, use following query and you will get new collection named 'col_20120105' in your database

 db.products.aggregate([
  { $match : { date : "20120105" } },
  { $out : "col_20120105" }
]);

Answer 6

There's no direct equivalent of SQL's insert into ... select from ....

You have to take care of it yourself. Fetch documents of interest and save them to another collection.

You can do it in the shell, but I'd use a small external script in Ruby. Something like this:

require 'mongo'

db = Mongo::Connection.new.db('mydb')

source = db.collection('source_collection')
target = db.collection('target_collection')

source.find(date: "20120105").each do |doc|
  target.insert doc
end

Answer 7

You can also use $merge aggregation pipeline stage.

db.full_set.aggregate([
  {$match: {...}},
  { $merge: {
     into: { db: 'your_db', coll: 'your_another_collection' },
     on: '_id',
     whenMatched: 'keepExisting',
     whenNotMatched: 'insert'
  }}
])