sorting - MongoDB sort by two possible properties

Question

I have a collection that contains properties, and each property can have an "override" value. Where overrides is a subcollection that stores subdocuments of override values.

I.e.

{
     "color":"red",
     "overrides": [
          {
                property:"color",
                value:"blue"
          }
      ]

}

There can only be 1 override subdocument per actualy property

I'd like to be able to sort by color - but....if a color override exists (then that will take precedence over the actual color property.

So in the example above, the value of blue would override the actual color property of "red" and would get sorted first.

So the algorithm I'm looking for is something like, "sort by color...but if a color override exists..then use that value instead".

I take it I'd have to "unroll" all values and then sort (using an aggregate)

Any ideas on how to do this and what will the performance impact be for ~2k documents (searching by just color is rather fast). Thanks!

question from:https://stackoverflow.com/questions/66051150/mongodb-sort-by-two-possible-properties

Answer 1

Waitting for answers