MongoDB

import (
    "context"
    
    "go.mongodb.org/mongo-driver/mongo"
    "go.mongodb.org/mongo-driver/mongo/options"
    "github.com/omniql-engine/omniql"
)

// Your MongoDB connection
mongoClient, _ := mongo.Connect(context.Background(),
    options.Client().ApplyURI("mongodb://localhost:27017"))
db := mongoClient.Database("mydb")

// Wrap with OmniQL
client := oql.WrapMongo(db)

// Query with OmniQL syntax
users, _ := client.Query(":GET User WHERE age > 21")

:GET User WHERE id = 1

db.users.find({ id: 1 })

:GET User WHERE age > 21 AND status = "active"

db.users.find({ age: { $gt: 21 }, status: 'active' })

:GET id, name, email FROM User WHERE active = true

db.users.find({ active: true }, { id: 1, name: 1, email: 1 })

:CREATE User WITH name = "John", email = "[email protected]"

db.users.insertOne({ name: 'John', email: '[email protected]' })

:BULK INSERT User WITH [name = "Alice", age = 28] [name = "Bob", age = 32]

db.users.insertMany([
  { name: 'Alice', age: 28 },
  { name: 'Bob', age: 32 }
])

:UPDATE User SET status = "active" WHERE id = 1

db.users.updateOne({ id: 1 }, { $set: { status: 'active' } })

:UPDATE User SET balance = balance + 100 WHERE id = 1

db.users.updateOne({ id: 1 }, { $inc: { balance: 100 } })

:UPDATE Product SET price = price * 0.9 WHERE category = "sale"

db.products.updateOne({ category: 'sale' }, { $mul: { price: 0.9 } })

:DELETE User WHERE id = 1

db.users.deleteOne({ id: 1 })

:UPSERT User WITH email = "[email protected]", name = "John" ON email

db.users.updateOne(
  { email: '[email protected]' },
  { $set: { email: '[email protected]', name: 'John' } },
  { upsert: true }
)

:REPLACE User WITH id = 1, name = "John", email = "[email protected]"

db.users.replaceOne({ id: 1 }, { name: 'John', email: '[email protected]' })

:GET User WHERE role IN ("admin", "moderator")

db.users.find({ role: { $in: ['admin', 'moderator'] } })

:GET User WHERE age BETWEEN 18 AND 65

db.users.find({ age: { $gte: 18, $lte: 65 } })

:GET User WHERE name LIKE "John%"

db.users.find({ name: { $regex: /^John/, $options: 'i' } })

:GET User WHERE role = "admin" OR role = "moderator"

db.users.find({ $or: [{ role: 'admin' }, { role: 'moderator' }] })

:GET User ORDER BY created_at DESC LIMIT 10 OFFSET 20

db.users.find({}).sort({ created_at: -1 }).skip(20).limit(10)

:COUNT * FROM User WHERE active = true

db.users.aggregate([
  { $match: { active: true } },
  { $group: { _id: null, result: { $sum: 1 } } }
])

:COUNT * FROM User GROUP BY status

db.users.aggregate([
  { $group: { _id: '$status', result: { $sum: 1 } } }
])

:SUM total FROM Order WHERE status = "completed"

db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $group: { _id: null, result: { $sum: '$total' } } }
])

:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10

db.users.aggregate([
  { $group: { _id: '$status', result: { $sum: 1 } } },
  { $match: { result: { $gt: 10 } } }
])

:INNER JOIN Order User ON Order.user_id = User.id

db.orders.aggregate([
  {
    $lookup: {
      from: 'users',
      localField: 'user_id',
      foreignField: '_id',
      as: 'users_joined'
    }
  },
  { $unwind: '$users_joined' }
])

:LEFT JOIN Order User ON Order.user_id = User.id

db.orders.aggregate([
  {
    $lookup: {
      from: 'users',
      localField: 'user_id',
      foreignField: '_id',
      as: 'users_joined'
    }
  },
  { $unwind: { path: '$users_joined', preserveNullAndEmptyArrays: true } }
])

:ROW NUMBER OVER (PARTITION BY department ORDER BY salary) FROM Employee

db.employees.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$department',
      sortBy: { salary: 1 },
      output: {
        rowNumber: { $documentNumber: {} }
      }
    }
  }
])

:UNION (GET User WHERE age > 50) (GET User WHERE role = "premium")

db.users.aggregate([
  { $match: { age: { $gt: 50 } } },
  { $unionWith: { coll: 'users', pipeline: [{ $match: { role: 'premium' } }] } }
])

:GET User WITH CASE WHEN age > 25 THEN "adult" ELSE "minor" END AS category

db.users.aggregate([
  {
    $project: {
      category: {
        $switch: {
          branches: [
            { case: { $gt: ['$age', 25] }, then: 'adult' }
          ],
          default: 'minor'
        }
      }
    }
  }
])

:BEGIN
:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2
:COMMIT

session.startTransaction();
db.accounts.updateOne({ id: 1 }, { $inc: { balance: -100 } });
db.accounts.updateOne({ id: 2 }, { $inc: { balance: 100 } });
session.commitTransaction();

:CREATE USER analyst WITH PASSWORD secret123

db.runCommand({ createUser: 'analyst', pwd: 'secret123', roles: [] })

:CREATE ROLE readonly

db.runCommand({ createRole: 'readonly', privileges: [], roles: [] })

:ASSIGN ROLE readonly TO analyst

db.runCommand({ grantRolesToUser: 'analyst', roles: ['readonly'] })