Nested, inline pivot table (oracle) I'm trying to go from a table like this:

ID | NAME | METADATA
-------------------------------
1  | test | '{"a":"x","b":"y"}'


where the metadata column is a JSON string. What I'd like to end up with is this:

ID | NAME | A | B
-----------------
1  | test | x | y


where the JSON "keys" are merged in-line as column names and the corresponding JSON "values" as the fields. So far, I have managed to parse the JSON and create a "pivot" table like this:

A | B
-----
x | y


but I can't work out how to append the other columns from the original table. My code so far:

SELECT * FROM (
    SELECT 
        SUBSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)), 1, INSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),':') - 1) COL_NAME,     
        SUBSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),    INSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),':') + 1) COL_VAL  
    FROM   (SELECT REPLACE (REPLACE (REPLACE (:json, '}'), '{'),  '"') AS str FROM DUAL)
    CONNECT BY LEVEL &lt; LENGTH (str) - LENGTH (REPLACE (str, ',', NULL)) + 2
)
PIVOT
(
  MAX(COL_VAL)
  FOR COL_NAME IN ('a' AS A, 'b' AS B)
);


This is where my SQL knowledge fails. 

Note: I know the JSON parsing is ugly, but I can't use a 3rd-party library and I'm running on 11g so don't have any built-in JSON support.

Thanks in advance for any help!
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.