How to order the query result based on following condition?

Let’s suppose I have a table as below:

employee | period  | commission
A        | 1Y      | 100
A        | 2Y      | 150
B        | 1Y      | 80
C        | 1Y      | 200
C        | 2Y      | 270
C        | 6M      | 80

this data shows the commission earned by employees based on time period. now, I need to get the data in the following format in node.js code

[
  {
    "employee": "A",
    "commission": [
      {
        "period": "1Y",
        "commission": 100
      },
      {
        "period": "2Y",
        "commission": 150
      }
    ]
  },
  {
    "employee": "B",
    "commission": [
      {
        "period": "1Y",
        "commission": 80
      }
    ]
  },
  {
    "employee": "C",
    "commission": [
      {
        "period": "6M",
        "commission": 80
      },
      {
        "period": "1Y",
        "commission": 200
      },
      {
        "period": "2Y",
        "commission": 270
      }
    ]
  }
]

I have written following query

select 
  employee, 
  json_agg (json_build_object('period', period, 'commission', commission)) as commission 
from 
  table_name 
  group by employee

This query gave me the desired result, but now I also want to sort the employees based on commission earned in a specific period e.g. sort employees based on the commission earned in period=’1Y’. I am unable to find a solution with this query. Please provide any other good solution for these kinds of problems

Answer

Extract the one year commission in your query and use it in the wrapper query to sort the results:

select employee, commission
from (
    select 
        employee, 
        json_agg (json_build_object('period', period, 'commission', commission)) as commission,
        max(case period when '1Y' then commission end) as one_year_commission
    from table_name 
    group by employee
    ) s
order by one_year_commission