How can I sort field if another filed is not null? I have data like the following and I want sort by pinOrder, pin, created. But I want to sort based on pinOrder only pin field not null, If pin field is null, just sort data by created.

Can this be solved by an aggregation pipeline?

[
  {
    "id": "62b9c1d74321afff4cb4e652",
    "created": "2022-06-27T14:42:31.726Z",
    "pin": "2022-06-27T14:42:31.703Z",
    "pin_order": 1
  },
  {
    "id": "62b55d6ee0e1fa12861f8cb8",
    "created": "2022-06-24T06:45:02.126Z",
    "pin": null,
    "pin_order": 1
  },
  {
    "id": "629a212eda64722e286d29f6",
    "created": "2022-06-03T14:56:46.980Z",
    "pin": null,
    "pin_order": 1
  }
]

CodePudding user response：

Yes, this can be solved using an aggregation pipeline. If you want to retrieve pinned items first, followed by the rest in order of creation, you can use the following pipeline (playground):

[
  {
    $set: {
      "sortField": {
        $cond: {
          if: {
            $ne: [
              "$pin",
              null
            ]
          },
          then: "$pin_order",
          else: 9999999
        }
      }
    }
  },
  {
    $sort: {
      sortField: 1,
      created: 1
    }
  },
  {
    $unset: "sortField"
  }
]

This pipeline creates an artificial field for sorting; if an item is pinned, the pin_order value is used, otherwise a value with a high number (higher than the expected pin_order values) is used so that all items that are not pinned will be added after the pinned items.

You could optimize this by using a high number as default for the pin_order field instead of 1 as in your sample data. Then there is no need for an aggregation pipeline and you can sort the data directly. As soon as an item is pinned, you assign a lower value to the pin_order property.