I have a oracle db (19c) table with a clob column containing multiple json files. For further processing i need the jsons that look like this:
"Parameter": {
"Sausage Length": {
"0": "20-30cm",
"1": "30-40cm",
"2": null
},
"Topping": {
"0": "Kraut",
"1": "Red Onions",
...
},
...
}
come out in this tabular form:
| Parameter | OP_ID | OP_VAL
| ---------------- | -------- |--------
| Sausage Length | 0 | 20-30cm
| Sausage Length | 1 | 30-40cm
| Sausage Length | 2 | null
| Topping | 0 | Kraut
| Topping | 1 | Red Onions
| ... | ... | ...
I understand, that column one and two are actually keys, while column three is the value.
So with parsing the key's with json_dataguide i get the key's, but not the value.
With using json_table i get the values but need to know the key's (which i don't, since they are dynamic in both levels).
Simply putting the whole "Parameter" section as FORMAT JSON PATH and parsing it "by hand" with instr & substr & connect by level got me so confused that i gave up on it.
I also have seen solutions that generate the key's with json_dataguide via listagg textstring and add these to the json_table query and then pivoting everything until the correct format is reached, but this would end up on the same complexity as with instr & substr & connect by level custom parsing.
What oracle json function or query am i missing?
