On Mon, Feb 27, 2023 at 4:11 PM mirabilos <tg@evolvis.org> wrote:
jsonb_build_object('opening_times', jsonb_agg(DISTINCT jsonb_build_object( 'weekday', cot.weekday, 'from_hour', cot.from_hour, 'to_hour', cot.to_hour) ORDER BY cot.weekday, cot.from_hour, cot.to_hour) )
This is because, when I use DISTINCT (but only then‽), the ORDER BY arguments must be… arguments to the function, or something.
So long as the function call itself is at least stable...:
DISTINCT func_call(...) ORDER BY func_call(...)
Order By is evaluated AFTER and over the contents of the distinct-ified expression
All other solutions I can find involve subqueries in the first place; I am somewhat proud I even managed to write this with JOINs and without any subqueries in the first place so I’m hesitant to go that route.
That pride seems misplaced. Related to Tom's comment, the presence of the DISTINCTs is telling you that what you did is not good. DISTINCT is almost always a code smell, and given the prevalence of direct table joins in your query, it is indeed a valid signal.
Lastly, if you do need to care about normalizing the output of JSON you should consider writing a function that takes arbitrary json input and reformats it, rather than trying to build up json from scratch where every individual component needs to be aware and take action. i.e., get rid of the ORDER BY also. Maybe this belongs in an application layer with tooling that already provides this capability.
