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 1pXrwF-0003dt-91 for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 23:02:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pXrwE-0001eD-0b for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 23:02:14 +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 1pXrwD-0001e3-M5 for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 23:02:13 +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 1pXrwA-0006Yd-Vc for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 23:02:12 +0000 Received: from x61w.mirbsd.org (2a0a-a540-18e0-0-21f-3bff-fe0d-cbb1.ipv6dyn.netcologne.de [IPv6:2a0a:a540:18e0: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 9A1381006A9; Thu, 2 Mar 2023 23:02:07 +0000 (UTC) Received: by x61w.mirbsd.org (Postfix, from userid 1000) id C01FC62181; Fri, 3 Mar 2023 00:02:06 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by x61w.mirbsd.org (Postfix) with ESMTP id BA27661382; Fri, 3 Mar 2023 00:02:06 +0100 (CET) Date: Fri, 3 Mar 2023 00:02:06 +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: Message-ID: <9da4cb5a-81bb-23a4-8261-6ba8a29579af@evolvis.org> References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <559416.1677547978@sss.pgh.pa.us> <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> 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 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=E2=80=99t see how. (But then I=E2=80=99ve not yet worked with lateral= JOINs.) But I c=CC=B2a=CC=B2n=CC=B2 just generate the objects first, I t=CC=B2h=CC= =B2i=CC=B2n=CC=B2k=CC=B2, given one of them corresponds to exactly one of the rows of an m:n-linked table and nothing else. Something like=E2=80=A6 WITH 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 ), -- =E2=80=A6 same for opening times SELECT cp.email, =E2=80=A6, =09-- =E2=80=A6 =09jsonb_build_object('possible_work_times', COALESCE( =09 jsonb_agg(DISTINCT cgwaj.obj ORDER BY cgwaj.obj->>'weekday', =09=09cgwaj.obj->>'forenoon', cgwaj.obj->>'afternoon', =09=09cgwaj.obj->>'evening') =09 FILTER (WHERE cgwaj.id IS NOT NULL))) || =09-- =E2=80=A6 FROM core_person cp =09-- =E2=80=A6 =09LEFT JOIN core_person_possible_work_times cppwt ON cppwt.person_id=3Dcp.= id =09LEFT JOIN cgwaj ON cgwaj.id=3Dcppwt.generalworkavailability_id =09-- =E2=80=A6 That is, add a CTE for each m:n-attached table whose =E2=80=9Cvalue=E2=80= =9D 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=E2=80=99ve not yet tried it (I don=E2=80=99t 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 =E2=80=9Cproperty table=E2=80=9D, anyway. (And = even if filtered, that can be passed down.) bye, //mirabilos --=20 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.=09=09-- Andreas Bogk =C3=BCber boehm-gc in d.a.s.r