> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omniql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MongoDB

> Using OmniQL with MongoDB

MongoDB is a document database that stores data in flexible, JSON-like documents.

## Quick Start

```go theme={}
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")
```

## Type Mappings

| OmniQL      | MongoDB      |
| ----------- | ------------ |
| `AUTO`      | `ObjectId`   |
| `BIGAUTO`   | `ObjectId`   |
| `STRING`    | `String`     |
| `TEXT`      | `String`     |
| `CHAR`      | `String`     |
| `INT`       | `Int32`      |
| `BIGINT`    | `Int64`      |
| `SMALLINT`  | `Int32`      |
| `DECIMAL`   | `Decimal128` |
| `NUMERIC`   | `Decimal128` |
| `FLOAT`     | `Double`     |
| `REAL`      | `Double`     |
| `BOOLEAN`   | `Boolean`    |
| `BOOL`      | `Boolean`    |
| `TIMESTAMP` | `Date`       |
| `DATETIME`  | `Date`       |
| `DATE`      | `Date`       |
| `TIME`      | `String`     |
| `JSON`      | `Object`     |
| `JSONB`     | `Object`     |
| `UUID`      | `UUID`       |
| `BINARY`    | `BinData`    |
| `BLOB`      | `BinData`    |

## Entity Naming

OmniQL entities become collection names (lowercase):

| OmniQL      | MongoDB      |
| ----------- | ------------ |
| `User`      | `users`      |
| `Order`     | `orders`     |
| `OrderItem` | `orderitems` |

## Translation Examples

### CRUD Operations

**GET (find)**

```sql theme={}
:GET User WHERE id = 1
```

```javascript theme={}
db.users.find({ id: 1 })
```

```sql theme={}
:GET User WHERE age > 21 AND status = "active"
```

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

```sql theme={}
:GET id, name, email FROM User WHERE active = true
```

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

**CREATE (insertOne)**

```sql theme={}
:CREATE User WITH name = "John", email = "john@example.com"
```

```javascript theme={}
db.users.insertOne({ name: 'John', email: 'john@example.com' })
```

**BULK INSERT (insertMany)**

```sql theme={}
:BULK INSERT User WITH [name = "Alice", age = 28] [name = "Bob", age = 32]
```

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

**UPDATE (updateOne)**

```sql theme={}
:UPDATE User SET status = "active" WHERE id = 1
```

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

**Arithmetic in UPDATE**

```sql theme={}
:UPDATE User SET balance = balance + 100 WHERE id = 1
```

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

```sql theme={}
:UPDATE Product SET price = price * 0.9 WHERE category = "sale"
```

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

**DELETE (deleteOne)**

```sql theme={}
:DELETE User WHERE id = 1
```

```javascript theme={}
db.users.deleteOne({ id: 1 })
```

**UPSERT**

```sql theme={}
:UPSERT User WITH email = "john@example.com", name = "John" ON email
```

```javascript theme={}
db.users.updateOne(
  { email: 'john@example.com' },
  { $set: { email: 'john@example.com', name: 'John' } },
  { upsert: true }
)
```

**REPLACE (replaceOne)**

```sql theme={}
:REPLACE User WITH id = 1, name = "John", email = "john@example.com"
```

```javascript theme={}
db.users.replaceOne({ id: 1 }, { name: 'John', email: 'john@example.com' })
```

### Filtering (Operators)

| OmniQL        | MongoDB           |
| ------------- | ----------------- |
| `=`           | `$eq`             |
| `!=`          | `$ne`             |
| `>`           | `$gt`             |
| `>=`          | `$gte`            |
| `<`           | `$lt`             |
| `<=`          | `$lte`            |
| `IN`          | `$in`             |
| `NOT IN`      | `$nin`            |
| `LIKE`        | `$regex`          |
| `IS NULL`     | `$eq: null`       |
| `IS NOT NULL` | `$ne: null`       |
| `AND`         | implicit / `$and` |
| `OR`          | `$or`             |

**Examples:**

```sql theme={}
:GET User WHERE role IN ("admin", "moderator")
```

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

```sql theme={}
:GET User WHERE age BETWEEN 18 AND 65
```

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

```sql theme={}
:GET User WHERE name LIKE "John%"
```

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

```sql theme={}
:GET User WHERE role = "admin" OR role = "moderator"
```

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

### Pagination

```sql theme={}
:GET User ORDER BY created_at DESC LIMIT 10 OFFSET 20
```

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

### Aggregation

**COUNT**

```sql theme={}
:COUNT * FROM User WHERE active = true
```

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

**GROUP BY**

```sql theme={}
:COUNT * FROM User GROUP BY status
```

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

**SUM, AVG, MIN, MAX**

```sql theme={}
:SUM total FROM Order WHERE status = "completed"
```

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

**HAVING**

```sql theme={}
:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10
```

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

### Joins (\$lookup)

OmniQL joins translate to MongoDB's `$lookup` aggregation:

```sql theme={}
:INNER JOIN Order User ON Order.user_id = User.id
```

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

```sql theme={}
:LEFT JOIN Order User ON Order.user_id = User.id
```

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

### Window Functions (MongoDB 5.0+)

```sql theme={}
:ROW NUMBER OVER (PARTITION BY department ORDER BY salary) FROM Employee
```

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

**Supported window functions:**

* ROW NUMBER → `$documentNumber`
* RANK → `$rank`
* DENSE RANK → `$denseRank`
* LAG/LEAD → `$shift`

### Set Operations (MongoDB 4.4+)

```sql theme={}
:UNION (GET User WHERE age > 50) (GET User WHERE role = "premium")
```

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

### CASE Expressions

```sql theme={}
:GET User WITH CASE WHEN age > 25 THEN "adult" ELSE "minor" END AS category
```

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

### Transactions (Replica Set Required)

```sql theme={}
:BEGIN
:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2
:COMMIT
```

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

### Permissions

**CREATE USER:**

```sql theme={}
:CREATE USER analyst WITH PASSWORD secret123
```

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

**CREATE ROLE:**

```sql theme={}
:CREATE ROLE readonly
```

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

**GRANT ROLE:**

```sql theme={}
:ASSIGN ROLE readonly TO analyst
```

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

## Supported Operations

| Category         | Operations                                                | Support          |
| ---------------- | --------------------------------------------------------- | ---------------- |
| CRUD             | GET, CREATE, UPDATE, DELETE, UPSERT, BULK INSERT, REPLACE | Full             |
| Filtering        | All operators (=, !=, IN, BETWEEN, LIKE, IS NULL, etc.)   | Full             |
| Aggregation      | COUNT, SUM, AVG, MIN, MAX                                 | Full             |
| Grouping         | GROUP BY, HAVING                                          | Full             |
| Sorting          | ORDER BY, LIMIT, OFFSET                                   | Full             |
| Joins            | INNER, LEFT, RIGHT, FULL                                  | Via \$lookup     |
| Window Functions | ROW NUMBER, RANK, DENSE RANK, LAG, LEAD                   | MongoDB 5.0+     |
| Set Operations   | UNION, UNION ALL                                          | MongoDB 4.4+     |
| Expressions      | Arithmetic (+, -, \*, /, %), CASE WHEN                    | Full             |
| Functions        | UPPER, LOWER, CONCAT, LENGTH, ABS, ROUND                  | Full             |
| Transactions     | BEGIN, COMMIT, ROLLBACK                                   | Replica set only |
| DDL              | CREATE/DROP COLLECTION, RENAME, CREATE VIEW               | Full             |
| DCL              | CREATE/DROP USER, CREATE/DROP ROLE, GRANT, REVOKE         | Full             |

## Limitations

| Feature                  | Status        | Notes                             |
| ------------------------ | ------------- | --------------------------------- |
| SAVEPOINT                | Not supported | MongoDB has no savepoint concept  |
| ROLLBACK TO              | Not supported | No partial rollback               |
| RELEASE SAVEPOINT        | Not supported | No savepoints                     |
| INTERSECT                | Not supported | Use aggregation workarounds       |
| EXCEPT                   | Not supported | Use aggregation workarounds       |
| CTEs                     | Not supported | Use aggregation pipelines instead |
| Single-node transactions | Not supported | Requires replica set              |

## Version Requirements

| Feature           | Minimum MongoDB Version |
| ----------------- | ----------------------- |
| Basic CRUD        | 3.6+                    |
| Transactions      | 4.0+ (replica set)      |
| \$unionWith       | 4.4+                    |
| \$setWindowFields | 5.0+                    |

## Next Steps

<CardGroup cols={2}>
  <Card title="Redis" icon="bolt" href="/databases/redis">
    Redis specifics
  </Card>

  <Card title="Data Types" icon="shapes" href="/reference/data-types">
    Type mappings
  </Card>
</CardGroup>