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, 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