1

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 < 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!

  • 1
    Please clarify possible typo in first table data: should this: '{"a":"x","b","y"} actually be this: '{"a":"x","b":"y"} ?? (2nd : between "b" and "y") ? – Ditto Feb 10 '16 at 21:23
  • @Ditto, yes, thanks for spotting that. Edited. – Justin Lewis Feb 10 '16 at 21:54
1

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.

  • Great, thanks! That works. I was overcomplicating the situation. – Justin Lewis Feb 11 '16 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.