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 1pWv4j-0008Sd-5e for pgsql-general@arkaria.postgresql.org; Tue, 28 Feb 2023 08:11:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWv4h-0006P2-Rx for pgsql-general@arkaria.postgresql.org; Tue, 28 Feb 2023 08:11:03 +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 1pWv4h-0006Ot-Ei for pgsql-general@lists.postgresql.org; Tue, 28 Feb 2023 08:11:03 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pWv4b-0002sM-Kd for pgsql-general@lists.postgresql.org; Tue, 28 Feb 2023 08:11:03 +0000 Received: by mail-ed1-x52c.google.com with SMTP id o15so33824246edr.13 for ; Tue, 28 Feb 2023 00:10:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; 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=TCi4AtH17LHWqod8pwOg8VDc2m2oHmNY7/6DXDn2yYs=; b=U6p8dk06r4N7LkFDc0JL89Pt6f4OPdPlLWB7IDSJkUN0rnRDhLOsFecXWkBSV7ByHe t6gjoYLNKfz3qH6T9pq5BwVjvat+5sahdG35cT4Q2LubXdqxWFF5tz5l4v66Byls6xSo znQ8RGixYRf+djvhIkIzLFBVjMf7RDL17e4Q+9aEVWkzbgBuMowige0iVnEVJakyD2eM t4k5nWylDAMEac02GYA2D/i9Ttw5dysxlUCrLRFD+pMQgCjLD7UA33g/Krjr263D9FPL D2ln7HA6x2ACY1DjPgUmaJMkDVWhvcUfsslt04YejlW1B+I47HJ0p8P69iqlPdSOkQ95 4U/g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; 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=TCi4AtH17LHWqod8pwOg8VDc2m2oHmNY7/6DXDn2yYs=; b=sDN0zXyCjkDajjwutWGHECJr6f8kokLy/mUpdxIhcOrhApwb/0BGwxrAyE7njN6oyH UBZnb+tPlVXXW1uzEVUkhj/rXfP2tcy8XIpcLThMzPALRc+5D03ZH/ykmtb5HldOsZlK OMZRDdRWJLFp6+qzpQpFwrarcIaQF8ylOYxQLnqDmEunypqgGvePUghzW4ZJoHgyautq zf6wL6AQk0Ql6ghBUkM0trp26+8POnDkE5k6VLRb3Fnd3vOYiU42sn4AzVynlPjOiA8U lYfS/mHq1+XOYVRmLZjdKX6yl/sstg6s2C7rIzpi9BqChVS+m43tHzlWMHGy2e7Q020D TG9w== X-Gm-Message-State: AO0yUKXpk3ogCPnx5Rwejd9u7IaCg8EpHYrvlBUy20Gjaga53QA32vo/ 6oMrp1LHNq7Kf0LLrdAolcaygnCJ2Ns= X-Google-Smtp-Source: AK7set84v4ssDLFbQZ9TVIPA3G6vTcRJTOZxe4VwWyNPirxZsT2Uz11KiVF/4D9fOZTt4OjmUveI2A== X-Received: by 2002:a17:906:a3c2:b0:8b2:37b5:cc9 with SMTP id ca2-20020a170906a3c200b008b237b50cc9mr1659388ejb.17.1677571856568; Tue, 28 Feb 2023 00:10:56 -0800 (PST) Received: from hollewijn.internal (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id qw26-20020a170906fcba00b008edf49f10a7sm4210337ejb.100.2023.02.28.00.10.55 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 28 Feb 2023 00:10:55 -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: <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> Date: Tue, 28 Feb 2023 09:10:54 +0100 Cc: Tom Lane , pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <559416.1677547978@sss.pgh.pa.us> <9fc8e12b-f943-c633-a37-254915f819a0@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 28 Feb 2023, at 3:54, Thorsten Glaser wrote: (=E2=80=A6) >> 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. >=20 > Thanks. But I fear it=E2=80=99s not as simple as you wrote. More like: >=20 > jsonb_build_object('opening_times', > jsonb_agg(DISTINCT jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_hour) > ORDER BY > jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_hour)->>'weekday', > jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_hour)->>'from_hour', > jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_hour)->>'to_hour') > ) >=20 > Isn=E2=80=99t that more like it? Perhaps you can use a lateral cross join to get the result of = jsonb_build_object as a jsonb value to pass around? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.