Use mongoose expression from inside array

Question

i have models look like, openPositionsDetail is array of object

const accountSummary = new Schema({
    account: {
        account: SchemaType.String,
        pialangCode: SchemaType.String,
        BranchOffice: SchemaType.String,
    },
    openPositionsDetail: [],
    qry_obj: {},
    idStatement: Number,
    createdBy: String

});

i want to get this document data if at least in openPositionsDetail.net not 0 (not 0, include negative number or positive number) this is my document look like

{
    "_id": ObjectId("5f2a35baec826fa4a9eaa186"),
    "openPositionsDetail": [
        {
            "buy": NumberInt("332"),
            "sell": NumberInt("310"),
            "locking": NumberInt("350"),
            "net": NumberInt("300"),
            "product": NumberInt("5"),
            "productName": "XUL10"
        },
        {
            "buy": NumberInt("5"),
            "sell": NumberInt("3"),
            "locking": NumberInt("350"),
            "net": NumberInt("0"),
            "product": NumberInt("5"),
            "productName": "HKK"
        }
    ],
    "account": {
        "account": "RZBJ2889",
        "pialangCode": "EWF",
        "BranchOffice": "SSC"
    },
    "idStatement": NumberInt("25009"),
    "createdBy": "RIZAL",
    "qry_obj": {
        "ntm": NumberInt("493500"),
        "dtm": NumberInt("283500")
    },
    "__v": NumberInt("0")
},
//2
{
    "_id": ObjectId("5f2a35a1ec826fa4a9ea41bb"),
    "openPositionsDetail": [
        {
            "buy": NumberInt("635"),
            "sell": NumberInt("1"),
            "locking": NumberInt("1"),
            "net": NumberInt("634"),
            "product": NumberInt("5"),
            "productName": "XUL10"
        }
    ],
    "account": {
        "account": "RDZ21797",
        "pialangCode": "RFB",
        "BranchOffice": "DBS"
    },
    "idStatement": NumberInt("486"),
    "createdBy": "RIZAL",
    "qry_obj": {
        "ntm": NumberInt("887810"),
        "dtm": NumberInt("444010")
    },
    "__v": NumberInt("0")
}

// 3
{
    "_id": ObjectId("5f2a35b6ec826fa4a9ea92ca"),
   "openPositionsDetail": [
        {
            "buy": NumberInt("1"),
            "sell": NumberInt("1"),
            "locking": NumberInt("5"),
            "net": NumberInt("0"),
            "product": NumberInt("5"),
            "productName": "XUL10"
        },
        {
            "buy": NumberInt("3"),
            "sell": NumberInt("3"),
            "locking": NumberInt("0"),
            "net": NumberInt("0"),
            "product": NumberInt("6"),
            "productName": "JPK"
        }
    ],
    "account": {
        "account": "RDW22175",
        "pialangCode": "RFB",
        "BranchOffice": "DBS"
    },
    "idStatement": NumberInt("21237"),
    "createdBy": "RIZAL",
    "qry_obj": {
        "ntm": NumberInt("572250"),
        "dtm": NumberInt("286650")
    },
    "__v": NumberInt("0")
}

this my query right now, but i am not sure this will work in array data

AccountSummary.aggregate([
          { $sort: { 'account.account':  1} },
          {
            $match: {
              $and: filter.concat({
                $expr: { $not: {"openPositionsDetail.net": 0} }  
              })
            },
          },
          {
            $facet: {
              "stage1": [{ "$group": { _id: null, count: { $sum: 1 } } }],
              "stage2": [{ "$skip": data.offset }, { "$limit": data.limit }]
            }
          },
          { $unwind: "$stage1" },
          {
            $project: {
              count: "$stage1.count",
              data: "$stage2"
            }
          }
        ])

im newcomer in mongodb

Answer 1

You can do as below

1. Unwind

2. Then check not 0 condition

3. Group back.

Update:

Working sample -

play

db.collection.aggregate([
  {
    "$unwind": "$openPositionsDetail"
  },
  {
    "$project": {
      "notZero": {
        "$ne": [
          "$openPositionsDetail.net",
          0
        ]
      },
      "openPositionsDetail": 1
    }
  },
  {
    "$match": {
      notZero: true
    }
  },
  {
    "$group": {
      "_id": "$_id",
      data: {
        $push: "$$ROOT"
      }
    }
  }
])