Mongodb aggregate to sort items with most common items in array?

Question

An item in collection contains an array of strings. I would like to find and sort items with most matching elements in array.

Consider a collection:

[
    {
        "item_name":"Item_1",
        "tags":["A","B","C","D","E"]
    },
    {
        "item_name":"Item_2",
        "tags":["A","B","D","E","G"]
    },
    {
        "item_name":"Item_3",
        "tags":["B","C","E","H"]
    }

]

I want to sort the collection based on an array like ["B","D","G","F"] which will return

[

    {
        "item_name":"Item_2",
        "tags":["A","B","D","E","G"]
    },
    {
        "item_name":"Item_1",
        "tags":["A","B","C","D","E"]
    },
    {
        "item_name":"Item_3",
        "tags":["B","C","E","H"]
    }

]

The expected order will be Item_2,Item_1 and then Item_3 since,

Item_2 matches 3 items ("B","D" and "G")
then Item_1 with 2 matches ("B" and "D")
finally Item_3 with 1 match ("B")

If not in mongodb, JavaScript methods will also be appreciated

user_1656749 · Answer

If the values in tags are unique, you can use the size of the intersections of tags and the query array using $size and $setIntersection

db.collection.aggregate([
  {
    $set: {
      matchedCount: {
        $size: {
          $setIntersection: ["$tags", ["B","D","G","F"]]
        }
      }
    }
  },
  {
    $sort: {
      matchedCount: -1
    }
  }
])

Ji237_Posp237il_19093 · Answer

You can use $setIntersection to get the number of intersecting items and then $sort based on the computed score.

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      item_name: "$item_name",
      tags: "$tags",
      score: {
        $let: {
          vars: {
            intersection: {
              $setIntersection: [
                "$tags",
                [
                  "B",
                  "D",
                  "G",
                  "F"
                ]
              ]
            }
          },
          in: {
            $size: "$$intersection"
          }
        }
      }
    }
  },
  {
    $sort: {
      score: -1
    }
  }
])

https://mongoplayground.net/p/tpDTtVKetFT

Mongodb aggregate to sort items with most common items in array?

2 Answers