The sweet potato that you eat can be grown in an aquarium. The roots can take over the whole tank REALLY FAST. Just anchor the sweet potato about half in/half out of the water and it will start growing. Thrives in high light, OK in medium. Not a low light house plant.
I have had most of the common Aroids (Philodendron and related) plants thriving in emersed set ups, usually roots in the water, stems hanging over the side of the tank. Pothos and Syngonium have been the best. Spider plant was OK as long as it got enough light, but was never great.
The only Orchid that I can ID that I have seen thrive in anything like this is the Jewel Orchid, a terrestrial Orchid with dark brown leaves and pink stripes. This was more of a riparium set up, though, the roots were not loose in the tank. I have seen some other Orchids that someone imported from Hawaii growing emersed, actually on something like shelving that created a tiered water fall, but I do not know what species.
I have found hanging plants to be easier to work with. Upright plants tend to fall over if they are not supported.
I have pumped aquarium water through a window box filled with lava rock. This is open so the water flows right through, but supports the upright plants very well. Prayer plant and several species of Dracaena worked well in that. Now I have Heartleaf Philodendron in it.