The John Hancock building has some of the deepest caissons of any building in the world into bedrock.

Yes, but that bedrock is way down there.

From [0] : “Construction of the John Hancock Center began in 1965; however, was stopped in 1967 because the building kept sinking. Because of the John Hancock Center's lakeside location, 57 concrete caissons had to be sunk into 10 ft wide holes drilled 197-feet below grade into bedrock, resulting in the John Hancock Center having the deepest foundation of any building at the time.”

[0] - https://www.beck-technology.com/blog/how-did-they-build-that...

In many places foundations are not sunk to bedrock, rock is far too deep to reach. Instead the pilings use friction to maintain their position.

What places besides the mess in SF ? (I'm not sure how to search such a query, but super interesting)

Bedrock is very deep in Seattle. You can see the map here: https://pubs.usgs.gov/mf/1692/plate-1.pdf

For instance the space needle is just on a giant hunk of concrete that is only 30ft deep. https://www.spaceneedle.com/history#:~:text=An%20underground....

But from memory most large buildings in the downtown area are on deep foundations in soils, not to bedrock.