How can I sort field if another filed is not null?

Question

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
  }
]

Does this answer your question? [MongoDB sort by field A if field B != null otherwise sort by field C](https://stackoverflow.com/questions/54810712/mongodb-sort-by-field-a-if-field-b-null-otherwise-sort-by-field-c) — Irfan Bilir, Jul 02 '22 at 13:28
One thing to note is that pin_order is a number whereas created is a date. How do you want to handle this? First all items where pin is not null by pin_order, then all items where pin is null by created? — Markus, Jul 02 '22 at 15:14

score 1 · Accepted Answer · answered Jul 02 '22 at 15:57

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.

Thank you, I think `else: 999999` can be replace with `else : "$$REMOVE"`. — user10965730, Jul 03 '22 at 06:16
@user10965730 you're welcome. If you use `$$REMOVE`, the without a pin date will not include the `sortField`. When sorting, this is considered as an empty value and the order is changed - the not pinned documents come first. As far as I understand your requirements, the pinned items should come first. In this case I'd propse to use the large number to put the items without a pin after the ones that have bin pinned. — Markus, Jul 04 '22 at 06:25

How can I sort field if another filed is not null?

1 Answers1