0

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?

1
  • You skipped a bit too much "for brevity". Only ever aggregate two rows? Which keys to sum up, and how to deal with other keys (if any)? Start by declaring your version of Postgres, please Oct 3, 2023 at 21:55

1 Answer 1

1

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:

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

1
  • Thanks for details, didn't know it. I'm using pg13, so getting [42P13] ERROR: no function body specified. Anyway it works
    – Capacytron
    Oct 4, 2023 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.