Please help to advise solution for query data

[Pipob_Piyaaromrat]

If I have the data

  {
    "productId": "1",
    "stock": {
      "updates": [
        {
          "status": "pending",
          "datetime": {
            "$date": "2023-11-26T04:25:07.363Z"
          },
          "by": "A"
        },
        {
          "status": "approved",
          "datetime": {
            "$date": "2023-11-26T05:47:10.501Z"
          },
          "by": "B"
        }
      ]
    }
  },
  {
    "productId": "2",
    "stock": {
      "updates": [
        {
          "status": "pending",
          "datetime": {
            "$date": "2023-11-26T04:25:07.363Z"
          },
          "by": "A"
        }
      ]
    }
  }

I will query data so check last index of array “stock.updates” by value { “status”: “pending” }
then result I want to show data only document { “productId”: “2”… }

Please help to advice the query solution

[steevej (Steeve Juneau)]

I do not know if it can be done with find() but with aggregate() the following should work.

function pipeline() {
    return [ set_last() , match_pending() , cleanup() ]
}

function set_last() {
    return { "$set" : { "_tmp.last" : { "$last" : "$stock.updates" } } }
}

function match_pending() {
    return { "$match" : { "_tmp.last.status" : "pending" } }
}

function cleanup() {
    return { "$unset" : "_tmp" }
}

db.products.aggregate( pipeline() )

The caveat about this solution is that no index can be used because it $match on a computed value. I do not even know if it is possible to come up with a query that can use an index with this schema. However by having a copy of the last status in a separate field, then the query is trivial and can use an index. Documents like:

{
    "productId": "1",
    "stock": {
      "last_status" : "approved" ,
      "updates": [
        {
          "status": "pending",
          "datetime": {
            "$date": "2023-11-26T04:25:07.363Z"
          },
          "by": "A"
        },
        {
          "status": "approved",
          "datetime": {
            "$date": "2023-11-26T05:47:10.501Z"
          },
          "by": "B"
        }
      ]
    }
  },
  {
    "productId": "2",
    "stock": {
      "last_status" : "pending" ,
      "updates": [
        {
          "status": "pending",
          "datetime": {
            "$date": "2023-11-26T04:25:07.363Z"
          },
          "by": "A"
        }
      ]
    }
  }

But your updates have to changed so that when you $push into stock.updates you also $set stock.last_status.

[Pipob_Piyaaromrat]

I successfully resolved the issue.
Initially, I attempted to find a solution by querying the find() function, but I couldn’t locate information on accessing the last array element using $last.
I appreciate your assistance in guiding me through the solution.

Thank you.