By 'exploration' I'm referring to things like this specifically:

 Things that turn the gears inside my head when I see them (outside of dungeons). Whether I can solve it right away or later.
"Looks like I need an item to get in there. Will there be something under one of the bushes? Or am I supposed to stand there and switch to the Light world to access an area I couldn't get to before?"

And with worthwhile rewards like heart pieces, bottles, an invisibility cloak, a blacksmith that can strengthen your sword, etc.
I don't want a large portion of them to be collectors quest items, like the 900 seeds I heard of from Breath of the Wild.
Because then I can be pretty sure what I'm going to find before I find it, and it won't give me any immediate value.

These are the Zelda games I've played through completely:

Zelda 1
Zelda 2
A Link to the Past
Link's Awakening
Ocarina of Time
Majora's Mask
A Link Between Worlds

I think this element was quite enjoyable in most of these.

Some years ago I also played Minish Cap, and completed a few dungeons. But I can't remember much about the game. None of the explorative secrets in the overworld.
I don't know if they were not memorable for me. But when I look at the game now, the aestetics makes me not want to play it if there are other options.

The game I want to play and like the most is Twilight Princess, because it seems the closest to OOT.
But after playing up until getting the magnetic boots (second dungeon I believe), I found the game up until that point to have been very unremarkable. I wasn't bored, but I didn't feel any of the 'Zelda magic' in it either. And I wasn't dying to keep playing. Same problem with Breath of the Wild. Although that one I only played up until completing the first dunegon.

I also don't remember any of the secrets exploration I may have encountered in Twilight Princess, but it's been 10 years since I played it.

The most interesting candidates in my mind are Oracle of Seasons/Ages.
I've heard good things. My only hesitation is that it only features the use of two buttons, like Link's Awakening since it's based on GameBoy. I did love Link's Awakening a lot, but that was an obvious technical limitation that shouldn't have been there. (The Remake lets you use 4 buttons.)

If there is a mod that lets you use 4 buttons in Oracle of Seasons/Ages, like for the recent PC port of Link's Awakening, then I'd play that right away.

I also should mention that I have low interest in the combat aspect of Zelda games these days.
So I'd be playing mainly for things like the overworld exploration of interesting secrets, memorable landmarks/locations, and dungeon puzzles.

Maybe Wind Waker? I haven't played that one. But I hear the sailing can be tedious.

Go play the Oracle games, the use of only two buttons and the item management never bothered me as much with them than it actually did on Awakening.

Sure I'm actually expecting a remake of both of them in the future. If you're willing to wait that far. I'd recommend to you to play Spirit Tracks or Phantom Hourglass

The Oracle games are definitely the closest to what you’re looking for. They’re both on NSO too.

Although I will always recommend Wind Waker. My favorite Zelda game but it is very straightforward. Not much major puzzle solving that really gets you thinking. And yes, the sailing is tedious lol

There‘s also Phantom Hourglass and Spirit Tracks from the DS but those were…..fine

I think what you're looking for is called Tunic.

The Oracle games for sure.

In terms of exploration....its not like the ones you played or Oracle games but closest ones I could think of in regards to the more modern Zelda games is...Skyward Sword or BotW

Oracle has been mentioned. There are also alts to Zelda like Crusader of Centry. 3D Dot Game Heroes. Neutopia games are good but are 2 buttons. If we go 3D then Okami.

Agreed, if he hasn't played it.  Tunic sounds spot on.

Cat’s Quest I and Ii
Okami HD

Tunic is also the first thing that came to my mind. It is somewhat of a Zelda-Souls crossover (so not pure Zelda alike), and it has its faults, but I quite enjoyed it.

It's a shame you don't like the aesthetics of Minish Cap. Based on what you are looking for, it might have been one of the best candidates.