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






Reply via email to