MongoDB / mongoose order by nested object value where other value is equal to

Question

I have this mongoose Schema:

const PostsSchema = new Schema(
  {
    type: { type: String, default: "house", index: true },
    title: { type: String, required: true },
    descriptiopn: String,
    image: String,
    userId: {
      type: Schema.Types.ObjectId,
      ref: "User",
      required: true,
      index: true
    },
    details: [{ name: String, label: String, value: Schema.Types.Mixed }]
  },
  { collection: "posts", timestamps: true }
);

I'm trying to order the documents by details.value where details.name="something"

For example if one of the details object looks like this:

{"name":"size", "label":"Size", "value": 50}

I want to order by it's value where name="size"

Is there a way to do it or perhaps should I structure the schema differently?

Thanks for the help :)

Answer 1

You can use mongodb aggregation framework.

First we need to $unwind details, then to be able to first sort by details.name: "size" we are adding a sortPriority field with $addFields, then we are sorting by this sortPriority field and details.value. After sorting we are using $group and $replaceRoot aggregations to shape our collections to back to their original structure.

You can use the following aggregate:

db.collection.aggregate([
  {
    $unwind: "$details"
  },
  {
    $addFields: {
      "details.sortPriority": {
        $eq: [
          "$details.name",
          "size"
        ]
      }
    }
  },
  {
    $sort: {
      "details.sortPriority": -1,
      "details.value": -1
    }
  },
  {
    $project: {
      "details.sortPriority": 0
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "details": {
        "$push": "$details"
      },
      "allFields": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$allFields",
          {
            "details": "$details"
          }
        ]
      }
    }
  }
])

Playground

Sample input:

[
  {
    _id: ObjectId("5a934e000102030405000000"),
    type: "house",
    title: "title 1",
    userId: "user 1",
    details: [
      {
        "name": "size",
        "label": "Size",
        "value": 50
      },
      {
        "name": "size",
        "label": "Size",
        "value": 70
      },
      {
        "name": "color",
        "label": "Color",
        "value": 60
      }
    ]
  },
  {
    _id: ObjectId("5a934e000102030405000001"),
    type: "house",
    title: "title 2",
    userId: "user 2",
    details: [
      {
        "name": "size",
        "label": "Size",
        "value": 10
      },
      {
        "name": "color",
        "label": "Color",
        "value": 20
      },
      {
        "name": "size",
        "label": "color",
        "value": 30
      }
    ]
  }
]

Output:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "details": [
      {
        "label": "Size",
        "name": "size",
        "value": 70
      },
      {
        "label": "Size",
        "name": "size",
        "value": 50
      },
      {
        "label": "Color",
        "name": "color",
        "value": 60
      }
    ],
    "title": "title 1",
    "type": "house",
    "userId": "user 1"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "details": [
      {
        "label": "color",
        "name": "size",
        "value": 30
      },
      {
        "label": "Size",
        "name": "size",
        "value": 10
      },
      {
        "label": "Color",
        "name": "color",
        "value": 20
      }
    ],
    "title": "title 2",
    "type": "house",
    "userId": "user 2"
  }
]

In mongoose you can use the aggregation using ModelName.aggregate([...])

For example if your model ise Post:

await Post.aggregate([...]);