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

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.)

But I c̲a̲n̲ just generate the objects first, I t̲h̲i̲n̲k̲, given one
of them corresponds to exactly one of the rows of an m:n-linked
table and nothing else. Something like…

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