From: | Daneel Yaitskov <dyaitskov(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: min/max aggregation for jsonb |
Date: | 2023-04-10 14:17:48 |
Message-ID: | CACNT4y4ObAEz-W0weAjVBXZxKTDacC3V8BmxkOTB4rGOfCN0BQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Nonetheless PostgreSQL min/max functions don't work with JSON - array_agg
distinct does!
I was working on an experimental napkin audit feature.
It rewrites a chain of SQL queries to thread through meta data about all
computations contributed to every column.
Every data column gets a meta column with JSON.
Calculating meta column for non aggregated column is trivial, because new
column relation with columns used for computation its is 1:1, but
history of aggregated column is composed of a set values (each value has
potentially different history, but usually it is the same).
So in case of aggregated column I had to collapse somehow a set of JSON
values into a few.
Original aggregating query:
SELECT max(a) AS max_a FROM t
The query with audit meta data embedded:
SELECT
max(a) AS max_a,
jsonb_build_object(
'q', 'SELECT max(a) AS max_a FROM t',
'o', jsonb_build_object(
'a', cast(array_to_json(array_agg( DISTINCT _meta_a)) AS
"jsonb")))
AS _meta_max_a
FROM t
On Fri, Mar 3, 2023 at 5:41 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Fri, 3 Mar 2023 at 23:17, Daneel Yaitskov <dyaitskov(at)gmail(dot)com> wrote:
> > I wanted to use min/max aggregation functions for jsonb type and noticed
> > there is no functions for this type, meanwhile string/array types are
> supported.
>
> It's not really clear to me how you'd want these to sort. If you just
> want to sort by what the output that you see from the type's output
> function then you might get what you need by casting to text.
>
> > Is there a concern about implementing support for jsonb in min/max?
>
> I imagine a lack of any meaningful way of comparing two jsonb values
> to find out which is greater than the other is of some concern.
>
> David
>
--
Best regards,
Daniil Iaitskov
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksander Alekseev | 2023-04-10 14:30:00 | Re: pgsql: psql: add an optional execution-count limit to \watch. |
Previous Message | Zhang Mingli | 2023-04-10 13:56:29 | Re: Fix the miss consideration of tuple_fraction during add_paths_to_append_rel |