Convert SQL Query to MongoDB syntax-CodePudding

I have a big problem with converting SQL queries to Mongo DB for Python. I have a question if you know better methods for transferring SQL queries to Mongo DB queries?

I have two queries but I still have big problems with the syntax conversion to Mongo DB.
Maybe there is someone here who can help me?

Table 1

select sto, areo, use

from prodw

Where id='KV' and areo like 'RSSO' and sto not like 'AMA' and sto not like 'BR' and use =1

Table 2

SELECT substring(CLIENT_ID, 1, 4) as SL , Avg(x) as x, Avg(y) as y 
FROM [SLOT_USE] 

where substring(CLIENT_ID, 1, 3) like '%STK' or substring(CLIENT_ID, 1, 3) like 'CP1' or substring(CLIENT_ID, 1, 4) like 'LP1WA%'

group by substring(CLIENT_ID, 1, 4)

ORDER BY substring(CLIENT_ID, 1, 4) ASC

I have tried various converters but the result is poor.
Maybe there is some other method pandas or another faster tool?

CodePudding user response：

I personally do not know of any good converters, It's just easier to understand each query and translate it on your own, especially as the queries you posted here are not too complex.

Query 1:

{
    id: "KVV",
    areo: "RSSO",
    sto: {$nin: ["AMA", "BR"]},
    use: 1
}

Combining with the "select" options:

db.prodw.find(
    {
        id: "KVV",
        areo: "RSSO",
        sto: {$nin: ["AMA", "BR"]},
        use: 1
    },
    {sto: 1, areo: 1, use: 1}
)

Mongo Playground

Query 2:

db.collection.aggregate([
  {
    $match: {
      CLIENT_ID: {
        $regex: "^(STK|CP1|LP1WA)"
      }
    }
  },
  {
    $group: {
      _id: {
        "$substr": [
          "$CLIENT_ID",
          0,
          4
        ]
      },
      x: {
        $avg: "$x"
      },
      y: {
        $avg: "$y"
      },
      
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $project: {
      _id: 0,
      SL: "$_id",
      x: 1,
      y: 1
    }
  }
])

Mongo Playground