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 1pYPRM-0002BY-Jr for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 10:48:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYPRL-0006HG-1l for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 10:48:35 +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 1pYPRK-0006H5-LD for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 10:48:34 +0000 Received: from mail-ed1-x532.google.com ([2a00:1450:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYPRE-0006Ss-7u for pgsql-general@lists.postgresql.org; Sat, 04 Mar 2023 10:48:34 +0000 Received: by mail-ed1-x532.google.com with SMTP id da10so19978702edb.3 for ; Sat, 04 Mar 2023 02:48:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677926907; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=tbcqeQT79maNHIY4z778pytZmbPLLezXivc9v6qT5iI=; b=keJ3+WpvlEE2g17YOVTJurHywTf6AFhYD+xhsG2uCyzm3Pl37Elwy9AD5paxW4EOzd q0Jr0kpeXcokmhzbVUPdDfPoZdilZj0E7Dja19DxE/5kl29ZnDS5+ehssDOH+xI7Sfyi 0QnsfSxM2sHtcgfr+SHJkTG0AMZpRaqsqQTkTfPzG/E0o5PlZyF2jHzwhvbzP3aVGAhP IKYHP9PfhB71c/e0y/WwdKVCdqrksle2KXRNLWFvwt1AphtwZjQOPwquDiTgLltdMirS k1KQuIptGzwjlKdZoGFXMjgbawTnke+ecT+APwTTe/9nVW9xZJ9sJDdr0zx1D9yXm26O 2KLg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677926907; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=tbcqeQT79maNHIY4z778pytZmbPLLezXivc9v6qT5iI=; b=wC37vUU3RnZo7KpGAu11h/xyZ6E6MlZEV1LJWLqvYOgoXFaygLsk3rX5+ZbCrBVvRQ Ze6hFdlII20K+ZkzMmtq6YAU7OitoNl8Mhf6s+zBTmzzCR4QNwQRJduMH4v4/mdCiTDG /wBRxYNLn9Z/wTnAv13gKaNn3PiroxgENhjCa/nzKPjDUPXJkp/i66xuFdJChz3ARmBD WwXz+XQ4ez7I+bIPviUpOcG9c4pOb7djyjUxlE3HySifHfS0klocSNhYZCxLSmSMqDX/ SsbGIunB4BbVKNjdJzpWVFpaxxGgfeHpDbn1LQ6S1O0gJ9XuGL81ZqmxOvSEqcockqYx ESAg== X-Gm-Message-State: AO0yUKVJi9TtMWMB1kByKoI5J93EIL4Hl4XhPSVMD+qL12LdkQGXvPM7 9uIpCymuHpaCUMeSB25YDZU= X-Google-Smtp-Source: AK7set92Zmc8pPRQ6Fv7tOUnCeGL+tUx/jhlMN/6GELKcrCrMCJcBG8xz01K5cMHlK5QtyknC9sKQA== X-Received: by 2002:aa7:c244:0:b0:4c0:1120:a167 with SMTP id y4-20020aa7c244000000b004c01120a167mr4710563edo.32.1677926906870; Sat, 04 Mar 2023 02:48:26 -0800 (PST) Received: from hollewijn.internal (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id d25-20020a50cd59000000b004bc422b58a2sm2256170edj.88.2023.03.04.02.48.25 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 04 Mar 2023 02:48:26 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y From: Alban Hertroys In-Reply-To: <9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org> Date: Sat, 4 Mar 2023 11:48:24 +0100 Cc: pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <82E2D483-67A9-480D-8671-8937D077840B@gmail.com> 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> <9e86b053-a29a-a82-78f0-1228a391edc3@evolvis.org> To: Thorsten Glaser X-Mailer: Apple Mail (2.3608.120.23.2.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 3 Mar 2023, at 20:32, Thorsten Glaser wrote: >=20 > On Fri, 3 Mar 2023, Alban Hertroys wrote: >=20 >> You can rewrite that into something like this: >>=20 >> select jsonb_build_object('opening_times=E2=80=99, >> obj >> ORDER BY >> obj->>'weekday=E2=80=99, >> obj->>'from_hour=E2=80=99, >> 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 >=20 > But isn=E2=80=99t that the same as with a regular LEFT JOIN? Similar, but not the same, I=E2=80=99d say. I do now notice that I made some copying errors there, I was a bit = nauseous at that time. That should have read: >> select jsonb_build_object('opening_times=E2=80=99, >> jsonb_agg(obj >> ORDER BY >> obj->>'weekday=E2=80=99, >> obj->>'from_hour=E2=80=99, >> obj->>'to_hour') >> ) >> from cot >> cross join lateral jsonb_build_object( >> 'weekday', cot.weekday, >> 'from_hour', cot.from_hour, >> 'to_hour', cot.to_hour) obj The lateral join applies the function to each row returned from the left = side of the join and enriches that row with the function result. I used a cross join because there is no join condition to apply to the = lateral, otherwise you could also use an inner join on true. I think you could also have used an implicit Cartesian product (using = =E2=80=98,=E2=80=99 for the join), and that in that case the lateral = would be implied. I prefer explicit notation though. A left join wouldn=E2=80=99t make much sense here, unless the function = could return NULL - for example if it were a function marked as STRICT = and some of the input parameter values (from the table) could be NULL. >>> 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 >=20 > plus There are some differences. You need a sub-select, which in turn creates its own result set. It=E2=80=99= s up to the planner whether the left or the right side gets executed = first, after which the results of the other side of the join get merged = to this, or whether this can all be collected in one go. That=E2=80=99s = up to the query planner to decide though, and it could be right. >>> LEFT JOIN cgwaj ON cgwaj.id=3Dcppwt.generalworkavailability_id >=20 > With the addition that I can aggregate=E2=80=A6 You can do so in both situations, but I guess that confusion stems from = my copy/paste mistake. In my experience, lateral joins go well with the jsonb functions. They = tend to reduce code repetition when referencing object members, such as = in your case. Regards, Alban Hertroys -- There is always an exception to always.