how to convert mysql query to mongodb aggregation

my database in mysql

i need to convert mysql to mongodb query

SELECT * FROM `auctions` WHERE ( (status!='SCHEDULED' AND status='FAILED') OR (status!='RUNNING' AND status='FAILED') ) GROUP BY auctioncode

in this query auctioncode and status are wise record needed auctioncode is multiple records. i need status='FAILED' that auctioncode status not in SCHEDULED or UNPAID

CodePudding user response：

Something like below can be done pls refer here for example query: https://mongoplayground.net/p/8zHYhXvbkxs

db.auctions.aggregate([
  {
    "$match": {
      "$or": [
        {
          "$and": [
            {
              status: {
                $ne: "SCHEDULED"
              }
            },
            {
              status: "FAILED"
            }
          ]
        },
        {
          "$and": [
            {
              status: {
                $ne: "RUNNING"
              }
            },
            {
              status: "FAILED"
            }
          ]
        }
      ]
    }
  },
  {
    "$group": {
      "_id": "$auctioncode",
      "auctioncode": {
        "$first": "$auctioncode"
      },
      "Details": {
        "$addToSet": "$$ROOT"
      }
    }
  }
])

Pipeline stages: match: to filter based on your requirement! group: to do group by auctioncode