CODEWITHSUNDEEP
javascriptnode.jsmongodb
kgommers
kgommers

Reputation: 74

How to get last defined value in an aggregration in MongoDB?

I have a collection in which the documents sometimes contain a status field and sometimes don't. There is always a data field. I'm trying to form a query to get the latest value for both, but when using the $last operator, I get the values from the latest document and results in status sometimes being null. How can I get it to retrieve the latest defined status value, while still keeping the data value from the latest document?

Current aggregration:

  const project = await collection.aggregate([
    {
      $match: {
        projectId: id
      }
    },
    {
      $group: {
        _id: '$projectId',
        status: {
          $last: '$status'
        },
        data: {
          $last: '$data'
        }
      }
    }
  ]).toArray();

Upvotes: 1

Views: 31

Answers (1)

NeNaD
NeNaD

Reputation: 20344

You can use $facet and perform multiple query in the parallel on the same set of documents.

db.collection.aggregate([
  {
    $facet: {
      last_status: [
        {
          "$match": {
            status: {
              $ne: null
            }
          }
        },
        {
          "$sort": {
            _id: -1
          }
        },
        {
          "$limit": 1
        }
      ],
      last_data: [
        {
          "$match": {
            data: {
              $ne: null
            }
          }
        },
        {
          "$sort": {
            _id: -1
          }
        },
        {
          "$limit": 1
        }
      ]
    }
  },
  {
    "$project": {
      other_fields: {
        $first: "$last_data"
      },
      status: {
        $first: "$last_status.status"
      }
    }
  }
])

Working example

Upvotes: 1

Related Questions