Nested, inline pivot table (oracle)
I don't really understand why you're trying to pivot anything, so maybe I'm missing your point. But try this:

select 
  id, name
, regexp_replace(metadata, '.*"a":"([^"]+)".*', '\1') m_a
, regexp_replace(metadata, '.*"b":"([^"]+)".*', '\1') m_b
from foo;


Doesn't rely on the order of the JSON attributes to always be the same. It's not completely safe - doesn't handle quoted " in particular, but it does the job for your sample.