Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

On Mon, Feb 27, 2023 at 4:11 PM mirabilos <tg(at)evolvis(dot)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.

David J.