I can't let you do this. 
Don't repeatedly UPDATE the triggering row. That's hugely inefficient and error-prone.
Use a BEFORE trigger instead, where you can simply assign new values to columns of the triggering row before writing it to the table.
Only covering the INSERT case. Extend to UPDATE accordingly.
Simplistic function with loop
CREATE OR REPLACE FUNCTION ins_bef_player()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
DECLARE
equipment_stat text;
BEGIN
FOR equipment_stat IN
SELECT jsonb_object_keys(armor_stats['stats'])
FROM equipments_armor
WHERE armor_id = (NEW.player_equipment_armor->>'armor_id')::int
LOOP
NEW := jsonb_populate_record(NEW, jsonb_build_object(equipment_stat, NEW.player_equipment_armor->'stats'->>equipment_stat));
END LOOP;
RETURN NEW;
END
$func$;
CREATE TRIGGER add_stats_to_player
BEFORE INSERT ON players
FOR EACH ROW
EXECUTE FUNCTION ins_bef_player();
Much better than expensive multiple UPDATEs. But still inefficient. You don't need the loop with multiple assignment at all.
Much smarter https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE
At best, one straight invocation of jsonb_populate_record() is all you need:
CREATE OR REPLACE FUNCTION ins_bef_player()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
NEW := jsonb_populate_record(NEW, NEW.player_equipment_armor['stats']); -- that's all !
RETURN NEW;
END
$func$;
See:
- https://dba.stackexchange.com/questions/105721/use-trigger-to-synchronize-columns-with-fields-in-json-column-on-insert-or-updat/105928#105928
Additional keys in player_equipment_armor['stats'] that have no matching field in NEW are discarded automatically. And additional fields in NEW that have no mo match in player_equipment_armor['stats'] are kept as is.
If armor_stats['stats'] needs to be applied to remove keys, you can do that, too:
CREATE OR REPLACE FUNCTION ins_bef_player()
RETURNS TRIGGER
LANGUAGE plpgsql AS
$func$
BEGIN
NEW := jsonb_populate_record(NEW
, NEW.player_equipment_armor['stats']
- ARRAY(SELECT jsonb_object_keys(NEW.player_equipment_armor['stats'])
EXCEPT ALL
SELECT jsonb_object_keys(armor_stats['stats'])
FROM equipments_armor e
WHERE e.armor_id = (NEW.player_equipment_armor->>'armor_id')::int)
);
RETURN NEW;
END
$func$;
db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=afca2edb0f26167d1f01b2ef967f74fd
This removes all keys that are not present armor_stats['stats'] before calling jsonb_populate_record(). See:
- https://stackoverflow.com/a/19364694/939860
