How do you do an Object_Insert with a join on Snowflake?

the parts are somewhat separate:

There is the UPDATE table FROM / WHERE which is where you do the update, and the new data comes from the FROM/WHERE

So from the 2 tables UPDATE example you have:

update t1
  set 
    number_column = t1.number_column + t2.number_column, 
    t1.text_column = 'ASDF'
  from t2
  where t1.key_column = t2.t1_key and t1.number_column < 10;

so you weave in the object_insert might become:

update t1
  set 
    number_column = t1.number_column + t2.number_column, 
    t1.text_column = 'ASDF',
    t1.awesome_json = OBJECT_INSERT( t1.awesome_json, 'add_new_field', t2.number_column, false),
    t1.other_json = OBJECT_INSERT( t1.other_json, 'update_field', t2.number_column, true)
  from t2
  where t1.key_column = t2.t1_key and t1.number_column < 10;