ARRAY_AGGを使用して職種の給与をARRAY_TO_JSONへの入力として集約し、JSONオブジェクトを出力として取得します。
例では、以前に作成したテーブルを使用します。
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;
結果:
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"]