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"]