Use ARRAY_AGG to aggregate the salaries of the job positions as input to ARRAY_TO_JSON, to obtain JSON objects as output.
The example uses the table(s) created earlier.
SELECT salary, (ARRAY_TO_JSON(ARRAY_AGG(pos ORDER BY empId, NEW varchararr5()))
RETURNS JSON(100) CHARACTER SET LATIN)
FROM employeeTable
GROUP BY salary
ORDER BY salary;
Result:
salary ARRAY_TO_JSON(ARRAY_AGG(…)) ----------------------------------- 50000 ["engineer","engineer","salesman","salesman","manager"] 75000 ["engineer","salesman","manager"] 100000 ["engineer","salesman","manager"] 125000 ["manager","executive"] 150000 ["executive","executive"] 200000 ["executive"] 1000000 ["executive"]