Ignore null values when sorting by array in MongoDB

Question

I have MongoDB collection items with following documents:

{ "values" : [1, 2] }
{ "values" : [5, null] }
{ "values" : [-5, null] }

Is there any way to sort this collection by values property without null values? My current query is:

db.items.aggregate([{"$sort": {"values": 1}}])

with this result:

{ "values" : [5, null] }
{ "values" : [-5, null] }
{ "values" : [1, 2] }

First values are [5, null] and [-5, null] because null is smallest value in collection. However, I would like to ignore null values and sort by numbers only, so:

{ "values" : [-5, null] }
{ "values" : [1, 2] }
{ "values" : [5, null] }

When there is no number (both values are null), document should be last in result.

turivishal · Accepted Answer · 2020-08-14 11:39:15Z

3

You can try this,

$addFields to create values_clone of values using $map, replace null to string "null", because when we sort it will sort last

db.collection.aggregate([
  {
    $addFields: {
      values_clone: {
        $map: {
          input: "$values",
          as: "v",
          in: {
            $cond: {
              if: { $eq: ["$$v", null] },
              then: "null",
              else: "$$v"
            }
          }
        }
      }
    }
  },

$sort by values_clone ascending order

  { $sort: { values_clone: 1 } },

$project to hide values_clone

  { $project: { values_clone: 0 } }
])

Playground

edited Aug 14, 2020 at 11:39

answered Aug 14, 2020 at 10:50

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ambianBeing Over a year ago

A fine solution as well (liked the approach of values_clone as tag along). But this also loses the document if both elements of values are [null, null].

turivishal Over a year ago

@ambianBeing, thanks, yes i realize that will do some solution for that, you have too nice solutions

ambianBeing · Accepted Answer · 2020-08-14 10:40:46Z

Other alternatives:

Solution 1: In case preservation of all null values[] or null fields in the array itself is not necessary.

Filter out the not null elements using $filter, the ( all null elements) array would be empty, filter that out from documents using $match then $sort on values.

db.collection.aggregate([
  {
    $project: {
      values: {
        $filter: {
          input: "$values",
          as: "d",
          cond: {
            $ne: [
              "$$d",
              null
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      "values": {
        $ne: []
      }
    }
  },
  {
    "$sort": {
      "values": 1
    }
  }
]);

Play Link

Solution 2: Not a great approach IMO. But gets the job done and preserves the documents and null values as it is.

First convert null values to "null" strings for the $sort order to work based on field type. Convert back the "null" strings to null using $map

db.collection.aggregate([
  {
    $project: {
      values: {
        $map: {
          input: "$values",
          as: "d",
          in: {
            $cond: {
              if: {
                $eq: [
                  "$$d",
                  null
                ]
              },
              then: "null",
              else: "$$d"
            }
          }
        }
      }
    }
  },
  {
    "$sort": {
      "values": 1
    }
  },
  {
    $project: {
      values: {
        $map: {
          input: "$values",
          as: "d",
          in: {
            $cond: {
              if: {
                $eq: [
                  "$$d",
                  "null"
                ]
              },
              then: null,
              else: "$$d"
            }
          }
        }
      }
    }
  }
]);

Play Link

Gibbs · Accepted Answer · 2020-08-14 09:56:44Z

1

You can do using the below stages

play

db.collection.aggregate([
  {
    $unwind: "$values" //unwind them to skip null values
  },
  {
    $match: {
      "values": { //skip null values
        $ne: null
      }
    }
  },
  {
    "$sort": { //sorting pipeline
      "values": 1
    }
  },
  {
    $group: { //regrouping them
      "_id": "$_id",
      "values": {
        $push: "$values"
      }
    }
  }
])

If you want to ignore complete array even if one value is null, you can skip unwind and group stages.

answered Aug 14, 2020 at 9:56

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

Collectives™ on Stack Overflow

Ignore null values when sorting by array in MongoDB

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related