Feature: Aggregation Pipeline ↔ SQL Joins/Group By Support

[hoangsonww]

Summary
Extend the converter to handle SQL joins, GROUP BY, and aggregate functions and map them to MongoDB’s aggregation pipeline (and vice versa). This expands beyond basic SELECT/WHERE/LIMIT to cover real-world analytics and reporting queries.

---

Why

• Current library is great for simple CRUD-like conversions, but most production workloads need aggregation.
• Developers migrating between relational DBs and MongoDB often need to convert joins, grouping, and aggregates.
• Bridges a major feature gap: SQL analytics → Mongo $lookup, $group, $project, $sort, $limit.

---

Scope (MVP)

SQL → Mongo

• Recognize JOIN clauses → translate to $lookup (with as, localField, foreignField).

• Translate GROUP BY + aggregates:

  • COUNT(*) → { $sum: 1 }
  • SUM(col) → { $sum: "$col" }
  • AVG(col) → { $avg: "$col" }
  • MIN/MAX(col) → { $min: "$col" } / { $max: "$col" }.

• Support ORDER BY, LIMIT, OFFSET → $sort, $limit, $skip.

Example:

SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30
GROUP BY u.name
ORDER BY total_orders DESC
LIMIT 5;

→

[
  {"$match": {"age": {"$gt": 30}}},
  {"$lookup": {
    "from": "orders",
    "localField": "id",
    "foreignField": "user_id",
    "as": "orders"
  }},
  {"$unwind": "$orders"},
  {"$group": {"_id": "$name", "total_orders": {"$sum": 1}}},
  {"$sort": {"total_orders": -1}},
  {"$limit": 5}
]

---

Mongo → SQL

• Convert $lookup to JOIN (inner or left outer depending on preserveNullAndEmptyArrays).
• Convert $group to GROUP BY + aggregates.
• Convert $project to SELECT expressions.
• Convert $sort, $skip, $limit accordingly.

---

Acceptance Criteria

• SQL JOIN + GROUP BY queries convert to valid MongoDB pipelines.

• Mongo pipelines with $lookup + $group roundtrip back into equivalent SQL.

• Tests include:

  • Simple join (users → orders).
  • Aggregation only (SUM/COUNT).
  • Combination of WHERE, JOIN, GROUP BY, ORDER BY, LIMIT.

---

Implementation Notes

• Update parser to capture JOIN and GROUP BY.
• Extend sql_to_mongo to emit aggregation arrays (instead of simple find).
• Add mongo_to_sql mappings for $lookup and $group.
• Backward compatibility: if no joins/aggregates, keep current simpler behavior.
• Add mode="pipeline" option to force full pipeline output even for simple queries.

---

Tasks

• Extend SQL parser to handle JOIN and GROUP BY.
• Implement SQL → Mongo $lookup mapping.
• Implement SQL → Mongo $group (COUNT, SUM, AVG, MIN, MAX).
• Implement Mongo $lookup/$group → SQL JOIN/GROUP BY.
• Add roundtrip unit tests.
• Update README with aggregation examples.

---