SQL JOIN → MongoDB $lookup

What it does

When you write SQL with one or more JOIN clauses, NoSqlStudio emits the equivalent aggregation pipeline: each JOIN becomes a $lookup stage followed by $unwind. Qualified column references (u.name, o.userId) are rewritten correctly — fields from the main collection drop the qualifier; fields from joined collections preserve the alias as a sub-document path.

How to open it

1. Press Ctrl+Alt+Q (or open Tools → SQL Query from the menu).
2. Type or paste your SQL into the left panel.
3. Click "Convert" — the MongoDB aggregation appears on the right, ready to copy into mongosh.

INNER JOIN — match required

INNER JOIN drops documents that have no match on the joined side (preserveNullAndEmptyArrays: false). Example:

SELECT u.name, COUNT(o._id) AS orderCount
FROM users u
INNER JOIN orders o ON u._id = o.userId
GROUP BY u.name

Translates to:

db.users.aggregate([
  { $lookup: { from: 'orders', localField: '_id', foreignField: 'userId', as: 'o' } },
  { $unwind: { path: '$o', preserveNullAndEmptyArrays: false } },
  { $group: { _id: '$name', orderCount: { $sum: { $cond: [{ $ne: ['$o._id', null] }, 1, 0] } } } },
  { $project: { _id: 0, name: '$_id', orderCount: 1 } }
])

LEFT JOIN — match optional

LEFT JOIN keeps documents with no match on the joined side (preserveNullAndEmptyArrays: true). Useful when you want all users plus their orders if they have any:

SELECT u.name, u.email, o.total, o.createdAt
FROM users u
LEFT JOIN orders o ON u._id = o.userId
WHERE u.tier = 'enterprise'

Chained JOINs

You can chain multiple JOINs. The localField of the N-th lookup can reference fields from any previously-joined alias:

SELECT u.name, o.total, p.title
FROM users u
INNER JOIN orders o ON u._id = o.userId
INNER JOIN products p ON o.productId = p._id

Limitations (v1)

• RIGHT JOIN / FULL OUTER JOIN / CROSS JOIN: not supported. $lookup has no native equivalent. Invert the sides (turn RIGHT JOIN into a LEFT JOIN of the reversed query) or write the pipeline by hand in mongosh.
• Multi-condition ON (AND/OR): not supported. Use the simpler form with a single equi-join condition, or rewrite using $lookup pipeline form in mongosh.
• USING (col): not supported. Use explicit ON a.col = b.col.
• Friendly errors: when one of the above shows up, the translator returns a specific message — not the cryptic parser error you would get from a generic translator.