User-defined aggregate function to sum jsonb values

Question

I have agg function that sums decimals. Business logic omitted for brevity

CREATE OR REPLACE FUNCTION calculate_snapshot_internal_decimal(_this_value DECIMAL,
                                                               _next_value DECIMAL
                                                               OUT _code DECIMAL)
    LANGUAGE plpgsql AS
$func$
BEGIN
_code = _this_value + _next_value;


END
$func$;

Now I need the same for JSONB where key is string and value is always BigDecimal

_this_value:

{"weight": "50", "height":"170", "size": "2"}

_next_value

{"weight": "40", "height":"20"}

aggregated result:

{"weight": "90", "height":"190", "size": "2"}

What could I try?

Erwin Brandstetter · Accepted Answer · 2023-11-29T05:22:21.013

You can unnest with jsonb_each_text(), UNION ALL, cast, sum up, then aggregate back with jsonb_object_agg():

CREATE OR REPLACE FUNCTION f_jsonb_sum_per_key(_j1 jsonb,_j2 jsonb, OUT _j jsonb)
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
   SELECT jsonb_object_agg(key, val)
   FROM  (
      SELECT key, sum(value::numeric) AS val
      FROM  (
         SELECT * FROM jsonb_each_text(_j1)
         UNION ALL
         SELECT * FROM jsonb_each_text(_j2)
         ) sub1
      GROUP  BY key
      ) sub2;
END;

Call:

SELECT f_jsonb_sum_per_key(jsonb '{"weight": "50", "height":"170", "size": "2"}'
                         , jsonb '{"weight": "40", "height":"20"}');

fiddle

This sums up all values per key in the two input jsonb documents. Values that are not compatible with numeric ( = decimal) raise an exception.

We don't need PL/pgSQL for this. Plain SQL does it. I chose the standard-SQL variant - your pick. See:

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

This is not an "aggregate function", strictly speaking, and neither is the example in the question. Both are plain functions aggregating two input values.

User-defined aggregate function to sum jsonb values

1 Answers1