min/max aggregation for jsonb

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!

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@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.