If you're looking for games with RPG style explicit stories, I'd recommend Chrono Trigger as the classic RPG choice with a good story. It holds up today and has good mechanics for an RPG of its age.
A couple of people recommended Earthbound and Mother 3. While I think the former's dialogue and overall aesthetic is a lot cooler, the second definitely has a more coherent story that's worth checking out. You don't need to play Earthbound first but you should because its probably my favourite game of all time.
In the same vein and clearly inspired by Earthbound, Lisa The Painful is worth playing. Lots more wacky dialogue and a really nice story. Off by Mortis Ghost is another "Weird RPGmaker" game which is a lot more surreal but could be fun too. Also I'm just going to throw the Hylics games in here. The story of those basically doesn't matter but they fucking own.
The Psychonauts games use an interesting narrative device of learning more about characters by having levels inside their minds. This also leads to really cool level design.
Coyo mentioned Outer Wilds but it's worth reiterating. This is a game about Xenoarcheology where a lot of the game is reading logs left behind by other people to try and piece together a story.
If you're into the environmental storytelling/worldbuilding stuff where you have to go out of your way to look for bits of lore there's a whole genre of souls style games and metroidvanias like Hollow Knight that you can go for.
If you want something extremely esoteric then I am going to draw attention again to Rain World. The game doesn't really have an explicit story but has a narrative that's told through the player's personal experience with the game and its mechanics which mirrors the games themes. This is the sort of thing that makes a lot of people not enjoy the game at all because it's very unfair (which is kind of the point, you aren't playing the main character of the world and so it's not really designed for you) and leads other people to write shit like this. I don't know of any other game that does narrative like this so its worth checking out (also for its extremely deep but alien mechanics and very "real" feeling AI).