SQL Mode for MongoDB

SQL Mode is a translation layer that lets you query your MongoDB collections using familiar SQL syntax. Under the hood, VisuaLeaf parses your SQL and translates it into an equivalent MongoDB find() or aggregation pipeline that runs against your MongoDB server. Great for developers coming from a relational background who want to explore MongoDB without learning the native query DSL first.

Interface Overview

SQL Editor Panel

The left panel contains the Monaco SQL editor with advanced features:

• Syntax Highlighting - Color-coded SQL syntax for readability
• Auto-completion - Context-aware suggestions for SQL keywords, collection names, and field names
• Error Detection - Real-time validation with inline error markers
• Format on Paste - Automatic indentation and formatting

Results Panel

The right panel displays results in tabbed interface:

• MongoDB Query Tab - Shows the translated MongoDB query or aggregation pipeline
• Result Tab - Displays query results in Tree View, Table View, or BSON View
• View Toggle - Switch between different result visualization modes
• Pagination - Navigate through large result sets efficiently

Toolbar

Control buttons and actions:

• Run Query Button - Execute the SQL query (F5)
• SQL Helper Button - Open comprehensive SQL reference (F1)
• Create Chart - Generate a chart from query results
• Save Query Dropdown - Save, load, export, and import queries
• Query History - Access previously executed SQL queries
• Layout Toggle - Switch between horizontal and vertical split

SQL to MongoDB Translation

VisualLeaf automatically translates your SQL queries into MongoDB operations in real-time:

Translation Types

• Simple Queries - SELECT statements translate to db.collection.find()
• Aggregations - GROUP BY, JOIN, and functions translate to aggregation pipelines
• Filtering - WHERE clauses become MongoDB filter objects
• Sorting - ORDER BY converts to MongoDB sort specifications
• Pagination - LIMIT and OFFSET map to skip/limit

Example Translations

SQL Query	MongoDB Translation
SELECT * FROM users	db.users.find({})
SELECT name, email FROM users WHERE age >= 18	db.users.find({age: {$gte: 18}}, {name: 1, email: 1})
SELECT COUNT(*) FROM orders GROUP BY status	db.orders.aggregate([{$group: {_id: "$status", count: {$sum: 1}}}])

Supported SQL Syntax

VisualLeaf supports a comprehensive subset of SQL syntax optimized for MongoDB operations:

SELECT Statements

• Select All - SELECT * FROM collection
• Select Fields - SELECT field1, field2, field3 FROM collection
• Field Aliases - SELECT name AS userName, age AS userAge FROM users
• Distinct - SELECT DISTINCT status FROM orders

WHERE Clauses

Filter data using standard SQL operators:

• Comparison - =, !=, <, >, <=, >=
• Logical - AND, OR, NOT
• Range - BETWEEN x AND y, NOT BETWEEN
• Lists - IN (value1, value2), NOT IN
• Pattern Matching - LIKE 'pattern%', NOT LIKE
• NULL Checks - IS NULL, IS NOT NULL

ORDER BY

• Ascending - ORDER BY field ASC
• Descending - ORDER BY field DESC
• Multiple Fields - ORDER BY field1 ASC, field2 DESC

LIMIT and OFFSET

• Limit Results - LIMIT 10
• Pagination - LIMIT 10, 20 (skip 10, return 20)
• Offset - OFFSET 10 LIMIT 20

Aggregate Functions

• COUNT - SELECT COUNT(*) FROM collection
• SUM - SELECT SUM(amount) FROM orders
• AVG - SELECT AVG(price) FROM products
• MIN / MAX - SELECT MIN(price), MAX(price) FROM products
• FIRST / LAST - Get first or last value in groups

GROUP BY and HAVING

• Group By - SELECT status, COUNT(*) FROM orders GROUP BY status
• Multiple Fields - GROUP BY field1, field2
• Having Filter - HAVING COUNT(*) > 10

JOIN Operations

• INNER JOIN - SELECT * FROM users INNER JOIN orders ON users._id = orders.user_id
• LEFT JOIN - SELECT * FROM users LEFT JOIN orders ON users._id = orders.user_id
• Multiple JOINs - Chain multiple collections together

Supported Functions

VisualLeaf supports a wide range of SQL functions that are translated to MongoDB aggregation operators:

String Functions

• CONCAT - Concatenate strings: CONCAT(first_name, " ", last_name)
• UPPER / LOWER - Convert case: UPPER(name)
• SUBSTRING - Extract substring: SUBSTRING(text, 1, 10)
• TRIM / LTRIM / RTRIM - Remove whitespace
• LENGTH - String length: LENGTH(name)
• REPLACE - Replace text: REPLACE(phone, "-", "")
• LEFT / RIGHT - Get leftmost or rightmost characters
• REVERSE - Reverse a string

Math Functions

• ROUND - Round to decimals: ROUND(price, 2)
• FLOOR / CEIL - Round down or up to integer
• ABS - Absolute value: ABS(balance)
• POW / SQRT - Power and square root
• MOD - Modulo operation: MOD(value, 10)
• TRUNC - Truncate without rounding
• EXP / LN / LOG / LOG10 - Exponential and logarithm functions

Date Functions

• YEAR / MONTH / DAY - Extract date components: YEAR(created_at)
• HOUR / MINUTE / SECOND - Extract time components
• NOW - Current date/time: WHERE created_at > NOW()
• DATE_FORMAT - Format dates: DATE_FORMAT(date, "%Y-%m-%d")
• DATE_ADD / DATE_SUB - Add or subtract intervals
• DATEDIFF - Difference between dates
• DAYOFWEEK / DAYOFYEAR / WEEK - Date calculations

Conditional Functions

• IF - Conditional value: IF(active, "Yes", "No")
• IFNULL - Null replacement: IFNULL(nickname, "Anonymous")
• COALESCE - First non-null: COALESCE(nick, first, "Unknown")
• NULLIF - Return null if equal: NULLIF(value, 0)
• CASE - Multi-branch logic
• GREATEST / LEAST - Max or min from list

Array Functions (MongoDB-specific)

• SIZE_OF_ARRAY - Array length: SIZE_OF_ARRAY(tags)
• UNWIND - Flatten arrays: SELECT UNWIND(items) FROM orders
• FIRST_IN_ARRAY / LAST_IN_ARRAY - Get first or last element
• ARRAY_ELEM_AT - Get element at index
• CONCAT_ARRAYS - Merge arrays
• SUM_ARRAY / AVG_ARRAY - Aggregate array values
• REVERSE_ARRAY - Reverse array order

Type Conversion

• CAST - Convert types: CAST(price AS STRING)
• TO_STRING / TO_DATE - Type conversion functions
• OBJECTID / ISODATE / UUID - MongoDB type constructors

MongoDB Types in SQL

Use MongoDB-specific types directly in your SQL queries:

ObjectId

Query by MongoDB ObjectId:

SELECT * FROM users WHERE _id = 'ObjectId("507f1f77bcf86cd799439011")'

ISODate

Use ISO date format for date comparisons:

SELECT * FROM orders
WHERE created_at >= 'ISODate("2023-01-01T00:00:00.000Z")'
AND created_at < 'ISODate("2024-01-01T00:00:00.000Z")'

NumberLong and NumberDecimal

Handle large integers and precise decimals:

SELECT * FROM transactions WHERE amount = 'NumberLong("9007199254740993")'
SELECT * FROM prices WHERE value = 'NumberDecimal("19.99")'

UUID

Query by UUID fields:

SELECT * FROM sessions WHERE session_id = 'UUID("550e8400-e29b-41d4-a716-446655440000")'

Regular Expressions

Use regex patterns for advanced matching:

SELECT * FROM products WHERE name = '/^iPhone/i'

SQL Helper Reference

Press F1 or click the Help button to open the comprehensive SQL Helper modal with interactive examples.

Helper Tabs

Tab	Contents
Basics	SELECT statements, WHERE clauses, ORDER BY, LIMIT
Operators	Comparison, range, pattern matching (LIKE), NULL operators
Functions	String, math, date, and conditional functions
Aggregation	COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
JOINs	INNER JOIN, LEFT JOIN examples and patterns
Types	MongoDB types: ObjectId, ISODate, NumberLong, UUID, regex
Advanced	Array functions, object operations, CASE expressions

Example Actions

Each example in the SQL Helper includes:

• Copy Button - Copy SQL to clipboard
• Insert Button - Insert SQL directly into editor
• MongoDB Output - See the equivalent MongoDB operation
• Description - Clear explanation of what the query does

Limitations and Differences

While VisualLeaf supports most common SQL operations, there are some differences from standard SQL due to MongoDB's document-oriented nature:

No Support For

• Transactions - No BEGIN, COMMIT, ROLLBACK in queries (use MongoDB transactions directly)
• Data Modification - No INSERT, UPDATE, DELETE statements (use Collection Activity for CRUD)
• Schema Changes - No CREATE TABLE, ALTER TABLE, DROP TABLE
• RIGHT JOIN / FULL OUTER JOIN - Only INNER JOIN and LEFT JOIN supported
• Subqueries in WHERE - Limited subquery support

MongoDB-Specific Behaviors

• Collection Names - FROM clause uses collection names, not table names
• Field Names - Fields can contain dots for nested documents (e.g., address.city)
• Array Fields - Arrays are first-class citizens; use array functions
• Case Sensitivity - Field names are case-sensitive in MongoDB
• NULL vs Missing - MongoDB distinguishes between null and missing fields

Performance Considerations

• COUNT(*) - May be slow on large collections without proper indexes
• JOINs - Translated to $lookup which can be expensive; use indexes on join fields
• LIKE patterns - Translate to regex; use indexes for prefix patterns (LIKE 'prefix%')
• ORDER BY - Requires indexes for optimal performance on large collections

Common Query Examples

Simple SELECT

-- Get all active users
SELECT * FROM users WHERE status = 'active'

-- Get specific fields
SELECT name, email, created_at FROM users WHERE age >= 18

Aggregation and Grouping

-- Count orders by status
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status

-- Average order value by customer
SELECT customer_id, AVG(total) AS avg_order, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000
ORDER BY total_spent DESC

JOINs

-- Get users with their orders
SELECT users.name, orders.total, orders.status
FROM users
INNER JOIN orders ON users._id = orders.user_id
WHERE orders.status = 'pending'

-- Include users without orders
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users._id = orders.user_id

Date Filtering

-- Orders from last 30 days
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)

-- Orders in 2023
SELECT * FROM orders
WHERE YEAR(created_at) = 2023

String Operations

-- Full name from parts
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       UPPER(email) AS email_upper
FROM users
WHERE email LIKE '%@gmail.com'

Array Operations

-- Products with tag count
SELECT name, SIZE_OF_ARRAY(tags) AS tag_count
FROM products
WHERE SIZE_OF_ARRAY(tags) > 0

-- Flatten array of items
SELECT order_id, UNWIND(items) AS item
FROM orders

Keyboard Shortcuts

Shortcut	Action
F5	Execute SQL query
F1	Open SQL Helper modal with examples and reference

Note: On macOS, keyboard shortcuts work the same (F1, F5)

Query Management

Save and organize your SQL queries for reuse:

Save Query Dropdown

• New Query - Clear the editor and start a new SQL query.
• Save - Save the current query. Prompts for name if not saved before.
• Save As - Save as a new query with a different name.
• Load - Open a previously saved SQL query from your library.
• Export - Export query to JSON file for backup or sharing.
• Import - Import a query from JSON file.

Query History

Click the History button to access previously executed SQL queries:

• Automatic Recording - All executed queries are saved automatically
• Search - Filter history by SQL content or execution date
• Preview - View query before loading
• Load to Editor - Click to load into current editor
• Type Filter - History shows only SQL queries (not shell scripts)

Create Charts from Results

Transform your query results into visual charts with one click:

How to Create a Chart

1. Execute a SQL query that returns data
2. Click the Create Chart button in the toolbar
3. The Chart Builder opens with your data pre-loaded
4. Select chart type and configure field mappings
5. Save to your dashboard or view immediately

Supported Query Types

• Simple Queries - Creates chart from find() results
• Aggregations - Creates chart from aggregation pipeline output
• Grouped Data - Perfect for GROUP BY queries with counts/sums

Pro Tips

1. Press F1 to open the SQL Helper with hundreds of copyable examples. Use the Insert button to paste directly into your editor.
2. Always check the MongoDB Query tab to learn how your SQL translates to MongoDB operations. Great for learning MongoDB!
3. Use LIMIT with large datasets to avoid slow queries. Start with LIMIT 100 to preview results quickly.
4. Create indexes on fields used in WHERE, ORDER BY, and JOIN conditions for optimal performance.
5. For date filtering, use ISODate() format for precise control: WHERE date >= 'ISODate("2023-01-01")'
6. Use MongoDB type constructors like ObjectId() when filtering by _id fields for proper type matching.
7. Save frequently used queries to build a library of common operations for your team.
8. Toggle the layout orientation (horizontal/vertical) to match your screen size and query complexity.

Related Features

• MongoDB Shell - Execute native MongoDB commands and JavaScript for advanced operations
• Collection Activity - Visual query builder with drag-drop interface
• Aggregation Activity - Build complex aggregation pipelines visually
• Chart Builder - Create visualizations from query results