Happy new year!
I think rocket jumping can be fun in games where its either really fast paced (arena/multiplayer) gameplay with lots of explosive weapons (ut rocket arena is most obvious example I quess) or if there are many elevations in the map (like hill that is otherwise more difficult to reach) and that those elevations offer some advantage (like a nice vantage point to snipe enemies etc). Though in my opinion the disadvantage of rocket jumps is that it removes a tactic element when using explosives (since you cannot do much self damage) and ofcourse you will have to set higher boundaries in maps (otherwise a player could jump out of the level ).

Iirc what I think is an interesting example of using rocket jump accidently as a feature is in Jedi Knight 1, where you already have gameplay that uses the z-axis alot (/force jump) and rocket jump offers an alternative when your force is low and e.g. quickly need to escape.