From: Alban Hertroys <haramrae@gmail.com>
To: Thorsten Glaser <tg@evolvis.org>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y
Date: Fri, 3 Mar 2023 09:31:03 +0100
Message-ID: <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com> (raw)
In-Reply-To: <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org>
References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org>
<CAKFQuwZ1f0zmVsFpYeNL8c=waGSCJB5K4AmkqzdXXHXB3XX3Lw@mail.gmail.com>
<c02191c1-64a8-3350-6853-784bf5ce57@evolvis.org>
<559416.1677547978@sss.pgh.pa.us>
<9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org>
<D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com>
<9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org>
> 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 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 instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: haramrae@gmail.com, tg@evolvis.org
Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y
In-Reply-To: <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com>
References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <CAKFQuwZ1f0zmVsFpYeNL8c=waGSCJB5K4AmkqzdXXHXB3XX3Lw@mail.gmail.com> <c02191c1-64a8-3350-6853-784bf5ce57@evolvis.org> <559416.1677547978@sss.pgh.pa.us> <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> <D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com> <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org> <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox