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 1pWq91-00017e-1s for pgsql-general@arkaria.postgresql.org; Tue, 28 Feb 2023 02:55:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWq8z-0007ro-KP for pgsql-general@arkaria.postgresql.org; Tue, 28 Feb 2023 02:55:09 +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 1pWq8z-0007re-3u for pgsql-general@lists.postgresql.org; Tue, 28 Feb 2023 02:55:09 +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 1pWq8r-0003aQ-M4 for pgsql-general@lists.postgresql.org; Tue, 28 Feb 2023 02:55:07 +0000 Received: from x61w.mirbsd.org (2001-4dd7-4829-0-21f-3bff-fe0d-cbb1.ipv6dyn.netcologne.de [IPv6:2001:4dd7:4829: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 63236100051; Tue, 28 Feb 2023 02:54:59 +0000 (UTC) Received: by x61w.mirbsd.org (Postfix, from userid 1000) id 8BA1160B8E; Tue, 28 Feb 2023 03:54:58 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by x61w.mirbsd.org (Postfix) with ESMTP id 864B460981; Tue, 28 Feb 2023 03:54:58 +0100 (CET) Date: Tue, 28 Feb 2023 03:54:58 +0100 (CET) From: Thorsten Glaser To: Tom Lane cc: pgsql-general@lists.postgresql.org Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y In-Reply-To: <559416.1677547978@sss.pgh.pa.us> Message-ID: <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <559416.1677547978@sss.pgh.pa.us> 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 Mon, 27 Feb 2023, David G. Johnston wrote: >Consider this then as a jumping point to a more precise query form: [=E2=80=A6] >the basic concept holds - produce single rows in subqueries then join thos= e >various single rows together to produce your desired json output. Ouch. I=E2=80=99ll have to read up and experiment with that, I guess. But wouldn=E2=80=99t I rather then do a CTA for each referenced table that does the aggregating and GROUP BY person_id, then join that (which has single rows for each user row) to users? >--BAD cross joining going on with multiple one-to-many relationships >--GOOD, only joining one-to-one relationships Not quite. This is many-to-many created by Django=E2=80=A6 On Mon, 27 Feb 2023, Tom Lane wrote: >Sure, I was just trying to explain the rule. Aaah, okay. Sorry, I misunderstood that, and the=E2=80=A6 general direction of the replies, then. >Well, that may be what you want, but it's not what you wrote in >the query. Follow David's advice and do [=E2=80=A6] >I'm pretty sure that this will only incur one evaluation of the >common subexpression, so even though it's tedious to type it's not >inefficient. Thanks. But I fear it=E2=80=99s not as simple as you wrote. More like: jsonb_build_object('opening_times', =09jsonb_agg(DISTINCT jsonb_build_object( =09=09'weekday', cot.weekday, =09=09'from_hour', cot.from_hour, =09=09'to_hour', cot.to_hour) =09ORDER BY =09 jsonb_build_object( =09=09'weekday', cot.weekday, =09=09'from_hour', cot.from_hour, =09=09'to_hour', cot.to_hour)->>'weekday', =09 jsonb_build_object( =09=09'weekday', cot.weekday, =09=09'from_hour', cot.from_hour, =09=09'to_hour', cot.to_hour)->>'from_hour', =09 jsonb_build_object( =09=09'weekday', cot.weekday, =09=09'from_hour', cot.from_hour, =09=09'to_hour', cot.to_hour)->>'to_hour') ) Isn=E2=80=99t that more like it? (Though I guess at that point I could just drop at least the to_hour fallback sort, hoping nobody inserts overlapping hours with indentical start times=E2=80=A6) Now that I see that, it sort of re-raises a question I had during developing this but didn=E2=80=99t pursue. How about, instead of creating a JSONB object here, I create a (not-JSON) ARRAY['weekday', cot.weekday, 'from_hour', cot.from_hour, 'to_hour', cot.to_hour] in the aggregate function. The array is something I could naturally use to sort (its elements already are in sort key order), and its elements *also* are in the order jsonb_build_object expects its arguments (assuming I can do something like passing the array to it instead of =E2=80=9Cspreading=E2= =80=9D the arguments). Pseudocode: =E2=80=A6 hmm, not that easy. The aggregate would return e.g. this=E2=80=A6 ARRAY[['weekday',1,=E2=80=A6],['weekday',2,=E2=80=A6]] =E2=80=A6 and there=E2=80=99s no array_map function that could be used to p= ass each inner array, one by one, to jsonb_build_object; converting the outer array to JSON gets us json_array_elements (same problem), other ways I can think of also don=E2=80=99t seem to have matching function= s (jq has=E2=80=A6 interesting ones). As I write this, I fear that won=E2=80=99t fly because n=C5=8Dn-JSON arrays cannot contain mixed types (text and numbers) in PostgreSQL=E2=80=A6 at least I ran into that at some point in the past vaguely remembering=E2=80= =A6 Oh well, //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