How to compare sorting with lookup in mongodb 4.4?

Question

I'm new to mongodb, I'm on version 4.4, no update possible.

I have a collection items with an attribute categories (array). I would like to "join" ($lookup) the collection categories to it - where each category has a sortOrder attribute - in order to compare if categories in items has a different sorting than in the collection categories.

My approach so far:

[
    {
        $addFields: {
            categoriesWithIndex: {
                $map: {
                    input: {$range: [0, {$size: "$categories" }]},
                    as: "index",
                    in: {
                        index: "$$index",
                        value: {$arrayElemAt: ["$categories", "$$index"]}
                    }
                }
            }
        }
    },
    {
        $unwind: "$categoriesWithIndex"
    },
    {
        $lookup: {
            from: "categories",
            localField: "categoriesWithIndex.value",
            foreignField: "_id"
            as: "categoryDetails"
        }
    },
    {
        $addFields: {
            "categoriesWithIndex.sortOrder": {
                $arrayElemAt: ["$categoryDetails.sortOrder", 0]
            }
        }
    },
    {
        $group: {
            _id: "$_id",
            categories: {
                $push: {
                    $mergeObjects: [
                        {
                            index: "$categoriesWithIndex.index",
                            sortOrder: "$categoriesWithIndex.sortOrder",
                            value: "$categoriesWithIndex.value"
                        }
                    ]
                }
            }
        }
    },
]

now the document looks like this:

_id: "1234"
categories: Array (3)

0: Object
index: 0
sortOrder: "2"
value: "catX"

1: Object
index: 1
sortOrder: "3"
value: "catY"

2: Object
index: 2
sortOrder: "1"
value: "catZ"

now it feels like I'm almost there, but I'm not sure how to continue. Unfortunately I don't have $sortArray... could you please point me to the right direction?

Answer 1

after the final grouping stage you can check if the sortOrder is in ascending order. If it is it means that items collection obey the sort order. If they are not in order means that items collection doesn't obey the sort order.
To check this you can try a $reduce pipeline stage. Start with true and check if sortOrder value of n+1 > n. Combining with $and you will get false if this condition is failed at least once.

you can add this as the last stage in your aggregation pipeline

{
  $addFields: {
    isSortOrderIncreasing: {
      $reduce: {
        input: { $range: [1, { $size: "$categories" }] },
        initialValue: true,
        in: {
          $and: [
            "$$value",
            {
              $gt: [
                { $arrayElemAt: ["$categories.sortOrder", "$$this"] },
                { $arrayElemAt: ["$categories.sortOrder", { $subtract: ["$$this", 1] }] }
              ]
            }
          ]
        }
      }
    }
  }
}

playground