Nested, inline pivot table (oracle)

Question

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!

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
– Ditto, Commented Feb 10, 2016 at 21:23

Mat · Accepted Answer · 2016-02-11 08:35:03Z

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.

answered Feb 11, 2016 at 8:35

Mat

10.3k4 gold badges44 silver badges40 bronze badges

Great, thanks! That works. I was overcomplicating the situation.

Justin Lewis
– Justin Lewis

2016-02-11 09:29:15 +00:00
Commented Feb 11, 2016 at 9:29

Add a comment |

Stack Exchange Network

Nested, inline pivot table (oracle)

1 Answer 1

Your Answer

Hot Network Questions

Nested, inline pivot table (oracle)

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions