MongoDB Aggregate: Sorting by existing values first

Question

My user has this field :

interestedIn: [{
        type: String,
        enum: [
            'art',
            'sport',
            'news',
            'calture',
            ...
        ],
    }],

and my video has this field:

categories: [{
            type: String,
            enum: [
                'art',
                'sport',
                'news',
                'calture',
                ...
            ],
        }],

So I need a Video query that has the following conditions:

Query to all videos and sort by values in req.user.interestedIn first.
Rest of the videos that are not match to req.user.interestedIn get in the last.

I got this far with said query:

Video.aggregate([

        { '$match': {}},
        { '$unwind': '$categories' },
        { '$match': {categories: {$in: req.user.interestedIn}}},
        { '$group': {
            '_id': '$categories',
            'categories': { '$push': '$categories' }
        }},
        { '$sort': { 'categories': 1 } }
    ])

This is the result:

   "videos": [
        {
            "_id": "art",
            "categories": [
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art"
            ]
        },
        {
            "_id": "news",
            "categories": [
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
   "videos": [
        {
            "_id": "art",
            "categories": [
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art",
                "art"
            ]
        },
        {
            "_id": "news",
            "categories": [
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news",
                "news"
            ]
        }
    ]
}

Something like Video.aggregate([ { '$match': {}}, { '$unwind': '$categories' }, {"$addFields":{ "sortorder":{"$indexOfArray":[req.user.interestedIn, "$categories"]}}}, {"$sort":{"sortorder":-1}} ]) — s7vr
– s7vr, Commented Oct 16, 2018 at 11:47
@Veeram tnx for answering . i get query result back but I have 100 videos in db in the result shows 367. I think query aggregate throw per element in array (InterestedIn) . — Babak Abadkheir
– Babak Abadkheir, Commented Oct 16, 2018 at 11:58
Can you show the expected response ? do you need to group the videos by category ? — s7vr
– s7vr, Commented Oct 16, 2018 at 12:00
I just need my videos to sort by existing of InterestedIn first — Babak Abadkheir
– Babak Abadkheir, Commented Oct 16, 2018 at 12:03
still not clear. how do we compare category with interestedIn array ? As both are array what the comparison should look like ? — s7vr
– s7vr, Commented Oct 16, 2018 at 12:20

s7vr · Accepted Answer · 2018-10-16 12:44:29Z

4

You can use $setIntersection to extract the matching elements followed by $size to count the matches.

$sort descending documents on number of matches.

Something like

Video.aggregate([ 
  {"$addFields":{ "numofmatches":{"$size":{"$setIntersection":["$categories", req.user.interestedIn]}}}}, 
  {"$sort":{"numofmatches":-1}} 
])

answered Oct 16, 2018 at 12:44

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB Aggregate: Sorting by existing values first

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related