Convert SQL Query to MongoDB syntax

Question

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?

Tom Slabbaert · Accepted Answer · 2022-07-10 12:04:07Z

2

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

answered Jul 10, 2022 at 12:04

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

aliciavika Over a year ago

question, why do I have: "{$nin:" '$' -->SyntaxError: invalid syntax

Tom Slabbaert Over a year ago

if you're using python all operators should be encased with ", meaning $nin should be "$nin$". this is a python restriction unrelated to the MongDB language.

aliciavika Over a year ago

have this error, is there perhaps a more universal syntax ?:unknown operator: $nin$, full error: {'ok': 0.0, 'errmsg': 'unknown operator: $nin$', 'code': 2, 'codeName': 'BadValue'}

Tom Slabbaert Over a year ago

As you can see in the linked playground (mongoplayground.net/p/d0rWxMWpQ3p) the syntax is fine, just make sure you wrap every field and action name with "

aliciavika Over a year ago

Thanks again for your help, I added it to python, and it doesn't find me results, is it correct ? db.prodw.find( { "id": "KVV", "areo": "RSSO", "sto": {"$nin$": ["AMA", "BR"]}, "use": 1 }, {"sto": 1, "areo": 1, "use": 1} )

|

Collectives™ on Stack Overflow

Convert SQL Query to MongoDB syntax

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related