This project is a part of the "Databases' university subject. This is a simple app where the user can
query the Mongo database using SQL. Our task was to give an object representation of an SQL query,
validate it and parse it to a Mongo query. Then we query the Mongo database and show the results.
The pipeline can be seen in the app/AppCore file. It consists of:
- Representing the query as an SQL object (sql package)
- Validating the query (validator package)
- Adapting the query for Mongo (adapter package)
- Querying the Mongo DB (database package)
If we wish to show a column that is not from a table we're querying, the table name needs
to be given as well. Example:
select last_name, departments.department_name, locations.street_address
from hr.employees join departments using (department_id) join locations using (location_id)
where last_name like 'P%'
Our code accepts an sql select statement:
clause | parameters |
---|---|
SELECT | multiple parameters with aggregate functions, no aliases |
FROM | up to 2 joins |
WHERE | either multiple inequalities or 1 simple subquery ('=' and 'in') |
HAVING | not supported |
GROUP BY | multiple parameters |
ORDER BY | multiple parameters asc and desc |
The query cannot at the same time have a join and a subquery.
It complicates the mongo query (introduces a $pipeline to our $aggregate pipeline).
Subqueries are simple, like:
SELECT A FROM B WHERE C = 10
select first_name, last_name from employees where department_id in (
select department_id from employees where employee_id = 103 or employee_id = 156)
db.employees.aggregate(
{ $lookup: {
from: "employees",
localField: "department_id",
foreignField: "department_id",
as: "result"
}
}
{ $unwind: "$result" },
{ $match: {
$or: [ { "result.employee_id": {$eq: 103} }, { "result.employee_id": {$eq: 156} } ] }
},
{ $project: {
first_name: 1,
last_name: 1,
_id: 0
}
}
)
select job_id, avg(salary) from employees group by job_id
db.employees.aggregate(
{ $project:
{job_id: "$_id.job_id", avgsalary: 1, _id: 0 }
}
)
select last_name, department_name, street_address from hr.employees join hr.departments using (department_id)
join hr.locations using (location_id) where last_name like 'P%'
db.employees.aggregate(
{ $lookup: {
from: "hr.departments",
localField: "department_id",
foreignField: "department_id",
as: "result1"
}
},
{ $unwind: "$result1" },
{
$lookup: {
from: "hr.locations",
localField: "location_id",
foreignField: "location_id",
as: "result2"
}
},
{ $unwind: "$result2" },
{ $match: {
last_name: {$regex: /^p.*/i}
}
},
{ $project: {
last_name: 1,
department_name: 1,
street_address: 1,
_id: 0
}
}
)