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

tableA contains demographics by country from one api and tableB contains the same but from a different api, and tableC contains both of these combined and added together where there are countries in common — user1898662
– user1898662, Commented Mar 17, 2021 at 18:56

Gerard H. Pille · Accepted Answer · 2021-03-19 10:39:40Z

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$

Stack Exchange Network

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

1 Answer 1

Your Answer

Linked

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions