He went down to the Challenger Deep, which is a part of the Mariana Trench in the Pacific Ocean. He's the only one to have gone down there alone. The only other people to have gone there are a US Navy lieutenant and a Swiss explorer who went together in 1960!
The trek took him 35,800 feet deep, which means that Mount Everest's height could fit inside the trench. He described it as a completely alien world and that the bottom was very soft, gelatinous and plain. One of the things some people were hoping he would find down there are some of the giant sea creatures of myth. He says he didn't find any particularly strange or large lifeforms that we've dreamed up.
He saw no fish down there, the only free swimmers he saw were shrimp like animals. Sadly, he was not able to collect samples. The hydraulics of the mechanical arm the vessel was equipped with failed. The submergible was co-designed by Cameron with other scientists and he was even able to live tweet his descent! The vessel is shaped like a drill, which allows it to dive at extremely high speeds of 500 to 700 feet per minute. It only took him 2:36 hours to get there. He had 3D cameras on hand for the documentary that's being made about the project.
As far as his movie career goes, he said he's about to shift focus into Avatar 2 and 3. The things he saw down there, he said, feed directly into his imagination. We can definitely expect some of his expedition to show through the movie screen.