Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYB8g-0006qy-EC for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 19:32:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYB8f-0007jb-8Q for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 19:32:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYB8e-0007jS-UR for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 19:32:20 +0000 Received: from evolvis.org ([217.144.135.11]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYB8c-0000T3-3j for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 19:32:19 +0000 Received: from x61w.mirbsd.org (2001-4dd7-3312-0-21f-3bff-fe0d-cbb1.ipv6dyn.netcologne.de [IPv6:2001:4dd7:3312:0:21f:3bff:fe0d:cbb1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) by evolvis.org (Postfix) with ESMTPSA id 271A4100782; Fri, 3 Mar 2023 19:32:15 +0000 (UTC) Received: by x61w.mirbsd.org (Postfix, from userid 1000) id 9EB0D624D8; Fri, 3 Mar 2023 20:32:14 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by x61w.mirbsd.org (Postfix) with ESMTP id 99C9C608E4; Fri, 3 Mar 2023 20:32:14 +0100 (CET) Date: Fri, 3 Mar 2023 20:32:14 +0100 (CET) From: Thorsten Glaser To: Alban Hertroys cc: pgsql-general@lists.postgresql.org Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y In-Reply-To: <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com> Message-ID: <9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org> References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <559416.1677547978@sss.pgh.pa.us> <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org> <5AF0C3F0-4D5A-4198-B58B-6E7D4994969B@gmail.com> Content-Language: de-DE-1901 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: QUOTED-PRINTABLE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 3 Mar 2023, Alban Hertroys wrote: >You can rewrite that into something like this: > >select jsonb_build_object('opening_times=E2=80=99, >=09obj >=09ORDER BY >=09 obj->>'weekday=E2=80=99, >=09 obj->>'from_hour=E2=80=99, >=09 obj->>'to_hour') >) >from cot >cross join lateral jsonb_agg(jsonb_build_object( >=09=09'weekday', cot.weekday, >=09=09'from_hour', cot.from_hour, >=09=09'to_hour', cot.to_hour) obj But isn=E2=80=99t that the same as with a regular LEFT JOIN? >> cgwaj AS ( >> =09SELECT cgwa.id AS id, jsonb_build_object( >> =09=09'weekday', cgwa.weekday, >> =09=09'forenoon', cgwa.forenoon, >> =09=09'afternoon', cgwa.afternoon, >> =09=09'evening', cgwa.evening) AS obj >> =09FROM core_generalworkavailability cgwa plus >> =09LEFT JOIN cgwaj ON cgwaj.id=3Dcppwt.generalworkavailability_id With the addition that I can aggregate=E2=80=A6 bye, //mirabilos --=20 15:41=E2=8E=9C Somebody write a testsuite for hello= world :-)