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

> On 3 Mar 2023, at 0:02, Thorsten Glaser <tg(at)evolvis(dot)org> wrote:
>
> On Tue, 28 Feb 2023, Alban Hertroys wrote:
>
>> Perhaps you can use a lateral cross join to get the result of
>> jsonb_build_object as a jsonb value to pass around?
>
> I don’t see how. (But then I’ve not yet worked with lateral JOINs.)

You posted this bit:

> 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
> jsonb_build_object(
> 'weekday', cot.weekday,
> 'from_hour', cot.from_hour,
> 'to_hour', cot.to_hour)->>'weekday',
> jsonb_build_object(
> 'weekday', cot.weekday,
> 'from_hour', cot.from_hour,
> 'to_hour', cot.to_hour)->>'from_hour',
> jsonb_build_object(
> 'weekday', cot.weekday,
> 'from_hour', cot.from_hour,
> 'to_hour', cot.to_hour)->>'to_hour')
> )

You can rewrite that into something like this:

select jsonb_build_object('opening_times’,
obj
ORDER BY
obj->>'weekday’,
obj->>'from_hour’,
obj->>'to_hour')
)
from cot
cross join lateral jsonb_agg(jsonb_build_object(
'weekday', cot.weekday,
'from_hour', cot.from_hour,
'to_hour', cot.to_hour) obj

That’s off the top of my head and I did leave out the DISTINCT. Just to show the concept here. A bit of experimenting and reading should get you there, I’m keeping $work waiting :P

(…)

> WITH
> cgwaj AS (
> SELECT cgwa.id AS id, jsonb_build_object(
> 'weekday', cgwa.weekday,
> 'forenoon', cgwa.forenoon,
> 'afternoon', cgwa.afternoon,
> 'evening', cgwa.evening) AS obj
> FROM core_generalworkavailability cgwa
> ),
> -- … same for opening times
> SELECT cp.email, …,
> -- …
> jsonb_build_object('possible_work_times', COALESCE(
> jsonb_agg(DISTINCT cgwaj.obj ORDER BY cgwaj.obj->>'weekday',
> cgwaj.obj->>'forenoon', cgwaj.obj->>'afternoon',
> cgwaj.obj->>'evening')
> FILTER (WHERE cgwaj.id IS NOT NULL))) ||
> -- …
> FROM core_person cp
> -- …
> LEFT JOIN core_person_possible_work_times cppwt ON cppwt.person_id=cp.id
> LEFT JOIN cgwaj ON cgwaj.id=cppwt.generalworkavailability_id
> -- …
>
> That is, add a CTE for each m:n-attached table whose “value” is
> an object, not a single field, keep the id field; LEFT JOIN that
> (instead of the original table), then we have a field to use in
> ORDER BY.
>
> I think. I’ve not yet tried it (I don’t have access to that DB
> normally, I was just helping out).
>
> This avoids sub-SELECTs in the sense of needing to run one for
> each user row, because the innermost JSON object building needs
> to be done for each (connected (if the query is not filtering on
> specific users)) row of the “property table”, anyway. (And even
> if filtered, that can be passed down.)
>
> bye,
> //mirabilos
> --
> Solange man keine schmutzigen Tricks macht, und ich meine *wirklich*
> schmutzige Tricks, wie bei einer doppelt verketteten Liste beide
> Pointer XORen und in nur einem Word speichern, funktioniert Boehm ganz
> hervorragend. -- Andreas Bogk über boehm-gc in d.a.s.r
>

Alban Hertroys
--
There is always an exception to always.