MongoDB - Sorting / Merging multiple embedded documents

Question

I’m quite new with mongoldb.

I have a question that I cannot resolve by myself.

This is my model (simplified for the scope of this question)

account {
type: String
videos: [video],
images: [image]
}

video {
name: String,
length: Number,
Date: Date
}

image {
name: String,
size: Number
Date: Date
}

so a main model Account with two embedded documents: videos and images.

I would like to (in this sequence):

1. query all the accounts by type
2. sort both videos and images arrays by Date
3. get the list of images and videos
4. limit the output in order to do a pagination

Is it possible or is preferable change the model?

Example:

Source

[{ 
type: 2, 
images: 
       [{ name: 'imagetest1', size: 3, Date: 2011-01-01}, 
       { name: 'imagetest2', size: 13, Date: 2011-02-02}], 
videos: 
       [{ name: 'videotest1', length: 24, Date: 2011-01-07}, 
       { name: 'videotest2', length: 15, Date: 2011-03-02}] }
{
type: 2, 
images: 
       [{ name: 'imagetest3', size: 3, Date: 2011-01-03}, 
       { name: 'imagetest4', size: 15, Date: 2011-01-06}], 
videos: 
       [{ name: 'videotest3', length: 24, Date: 2011-02-05}, 
       { name: 'videotest4', length: 16, Date: 2011-02-04}] 
},
{
type: 1, 
images: 
       [{ name: 'imagetest5', size: 3, Date: 2011-01-03}, 
       { name: 'imagetest6', size: 15, Date: 2011-01-06}], 
videos: 
       [{ name: 'videotest5', length: 24, Date: 2011-02-05}, 
       { name: 'videotest6', length: 16, Date: 2011-02-04}] 
}]

MongoDB query:

Query account by type: 2, sort both images and video by data ascending and finally merge images and video in one array.

Output

[{ name: 'imagetest1', size: 3, Date: 2011-01-01},
{ name: 'videotest1', length: 24, Date: 2011-01-02}, 
{ name: 'imagetest3', size: 3, Date: 2011-01-03},
{ name: 'videotest4', length: 16, Date: 2011-02-04},
{ name: 'videotest3', length: 24, Date: 2011-02-05},
{ name: 'imagetest4', size: 15, Date: 2011-01-06},
{ name: 'videotest1', length: 24, Date: 2011-01-07},
{ name: 'imagetest2', size: 13, Date: 2011-02-02},
{ name: 'videotest2', length: 15, Date: 2011-03-02}]

Answer 1

You can do it with help of MAP-REDUCE.

Map function :

var map = function()
{

var doc = this.videos;

for( var i =0 ; i < this.images.length; i++ )
{
doc.push({name: this.images[i].name, length : this.images[i].size, Date: this.images[i].Date});
}

emit(this.type, doc );
}

Reduce Function :

var reduce = function(k,v)
{
 var arr = v[0];
 for( var j = 1 ; j < v.length; j++ )
  {
    arr = arr.concat(v[j]);
  }
  return arr;
}

Query :

db.accounts.mapReduce(
                       map,
                       reduce,
                       { 
                         out : {inline: 1} ,
                         query : {type : 2} , 
                         sort : {Date : 1}
                       }
                     );

In the future releases of MongoDb 3.2 you can use $concatArrays operator to merge the Arrays in aggregation pipeline easily.

db.accounts.aggregate([
{
  $match : { type: 2 }
},
{
  $project : 
            { 
             newArray:  
                  { $concatArrays: [ {$ifNull: ["$videos", [] ] }, {$ifNull: ["$images", [] ] } ] } ,
             type : 1
            } 
},
{
  $sort : {"$newArray.Date" : 1 }
}
]);