Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y - Mailing list pgsql-general

> On 3 Mar 2023, at 0:02, Thorsten Glaser <tg@evolvis.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
andreading 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.