Mongodb with ranking / search count

Question

Good day everyone!

I was trying to find solution for 3 days but cant find any. Hope you guys could help me.

data

{       
 'item': 'pen-1', 
 'colors': [ 'yellow', 'green', 'blue', 'red', 'pink', 'purple' ] //total: 6
  },
  {
 'item': 'pen-2', 
 'colors': [ 'green', 'blue','pink', 'purple' ] //total: 4
 }

this is the query i did so far:

var col = ['yellow', 'red', 'blue'];
db.getCollection('data').aggregate([
            { $match: { colors : { $in : col } } },
            { $group: { _id: { 'item': '$item',  colors: { $size : '$colors' } } } }
          ])

and the result

{
    "_id" : {
        "item" : "pen-1",
        "colors" : 6
    }
}
{
    "_id" : {
        "item" : "pen-2",
        "colors" : 4
    }
}

what i want to do:

return the item, return total of (color / color that match) so it would be something like this:

"_id" : {
        "item" : "pen-1",
        "rank": 0.5 // total colors(6) / that match(3)
    }

 "_id" : {
        "item" : "pen-2",
        "rank": 0.25 // total colors(4) / that match(1)
    }

Answer 1

You could use the $setIntersection operator which takes two or more arrays and returns an array that contains the elements that appear in every input array. You can then use $size to return the number of elements in the resulting array to get the total colors that match.

If using MongoDB 3.4 and newer then $addFields will allow you to add an extra field to the documents without explicitly projecting the rest of the fields, otherwise $project will suffice.

The following example shows this:

var col = ['yellow', 'red', 'blue'];
db.getCollection('data').aggregate([
    { "$match": { "colors": { "$in": col } } },
    {
        "$addFields": {
            "rank": {
                "$divide": [                        
                    { "$size": { "$setIntersection": ["$colors", col] } },
                    { "$size": "$colors" }
                ]
            }
        }
    },
    { "$sort": { "rank": 1 } }            
])

Sample Output

/* 1 */
{
    "_id" : ObjectId("58c645eaf3d1c82da16279a6"),
    "item" : "pen-2",
    "colors" : [ 
        "green", 
        "blue", 
        "pink", 
        "purple"
    ],
    "rank" : 0.25
}

/* 2 */
{
    "_id" : ObjectId("58c645eaf3d1c82da16279a5"),
    "item" : "pen-1",
    "colors" : [ 
        "yellow", 
        "green", 
        "blue", 
        "red", 
        "pink", 
        "purple"
    ],
    "rank" : 0.5
}

Answer 2

Here is how you can do it in MongoDB starting from v. 3.4:

var colors = ['red', 'pink'];

db.getCollection('test').aggregate([
        //Filtering out the records where colors array is empty
        //To avoiding dividing by zero.
	{
		$match: {
			'colors.0': {$exists: true}
		}
	},
	{
		$addFields: {
			matchingColors: {
				$filter: {
					input: '$colors',
					as: 'color',
					cond: {'$in': ['$$color', colors]}
				}
			}
		}
	},
	{
		$project: {
			item: '$item',
			score: {$divide: [{$size: '$matchingColors'}, {$size: '$colors'}]}
		}
	}
]);