CODEWITHSUNDEEP
mongodbmongoid
David Elner
David Elner

Reputation: 5201

MongoDB: Match and sort on array element?

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

Some related links I looked at:

Upvotes: 4

Views: 3287

Answers (2)

Stennie
Stennie

Reputation: 65433

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
}

Upvotes: 5

luttkens
luttkens

Reputation: 1264

Since social_stats is an array I think you will need the aggregation framework or MapReduce. But if you change the structure to

{ name: 'ProductB',
  social_stats : { 
    'facebook' : {shares: 0, name : "Facebook"},
    'twitter' : {shares: 30, name : "Twitter"},
  }
}

... you could do:

.sort({"social_stats.facebook.shares" : -1})

Note: This will require a change your query as well!

Upvotes: 1

Related Questions