pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.









view thread (14+ messages)  latest in thread

Message-ID: <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com>
Permalink:  ../5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com/
Also on:    postgresql.org/message-id/5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com

 · 

reply

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