A lot of modern open-world games incorporate the meandering/gratification loop you're talking about though.
I can't find it now, but there was a heartwarming story on reddit a few months ago about a dad dying of cancer and playing BOTW on a switch in his hospital bed, and bonding with his adult son over it— he never even made it off the Plateau (the initial tutorial section of the game), but was having a ball running around collecting plants, making potions, getting gear, whatever.
Even if the violence level of Assassins Creed or God of War isn't your cup of tea, something like Detroit Become Human or Horizon Zero Dawn could be a good fit.