Обсуждение: min/max aggregation for jsonb
Hi,
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.
Is there a concern about implementing support for jsonb in min/max?
jsonb is a byte array.
json faces same limitations.
--
Best regards,
Daniil Iaitskov
On Fri, 3 Mar 2023 at 23:17, Daneel Yaitskov <dyaitskov@gmail.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
Nonetheless PostgreSQL min/max functions don't work with JSON - array_agg distinct does!
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,
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@gmail.com> wrote:
On Fri, 3 Mar 2023 at 23:17, Daneel Yaitskov <dyaitskov@gmail.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
On 03.03.23 11:41, David Rowley wrote: > On Fri, 3 Mar 2023 at 23:17, Daneel Yaitskov <dyaitskov@gmail.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. We already have ordering operators and operator classes for jsonb, so sticking min/max aggregates around that should be pretty straightforward.