Skip to content
Advertisement

Convert SQL Query to MongoDB syntax

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

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

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

Advertisement

Answer

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


User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement