> ## Documentation Index
> Fetch the complete documentation index at: https://docs.synehq.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Zero-SQL for MongoDB
> Write SQL queries against MongoDB databases with our intelligent AST-based converter
Zero-SQL transforms MongoDB querying by enabling SQL syntax directly against MongoDB collections. Our AST-based converter intelligently translates SQL queries into MongoDB aggregation pipelines, making MongoDB accessible to SQL developers while maintaining the full power of both query languages.
## Why Zero-SQL?
Use standard SQL SELECT, JOIN, WHERE, and GROUP BY statements with MongoDB
Leverage existing SQL knowledge without learning MongoDB query syntax
AST-based converter ensures accurate and optimized MongoDB pipeline generation
Built in Go for lightning-fast query processing and execution
## Supported SQL Features
Zero-SQL provides comprehensive SQL functionality with intelligent MongoDB translation:
* Column selection and wildcards (`SELECT name, email` or `SELECT *`)
* Column aliases (`SELECT name AS full_name`)
* Aggregation functions (`COUNT()`, `SUM()`, `AVG()`, `MIN()`, `MAX()`)
* Quoted column names for special characters
* INNER JOIN, LEFT JOIN, RIGHT JOIN
* Multiple table joins with aliases
* Complex join conditions
* Nested join operations
* Comparison operators (`=`, `!=`, `>`, `<`, `>=`, `<=`)
* Pattern matching (`LIKE`, `ILIKE`)
* List operations (`IN`, `NOT IN`)
* NULL checks (`IS NULL`, `IS NOT NULL`)
* Logical operators (`AND`, `OR`, `NOT`)
* Parenthetical grouping
* GROUP BY single or multiple columns
* HAVING clauses for filtered aggregations
* ORDER BY with ASC/DESC sorting
* LIMIT and OFFSET for pagination
## Query Examples
### Basic Operations
#### Examples
**Simple SELECT with WHERE**
```sql theme={"system"}
SELECT name, email FROM users WHERE active = true
```
This means:
```json theme={"system"}
[
{
"$match": {
"active": true
}
},
{
"$project": {
"_id": 0,
"name": "$name",
"email": "$email"
}
}
]
```
**Quoted Column Names**
Zero-SQL fully supports quoted identifiers for column names with spaces or special characters:
```sql theme={"system"}
SELECT "User Name", "Email Address" FROM users WHERE "Created Date" > '\''2023-01-01'\''
```
**JOIN Operations**
**Simple INNER JOIN**
```sql theme={"system"}
SELECT u.name, p.title FROM users u JOIN posts p ON u.id = p.user_id
```
This means:
```json theme={"system"}
[
{
"$lookup": {
"from": "posts",
"localField": "id",
"foreignField": "user_id",
"as": "p"
}
},
{
"$unwind": "$p"
},
{
"$project": {
"_id": 0,
"name": "$u.name",
"title": "$p.title"
}
}
]
```
**LEFT JOIN**
```sql theme={"system"}
SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id
```
This means:
```json theme={"system"}
[
{
"$lookup": {
"from": "posts",
"localField": "id",
"foreignField": "user_id",
"as": "p"
}
},
{
"$unwind": {
"path": "$p",
"preserveNullAndEmptyArrays": true
}
},
{
"$project": {
"_id": 0,
"name": "$u.name",
"title": "$p.title"
}
}
]
```
**Multiple JOINs**
```sql theme={"system"}
SELECT u.name, p.title, c.name as category FROM users u
JOIN posts p ON u.id = p.user_id
JOIN categories c ON p.category_id = c.id
```
This means:
```json theme={"system"}
[
{
"$lookup": {
"from": "posts",
"localField": "id",
"foreignField": "user_id",
"as": "p"
}
},
{
"$unwind": "$p"
},
{
"$lookup": {
"from": "categories",
"localField": "category_id",
"foreignField": "id",
"as": "c"
}
},
{
"$unwind": "$c"
},
{
"$project": {
"_id": 0,
"category": "$c.name",
"name": "$u.name",
"title": "$p.title"
}
}
]
```
**GROUP BY with Aggregation**
```sql theme={"system"}
SELECT status, COUNT(*) as total FROM orders GROUP BY status
```
This means:
```json theme={"system"}
[
{
"$group": {
"_id": {
"status": "$status"
},
"total": {
"$sum": 1
}
}
}
]
```
**Complex WHERE Conditions**
```sql theme={"system"}
SELECT * FROM products WHERE (price > 100 AND category = 'electronics')
OR (price < 50 AND category = 'books')
```
**LIKE and Pattern Matching**
```sql theme={"system"}
SELECT name FROM users WHERE email ILIKE '%@gmail.com'
```
**ORDER BY and LIMIT**
```sql theme={"system"}
SELECT name, created_at FROM users ORDER BY created_at DESC LIMIT 10
```
**Complex Investment Analysis Example**
```sql theme={"system"}
SELECT _id, "Date", "Description", "Operation", SUM("Amount") AS Total_Amount, COUNT(_id) AS Investment_Count, AVG("Amount") AS Average_Investment_Amount FROM investments WHERE "Date" BETWEEN '\''2023-01-01'\'' AND '\''2023-12-31'\'' AND "Operation" IN ('\''Deposit'\'', '\''Withdrawal'\'') GROUP BY _id, "Date", "Description", "Operation" ORDER BY Total_Amount DESC LIMIT 50;
```
This means:
```json theme={"system"}
[
{
"$match": {
"$and": [
{
"Date": {
"$gte": "2023-01-01",
"$lte": "2023-12-31"
}
},
{
"Operation": {
"$in": ["Deposit", "Withdrawal"]
}
}
]
}
},
{
"$group": {
"Average_Investment_Amount": {"$avg": "$Amount"},
"Investment_Count": {
"$sum": {
"$cond": [{"$ne": ["$_id", null]}, 1, 0]
}
},
"Total_Amount": {"$sum": "$Amount"},
"_id": {
"Date": "$Date",
"Description": "$Description",
"Operation": "$Operation",
"_id": "$_id"
}
}
},
{
"$sort": {"Total_Amount": -1}
},
{
"$limit": 50
}
]
```
### Supported SQL Features
#### SELECT Clause
* Column selection: `SELECT name, age`
* Wildcard: `SELECT *`
* Column aliases: `SELECT name AS full_name`
* Aggregation functions: `COUNT()`, `SUM()`, `AVG()`, `MIN()`, `MAX()`
#### FROM Clause
* Table references: `FROM users`
* Table aliases: `FROM users u`
#### JOIN Clause
* INNER JOIN: `INNER JOIN posts ON users.id = posts.user_id`
* LEFT JOIN: `LEFT JOIN posts ON users.id = posts.user_id`
* Table aliases in JOINs: `FROM users u JOIN posts p ON u.id = p.user_id`
* Multiple JOINs: `FROM users u JOIN posts p ON u.id = p.user_id JOIN categories c ON p.category_id = c.id`
* JOINs with WHERE conditions: `FROM users u JOIN posts p ON u.id = p.user_id WHERE u.active = true`
#### WHERE Clause
* Comparison operators: `=`, `!=`, `<>`, `>`, `<`, `>=`, `<=`
* Pattern matching: `LIKE`, `ILIKE`
* List operations: `IN`, `NOT IN`
* NULL checks: `IS NULL`, `IS NOT NULL`
* Logical operators: `AND`, `OR`, `NOT`
* Parentheses for grouping: `(condition1 OR condition2) AND condition3`
#### GROUP BY Clause
* Single column: `GROUP BY status`
* Multiple columns: `GROUP BY category, status`
* Works with aggregation functions in SELECT
#### HAVING Clause
* Filter aggregated results: `HAVING COUNT(*) > 5`
* Supports same operators as WHERE clause
#### ORDER BY Clause
* Ascending: `ORDER BY name` or `ORDER BY name ASC`
* Descending: `ORDER BY created_at DESC`
* Multiple columns: `ORDER BY category, name DESC`
#### LIMIT and OFFSET
* Limit results: `LIMIT 10`
* Skip results: `OFFSET 20` or `LIMIT 20, 10`
### MongoDB Output
\`zero-sql\` generates MongoDB aggregation pipelines using these stages:
* `$lookup` - for JOIN operations
* `$unwind` - to flatten joined arrays
* `$match` - for WHERE and HAVING clauses
* `$group` - for GROUP BY clauses
* `$project` - for SELECT column specification
* `$sort` - for ORDER BY clauses
* `$skip` - for OFFSET
* `$limit` - for LIMIT
### Error Handling
Zero-SQL provides detailed error messages for:
* Invalid SQL syntax
* Unsupported SQL features
* Type mismatches
* Missing required clauses
### Limitations
Current limitations include:
* Only SELECT statements are supported
* Subqueries are not yet supported
* Window functions are not supported
* Some advanced SQL features may not be available
### Contributing
Contributions are welcome! Please see the Contributing Guide for details. [https://github.com/SyneHQ/zero-sql](https://github.com/SyneHQ/zero-sql/tree/sudo)
### Architecture
The converter package is the heart of the application, handling the conversion from SQL Abstract Syntax Trees (AST) to MongoDB aggregation pipelines.
### Troubleshooting
#### MongoDB Aggregation Namespace Error
If you encounter an error like `(InvalidNamespace) {aggregate: 1} is not valid for '$limit'; a collection is required`, this means that when executing the generated aggregation pipeline in MongoDB, the collection name is not being specified correctly.
Then, in your MongoDB client/driver, use the collection name when executing the aggregation:
```bash theme={"system"}
// MongoDB shell
db.users.aggregate([{"$limit": 10}])
// Node.js with MongoDB driver
await db.collection("users").aggregate([{"$limit": 10}]).toArray()
```