My first dedicated gaming system was the PS1, and in general I have no trouble going back to the sprites and chunky polygons of the mid-to-late 90s, whether on consoles or computers
spoiler
as long as I can upscale them to 1080P
Beyond that it gets a bit hit and miss- SNES and Mega Drive games look and sound fine to me and I've played plenty of 16 bit console games as an adult. On PC, I can enjoy 2D stuff like Sam & Max Hit the Road or the original X-Com but most early 3D, like the original System Shock, looks a bit too much like visual vomit.
Going to 8-bit, while the vast majority of NES games are too primitive to my eyes and ears, I have no problems with Game Boy/Game Boy Color games.
spoiler
(Well, at least the good ones, mostly made by Nintendo)
Is it just nostalgia because I had a GBC as a kid or is it because Game Boy games came later and had more developed visual aesthetics? 
My limit is probably the very late 80s
The original Pong is still good in the context it was meant for (a bar or arcade), if you run into one somehow.
Since you mentioned the NES specifically: Super Mario Brothers 3 holds up the best, but they still make Mario games that are just as good, so I think Mega Man 2 would be my biggest recommendation. In either case, I'd consider an infinite lives Game Genie code; standards changed for the better there.
You're right that early 3D looks terrible. Except Doom, which still rules.
I think older games generally become less playable over time, but the high points tend to stay around a long time. The old games people say are still great aren't nostalgia, they're survivorship bias. Using SNES RPGs as examples, everyone says you should play Final Fantasy 6, Chrono Trigger, Earthbound, maybe the other Final Fantasies and Lufia II. But nobody recommends Breath Of Fire anymore, even though at the time it was considered good enough to have a whole series spawn off of it.
Dragon Quest?
A question I'm not qualified to answer.
Yeah, I kind of forgot to take arcade games into account which tend to have very simple gameplay that stands the test of time better. I've just never really played any to any appreciable degree.
The funny thing about the NES Super Mario games is that I did actually play the original Super Mario Bros as a kid as Super Mario Brothers Deluxe, the GBC port that had a horrible field of view due to the lower resolution of the Game Boy Color. I agree that the NES Marios are pretty fun, but I vastly prefer the All-Stars SNES remakes in terms of graphics.
I guess I'd categorise Doom as "late early 3D." Don't really have a problem with it or games with similar aesthetics like Descent or Hexen which I played on an old family computer.
BoF 1 was good, but not great. BoF 2 belongs with Lufia 2 and the Square gang IMO.