I'd second the recommendation of the Oracle games. Yes they are a bit outdated because of the GameBoy controls, but I think they are good for the aspects you're looking for. Those are games in the same vein as ALttP and LA, and I guess the newer ALBW, which you are familiar with.

The Wind Waker might look like it is as well, but like others said that game is quite straight forward in actuality. Also, the dungeons in that game are few, short and fairly simple. Likewise Twilight Princess is straightforward too, though in that game the dungeons are much, much better. Skyward Sword might also be interesting for what you're looking for, if I'd had to mention one of the 3D ones, because it has a puzzle-y overworld. That game however also puts a big emphasis on the combat.

Skyward has the best dungeons in the franchise, in my opinion.

BOTW imo would be right up your alley, yes the 900 seed reward is underwhelming, but I think thats the only one that is, and you dont need to get all seeds either way. Other than that theres plenty to explore, secrets and equipment to find. I think its a very rewarding game exploration-wise.

You should play Tunic as well tho.

BotW is what I'd recommend most.  It has tons of secrets.  That is kind of the point to the whole game.  Just don't try to find all 900 Korok seeds.  There are a lot more secrets in it than that.

Forget Zelda....play 3D Dot Game Heroes :P

I really enjoyed Hob, a small independent game that gives off those old school Zelda vibes.

Cocoon is not zelda but still pretty damn good puzzle game.

I would recommend the original final fantasy because it has excellent exploration.

The Oracle games are good for this, especially Oracle of Seasons imo.
Wind Waker and Twilight Princess both have enjoyable overworlds, but I'd suggest you play the HD versions if possible. They both have QoL upgrades that reduce minor annoyances and tedious parts of the original games. Plus, TP has the best dungeons and bosses in the series.
Minish Cap is quite good, shame you didn't like the aesthetic.
There is also Blossom Tales, it's a fun little Zelda-like
And Ittle Dew, altho that one is definitely a little weird and zany for a Zelda-like so I'd watch some gameplay before buying it.

3D Dot Hero?