pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thorsten Glaser <tg@evolvis.org>
To: Alban Hertroys <haramrae@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y
Date: Fri, 3 Mar 2023 00:02:06 +0100 (CET)
Message-ID: <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org> (raw)
In-Reply-To: <D9BE942C-58B4-48D0-9DA1-2B7514BA4881@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>

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






view thread (14+ messages)  latest in thread

Message-ID: <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org>
Permalink:  ../9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org/
Also on:    postgresql.org/message-id/9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org

 · 

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: tg@evolvis.org, haramrae@gmail.com
  Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y
  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>

* 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