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

database-designjsonpostgresqlpostgresql-12query

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

Best Answer

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$
Related Question