postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Question

Lets say that tableA has column columnA with jsonb data in it like the following:

{
   "A": 5,
   "B": 15,
   "C": 20
}

and tableB has columnA with jsonb data in it like the following:

{
   "B": 10,
   "C": 55,
   "D": 60
}

Is it possible to set it up so that whenever either of the jsonb columns from tableA or tableB change then both jsonB columns will be taken, merged and added to a third table, tableC?

so in this example, if the column in tableB was modified, then what gets stored in tableC will be:

{
   "A": 5,
   "B": 25,
   "C": 75,
   "D": 60
}

The keys and values are retrieved from separate APIs and are unknown ahead of time. I'd like to keep them separate but also have a table that summarizes all the overall values.

Currently using postgresql 12.5 but can move to 13.1 if necessary

thanks in advance

Answer 1

Check out this PostgreSQL function

CREATE OR REPLACE FUNCTION public.test1(in_r1 json, in_r2 json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum_R jsonb;
 R record;
 x text[];

BEGIN
 sum_R = '{}'::jsonb;
 /* FOR R IN SELECT * FROM json_each_text(in_R1) */
 FOR R IN SELECT * FROM json_each(in_R1)
 LOOP
   raise notice 'key %', R.key;
   raise notice 'value %', R.value;
   x := array_append(array[]::text[],R.key);
   raise notice 'x %', x;
   raise notice 'jsonb value  %', to_jsonb(R.value);
   sum_R := jsonb_insert(sum_R, x, to_jsonb(R.value));
   raise notice 'sum_R %', sum_R;
 END LOOP;
 FOR R IN SELECT * FROM json_each_text(in_R2)
 LOOP
   raise notice 'key %', R.key;
   x := array_append(array[]::text[],R.key);
   raise notice 'x %', x;
   raise notice 'jsonb value  %', to_jsonb(R.value);
   raise notice 'jsonb old value  %', sum_R->>R.key;
   if ((sum_R->>R.key = '') is not false) then
     sum_R := jsonb_insert(sum_R, x, to_jsonb(R.value));
   else
     raise notice 'sum %', (sum_R->>R.key)::int + R.value::int;
     sum_R := jsonb_set(sum_R, x,
                        to_jsonb((sum_R->>R.key)::int + R.value::int));
   end if;
   raise notice 'sum_R %', sum_R;
 END LOOP;
 RETURN to_json(sum_R);
END;
$function$

Call it like

select test1('{"A": 5, "B": 15, "C": 10 }','{"A": 1, "C": 5, "D": 1}');

to get

                test1                 
--------------------------------------
 {"A": 6, "B": 15, "C": 15, "D": "1"}
(1 row)

apart from a lot of comments.

Using HSTORE for intermediate results:

CREATE OR REPLACE FUNCTION public.test2(in_r1 json, in_r2 json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum_R hstore;
 R record;
 x integer;

BEGIN
 sum_R = ''::hstore;
 /* FOR R IN SELECT * FROM json_each_text(in_R1) */
 FOR R IN SELECT * FROM json_each(in_R1)
 LOOP
   sum_R := sum_R || hstore(R.key::text, R.value::text);
   raise notice 'sum_R %', sum_R;
 END LOOP;
 FOR R IN SELECT * FROM json_each_text(in_R2)
 LOOP
   x := sum_R->R.key;
   if (((x::text) = '') is not false) then
     sum_R := sum_R || hstore( R.key, R.value::text);
   else
     sum_R := sum_R || hstore( R.key, (x + (R.value::int))::text);
   end if;
   raise notice 'sum_R %', sum_R;
 END LOOP;
 RETURN hstore_to_json(sum_R);
END;
$function$