I liked it in Luigis Mansion, where you're fighting ghosts, and you get to the end of the game and it turns out that Luigi has been dead the whole time!
I also enjoyed Mario Sunshine, where you fight these alien creatures all around (they look all weird), but it turns out their weakness is water, and so you go around spraying water on them!
Also good was Animal Crossing, where you start in a village, but as time goes on, you realise something is up, and it turns out that Tom Nook has kept everyone trapped in the village using scare stories about a monster, and actually the village is set in modern times, but you don't realise!
Nintendo make some great stories really.