I have used the Nikon 105mm for many years in the greenest water and visibility as low as 2-3 feet. With a spotting (white or red) light you can still get really good images. If you worried about distance you can get closer with a diopter. I use a +5 subsea with great results.
I originally used this lens with a D70 and D300 DX cameras. It worked very well on those cameras, but the autofocus performance improved a lot with the D800. I expect any DX camera being sold today will be at least as good, certainly the D500 would be as good or better.
Below is a shot I took recently in some of the worst visibility I've experienced in a long time. Probably 3 feet of visibility. Finding something to shoot was difficult, but taking some shots once found was not difficult. This was shot with the D850, 105mm, +5 closeup lens, and the Backscatter Snoot.