Sort by aggregated fields and their count

Working with Data
, , ,
1

Hello I have the first schema for the properties:

const property = mongoose.Schema({

  city: { type: String, default: null },

  tags: [
    {
      _id: { type: mongoose.Schema.Types.ObjectId, ref: "Tag",  default: null },
      count: { type: Number, default: null },
    },
  ],
});

and the second one for tags:

const tag = mongoose.Schema({
  name: { type: String, unique: true, default: null },
  createdAt: { type: Date, default: Date.now },
});

I prepared a playground for testing : Mongo playground

input from the frontend is an array of tag ids example: [1 , 2] // the array is dynamic.

example of the sorted result:

[
  {
    "_id": 1,
    "city": "Paris", // on top because the tags count is higher
    "tags": [
      {
        "_id": 1, // seen
        "count": 43
      },
      {
        "_id": 2, // liked
        "count": 32
      }
    ]
  },
  {
    "_id": 2,
    "city": "leon",
    "tags": [
      {
        "_id": 1, // seen
        "count": 17
      }
    ]
  }
]

PLEASE CONSIDER: the tag ids that I need to sort by are in the request coming from the frontend.

I want to sort by total of tag count in properties, so I can get a list of properties with the highest matched tags count on top.

thank you.

2

Hi @abdelghafour_ennahid,

Could you please show your expected result in JSON format as per your schemas? Also, add the example inputs.

3

hello @turivishal thank you for the reply, i made the changes.

4

What is the role of the “tags” collection?

5

this? Mongo playground

    db.properties.aggregate([
      {
        $match: {
          "tags._id": {
            $in: [
              1,
              2
            ]
          }
        }
      },
      {
        $set: {
          tags: {
            //filters array 'tags' to the list of tags 1, 2
            $filter: {
              input: "$tags",
              cond: {
                $in: [
                  "$$this._id",
                  [
                    1,
                    2
                  ]
                ]
              }
            }
          }
        }
      },
      {
        "$unwind": "$tags"
      },
      {
        $sort: {
          "tags.count": -1
        }
      },
      {
        "$group": {
          "_id": "$_id",
          "city": {
            "$first": "$city"
          },
          tags: {
            $push: "$tags"
          }
        }
      }
    ])
3 Likes
6

it works, thank you so much this is exactly what I needed.

7

Great solution by @Katya.

You can also use a new operator $sortArray from MongoDB v5.2,

Just combine the stages into one stage,

  • pass that filter operation into the $sortArray operator’s input, just sort by count in descending order
var tags = [1, 2];
db.properties.aggregate([
  {
    $match: {
      "tags._id": { $in: tags }
    }
  },
  {
    $addFields: {
      tags: {
        $sortArray: {
          input: {
            $filter: {
              input: "$tags",
              cond: { $in: ["$$this._id", tags] }
            }
          },
          sortBy: { count: -1 }
        }
      }
    }
  }
])

Playground

8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.