irinatomic / sql2mongo_parser Goto Github PK

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%'

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 
     } 
    }
)