Skip to main content
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.
Zero-SQL MongoDB Interface

​
Why Zero-SQL?

Familiar SQL Syntax

Use standard SQL SELECT, JOIN, WHERE, and GROUP BY statements with MongoDB

No Learning Curve

Leverage existing SQL knowledge without learning MongoDB query syntax

Intelligent Translation

AST-based converter ensures accurate and optimized MongoDB pipeline generation

High Performance

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 
SELECT name, email FROM users WHERE active = true
This means: 
[
  {
    "$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: 
SELECT "User Name", "Email Address" FROM users WHERE "Created Date" > '\''2023-01-01'\''
JOIN Operations Simple INNER JOIN 
SELECT u.name, p.title FROM users u JOIN posts p ON u.id = p.user_id
This means: 
[
  {
    "$lookup": {
      "from": "posts",
      "localField": "id",
      "foreignField": "user_id",
      "as": "p"
    }
  },
  {
    "$unwind": "$p"
  },
  {
    "$project": {
      "_id": 0,
      "name": "$u.name",
      "title": "$p.title"
    }
  }
]
LEFT JOIN 
SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id
This means: 
[
  {
    "$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 
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: 
[
  {
    "$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 
SELECT status, COUNT(*) as total FROM orders GROUP BY status
This means: 
[
  {
    "$group": {
      "_id": {
        "status": "$status"
      },
      "total": {
        "$sum": 1
      }
    }
  }
]
Complex WHERE Conditions 
SELECT * FROM products WHERE (price > 100 AND category = 'electronics') 
OR (price < 50 AND category = 'books')
LIKE and Pattern Matching 
SELECT name FROM users WHERE email ILIKE '%@gmail.com'
ORDER BY and LIMIT 
SELECT name, created_at FROM users ORDER BY created_at DESC LIMIT 10
Complex Investment Analysis Example 
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: 
[
  {
    "$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

​
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: 
// MongoDB shell
db.users.aggregate([{"$limit": 10}])

// Node.js with MongoDB driver
await db.collection("users").aggregate([{"$limit": 10}]).toArray()
⌘I