I've only been a few times, and mostly because I could get in for free as a student or due to my current part-time job at the theatre. Nonetheless I feel impelled to share my unqualified opinions.
My absolute favorite was Macbeth
by Verdi, I don't remember the sound that well, but the staging was really memorable, very surreal with colored lighting, monumental set pieces and good costumes. All the other ones I've seen the stage design and the costumes were pretty average/bad. In fact they seem to recycle most of the costumes from various different pieces without too much regard and put it off as creative freedom or whatever. I don't mind it that much but it's just kind of sad.
Recently I've seen Mussorgsky's Boris Godunov
(based on a play by Pushkin) which was pretty good (again if you didn't pay too much attention to the stage design, and to the accents, and to the bad subtitles). They even made a trailer for it, see for yourself: https://www.youtube.com/watch?v=FRvQG6CzrV4
Though I'm afraid that either something is wrong with my ears or the acoustics aren't all that great in the theatre I've watched it at. Then again I didn't exactly have the best seat of course. And it really matters where you sit so if you're actually paying for your ticket you need to really make sure it's a good adaptation else it might not be worth the money.
Also I've seen half of Der Ring an einem Abend
which is a shortened version of Wagner's Ring cycle with intermittent commentary written by Loriot which is performed by a speaker. It wasn't as cringe as I expected it to be, but I didn't really enjoy it either. Maybe I just don't like Wagner that much, it's a bit too pompous, too many crashing cymbals. Though I did enjoy the Parsifal
movie adaptation by Syberberg, but it's also a visual feast. What's the appeal of Wagner to you?
Then I've also seen Eugen Onegin
by Tchaikovsky but that was quite immemorable, also the accents again pained my ears.
Also, in general, I think I'd enjoy opera much more if they'd just project proper subtitles which really can't be that hard given current technology. But I guess there's still quite a bit of elitist/conservative mentality going on around opera.