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 20:32:14 +0100 (CET)
Message-ID: <9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org> (raw)
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>

On Fri, 3 Mar 2023, Alban Hertroys wrote:

>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

But isn’t that the same as with a regular LEFT JOIN?

>>    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

plus

>> 	LEFT JOIN cgwaj ON cgwaj.id=cppwt.generalworkavailability_id

With the addition that I can aggregate…

bye,
//mirabilos
-- 
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)





view thread (14+ messages)  latest in thread

Message-ID: <9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org>
Permalink:  ../9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org/
Also on:    postgresql.org/message-id/9e86b053-a29a-a82-78f0-1228a391edc3@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: <9e86b053-a29a-a82-78f0-1228a391edc3@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> <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com> <9e86b053-a29a-a82-78f0-1228a391edc3@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