Feature: Aggregation Pipeline ↔ SQL Joins/Group By Support

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.

---