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 1pWmey-0005IO-PC for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 23:11:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWmex-00065j-9v for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 23:11:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWmew-00065X-UW for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 23:11:54 +0000 Received: from evolvis.org ([217.144.135.11]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWmeu-0006Ee-GE for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 23:11:54 +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 01298100339 for ; Mon, 27 Feb 2023 23:11:51 +0000 (UTC) Received: by x61w.mirbsd.org (Postfix, from userid 1000) id 014FD60B8E; Tue, 28 Feb 2023 00:11:49 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by x61w.mirbsd.org (Postfix) with ESMTP id EFD9860981 for ; Tue, 28 Feb 2023 00:11:49 +0100 (CET) Date: Tue, 28 Feb 2023 00:11:49 +0100 (CET) From: mirabilos To: pgsql-general@lists.postgresql.org Subject: DISTINCT *and* ORDER BY in aggregate functions on expressions(!) Message-ID: <23d08470-aaa8-afca-dfd7-37261487a1f8@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 Hi, I=E2=80=99ve got a=E2=80=A6 rather large query (see below), in which I join= a complex data structure (whose exact contents do not, at this point, matter) together to get some auxiliary data to expose as JSON field. In this query I can use, for example=E2=80=A6 jsonb_build_object('user_permissions', jsonb_agg(DISTINCT ap.name ORDER BY ap.name)) =E2=80=A6 to get a distinct, sorted, list of =E2=80=9Cuser permissions=E2= =80=9D from a table ap which I joined to the user table which is the main subject of the query. (For some reason I need to add DISTINCT because else duplica=E2=80= =90 tes are shown.) Wrapping this as=E2=80=A6 jsonb_build_object('user_permissions', COALESCE( =09jsonb_agg(DISTINCT ap.name ORDER BY ap.name) FILTER (WHERE ap.id IS NOT NULL))) =E2=80=A6 gets me the JSON object=E2=80=99s value for the user_permissions = set to null if there=E2=80=99s nothing in the m:n intermediate table for the user in question. This works well. However, what I seem to be not allowed to do is (without the extra COALESCE, to simplify): jsonb_build_object('opening_times', jsonb_agg(DISTINCT jsonb_build_object( =09'weekday', cot.weekday, =09'from_hour', cot.from_hour, =09'to_hour', cot.to_hour) ORDER BY cot.weekday, cot.from_hour, cot.to_hour) ) This is because, when I use DISTINCT (but only then=E2=80=BD), the ORDER BY arguments must be=E2=80=A6 arguments to the function, or something. In the above case, the sort key is ap.name which is also the argument to the jsonb_agg function, so no problem there, but here, the jsonb_agg argument is the return value of a function so=E2=80=A6 it has no name. What I=E2=80=99m looking for is something like=E2=80=A6 jsonb_build_object('opening_times', jsonb_agg(DISTINCT jsonb_build_object( =09'weekday', cot.weekday, =09'from_hour', cot.from_hour, =09'to_hour', cot.to_hour) AS jbo ORDER BY jbo->>'weekday', jbo->>'from_hour', jbo->>'to_hour') ) =E2=80=A6 except I cannot define aliases in that place. Any other syntax would also work. The suggested solution for this is apparently to do=E2=80=A6 CREATE OR REPLACE FUNCTION core_openingtime_jsonb_sort(JSONB) RETURNS JSONB AS $$ SELECT jsonb_agg(e ORDER BY e->>'weekday', e->>'from_hour', e->>'to_hour') FROM jsonb_array_elements($1) AS e $$ LANGUAGE SQL IMMUTABLE; =E2=80=A6 and then query as=E2=80=A6 jsonb_build_object('opening_times', core_openingtime_jsonb_sort(jsonb_agg(DISTINCT jsonb_build_object( =09'weekday', cot.weekday, =09'from_hour', cot.from_hour, =09'to_hour', cot.to_hour))) ) =E2=80=A6 which involves internally subquerying for each output row (i.e. row of the user table) times amount of sub-JSONArrays that need to be sorted like this, which is currently 3. All other solutions I can find involve subqueries in the first place; I am somewhat proud I even managed to write this with JOINs and without any subqueries in the first place so I=E2=80=99m hesitant to go that route. Any advice here? And, somewhat related: when outputting JSONB, the order of JSONObject elements is indeterminate, which is=E2=80=A6 okay, but forcing sorted (ASCIIbetically, i.e. by codepoint order) keys would be very very welcome, for reproducibility of the output. (I=E2=80=99m sure the reproducible-builds project would also love if this could be changed, or at least added, in a way it can be enabled for queries, as session parameter perhaps?) The query in its current incarnanation is as follows: -- -----BEGIN SQL----- CREATE OR REPLACE FUNCTION core_openingtime_jsonb_sort(JSONB) RETURNS JSONB AS $$ SELECT jsonb_agg(e ORDER BY e->>'weekday', e->>'from_hour', e->>'to_hour') FROM jsonb_array_elements($1) AS e $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION core_generalworkavailability_jsonb_sort(JSONB) RETURNS JSONB AS $$ SELECT jsonb_agg(e ORDER BY e->>'weekday', e->>'forenoon', e->>'afternoon',= e->>'evening') FROM jsonb_array_elements($1) AS e $$ LANGUAGE SQL IMMUTABLE; COPY (SELECT cp.email, cp.first_name, cp.last_name, cp.street, cp.number, =09jsonb_build_object('groups', COALESCE(jsonb_agg(DISTINCT ag.name ORDER B= Y ag.name) =09 FILTER (WHERE ag.id IS NOT NULL))) || =09jsonb_build_object('help_operations', COALESCE(jsonb_agg(DISTINCT cho.na= me ORDER BY cho.name) =09 FILTER (WHERE cho.id IS NOT NULL))) || =09jsonb_build_object('emergency_opening_times', COALESCE( =09 core_openingtime_jsonb_sort(jsonb_agg(DISTINCT jsonb_build_object( =09=09'weekday', ceot.weekday, =09=09'from_hour', ceot.from_hour, =09=09'to_hour', ceot.to_hour)) =09 FILTER (WHERE ceot.id IS NOT NULL)))) || =09jsonb_build_object('opening_times', COALESCE( =09 core_openingtime_jsonb_sort(jsonb_agg(DISTINCT jsonb_build_object( =09=09'weekday', cot.weekday, =09=09'from_hour', cot.from_hour, =09=09'to_hour', cot.to_hour)) =09 FILTER (WHERE cot.id IS NOT NULL)))) || =09jsonb_build_object('possible_work_times', COALESCE( =09 core_generalworkavailability_jsonb_sort(jsonb_agg(DISTINCT jsonb_bui= ld_object( =09=09'weekday', cgwa.weekday, =09=09'forenoon', cgwa.forenoon, =09=09'afternoon', cgwa.afternoon, =09=09'evening', cgwa.evening)) =09 FILTER (WHERE cgwa.id IS NOT NULL)))) || =09jsonb_build_object('qualifications', =09 jsonb_build_object('administrative', COALESCE(jsonb_agg(DISTINCT cqa.na= me ORDER BY cqa.name) =09 FILTER (WHERE cqa.id IS NOT NULL))) || =09 jsonb_build_object('health', COALESCE(jsonb_agg(DISTINCT cqh.name ORDER= BY cqh.name) =09 FILTER (WHERE cqh.id IS NOT NULL))) || =09 jsonb_build_object('language', COALESCE(jsonb_agg(DISTINCT cqlang.name = ORDER BY cqlang.name) =09 FILTER (WHERE cqlang.id IS NOT NULL))) || =09 jsonb_build_object('license', COALESCE(jsonb_agg(DISTINCT cqlic.name OR= DER BY cqlic.name) =09 FILTER (WHERE cqlic.id IS NOT NULL))) || =09 jsonb_build_object('technical', COALESCE(jsonb_agg(DISTINCT cqt.name OR= DER BY cqt.name) =09 FILTER (WHERE cqt.id IS NOT NULL)))) || =09jsonb_build_object('restrictions', COALESCE(jsonb_agg(DISTINCT cr.name O= RDER BY cr.name) =09 FILTER (WHERE cr.id IS NOT NULL))) || =09jsonb_build_object('user_permissions', COALESCE(jsonb_agg(DISTINCT ap.na= me ORDER BY ap.name) =09 FILTER (WHERE ap.id IS NOT NULL))) AS "other_data" FROM core_person cp =09LEFT JOIN core_person_emergency_opening_times cpeot ON cpeot.person_id= =3Dcp.id =09LEFT JOIN core_openingtime ceot ON ceot.id=3Dcpeot.openingtime_id =09LEFT JOIN core_person_groups cpg ON cpg.person_id=3Dcp.id =09LEFT JOIN auth_group ag ON ag.id=3Dcpg.group_id =09LEFT JOIN core_person_help_operations cpho ON cpho.person_id=3Dcp.id =09LEFT JOIN core_helpoperation cho ON cho.id=3Dcpho.helpoperation_id =09LEFT JOIN core_person_opening_times cpot ON cpot.person_id=3Dcp.id =09LEFT JOIN core_openingtime cot ON cot.id=3Dcpot.openingtime_id =09LEFT JOIN core_person_possible_work_times cppwt ON cppwt.person_id=3Dcp.= id =09LEFT JOIN core_generalworkavailability cgwa ON cgwa.id=3Dcppwt.generalwo= rkavailability_id =09LEFT JOIN core_person_qualifications_administrative cpqa ON cpqa.person_= id=3Dcp.id =09LEFT JOIN core_qualificationadministrative cqa ON cqa.id=3Dcpqa.qualific= ationadministrative_id =09LEFT JOIN core_person_qualifications_health cpqh ON cpqh.person_id=3Dcp.= id =09LEFT JOIN core_qualificationhealth cqh ON cqh.id=3Dcpqh.qualificationhea= lth_id =09LEFT JOIN core_person_qualifications_language cpqlang ON cpqlang.person_= id=3Dcp.id =09LEFT JOIN core_qualificationlanguage cqlang ON cqlang.id=3Dcpqlang.quali= ficationlanguage_id =09LEFT JOIN core_person_qualifications_license cpqlic ON cpqlic.person_id= =3Dcp.id =09LEFT JOIN core_qualificationlicense cqlic ON cqlic.id=3Dcpqlic.qualifica= tionlicense_id =09LEFT JOIN core_person_qualifications_technical cpqt ON cpqt.person_id=3D= cp.id =09LEFT JOIN core_qualificationtechnical cqt ON cqt.id=3Dcpqt.qualification= technical_id =09LEFT JOIN core_person_restrictions cpr ON cpr.person_id=3Dcp.id =09LEFT JOIN core_restriction cr ON cr.id=3Dcpr.restriction_id =09LEFT JOIN core_person_user_permissions cpup ON cpup.person_id=3Dcp.id =09LEFT JOIN auth_permission ap ON ap.id=3Dcpup.permission_id GROUP BY cp.email, cp.first_name, cp.last_name, cp.street, cp.number ORDER BY cp.email ) TO STDOUT WITH (FORMAT csv, HEADER, FORCE_QUOTE *, ENCODING 'UTF-8'); DROP FUNCTION core_openingtime_jsonb_sort(JSONB); DROP FUNCTION core_generalworkavailability_jsonb_sort(JSONB); -- -----END SQL----- Thanks in advance, //mirabilos --=20 =E2=80=9ECool, /usr/share/doc/mksh/examples/uhr.gz ist ja ein Grund, mksh auf jedem System zu installieren.=E2=80=9C =09-- XTaran auf der OpenRheinRuhr, ganz begeistert (EN: =E2=80=9C[=E2=80=A6]uhr.gz is a reason to install mksh on every system= =2E=E2=80=9D)