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 <[email protected]> wrote:
> On Fri, 3 Mar 2023 at 23:17, Daneel Yaitskov <[email protected]> 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
