MongoDB: Match and sort on array element?

Question

I have a few 'Product' objects:

{ name: 'ProductA',
  social_stats: [
    { name: 'facebook',
      shares: 60
    },
    { name: 'twitter',
      shares: 0
    }
  ]
}

{ name: 'ProductB',
  social_stats: [
    { name: 'facebook',
      shares: 0
    },
    { name: 'twitter',
      shares: 30
    }
  ]
}

I'd like to query for "most shared products on Facebook" and the "most shared products on Twitter", always sorted from most to least shares.

So my first query for Facebook looks like:

db.videos.find({
  _id: {
    social_stats: {
      $elemMatch: {
        name: 'facebook'
      }
    }
  }
).sort( {
  social_stats: {
    shares: -1
  }
})

Yields:

{ name: 'ProductA' }
{ name: 'ProductB' }

This is 'correct', however when I run the same query for 'twitter' instead, I expect B->A, but receive the same output as above. It appears to be not applying the where & sort logic together as I intend i.e. "Order by the social_stat element that matches 'twitter'".

What I'm looking for

• How do a change my query to reflect the application of order() to matched social_stat elements?
• If it isn't possible with these normal MongoDB queries, is this something I can do with the aggregation framework? And what would that look like?
• Then as a bonus, how would code the equivalent query with Mongoid?

Some related links I looked at:

• Found this similar post (does mean I need to use the aggregation framework?)
• $elemMatch operator
• Aggregation operators

Answer 1

You can't sort() your results by an array, so this isn't going to achieve the outcome you're after.

Your best approach (as at MongoDB 2.4) would be to use the Aggregation Framework:

db.videos.aggregate(
    // Optional: potentially take advantage of an index to only find videos with
    //           facebook stats; could also limit to those with shares $gt 0
    { $match: {
        'social_stats.name' : 'facebook'
    }},

    // Convert the social_stats array into a document stream
    { $unwind: '$social_stats' },

    // Only match the social stats for facebook
    { $match: {
        'social_stats.name' : 'facebook'
    }},

    // Sort in descending order
    { $sort: {
        'social_stats.shares' : -1
    }},

    // Only include the product names & score
    { $project: {
        _id: 0,
        name: "$name",
        shares: "$social_stats.shares"
    }}
)

Results for 'twitter':

{
    "result" : [
        {
            "name" : "ProductB",
            "shares" : 30
        },
        {
            "name" : "ProductA",
            "shares" : 0
        }
    ],
    "ok" : 1
}

Results for 'facebook':

{
    "result" : [
        {
            "name" : "ProductA",
            "shares" : 60
        },
        {
            "name" : "ProductB",
            "shares" : 0
        }
    ],
    "ok" : 1
}