'ORACLE - JSON To Key Value Pair Table
Is there any way to obtain a table with key/value pairs from a CLOB Json Column?
The idea here is to get these values, on a dynamic way. Because the CLOB column does not always contain the same structure.
I've created a function that does this, however since it literally parses the json string, when we use it in a table with many records its very slow. And by very slow I mean like 2-5 records per second, i know it's terrible.
The Oracle tools (v.12c) do not provide a dynamic way to obtain the json tags/values, we have always to specify the paths.
I've been digging all around without any luck. Any thoughts?
Solution 1:[1]
Here's a variant that will walk a nested structure.
SQL> drop FUNCTION PROCESS_JSON_DOCUMENT
2 /
Function dropped.
SQL> drop TYPE NV_PAIR_TABLE
2 /
Type dropped.
SQL> drop TYPE NV_PAIR_T
2 /
Type dropped.
SQL> create or replace TYPE NV_PAIR_T as object (
2 JSON_PATH VARCHAR2(4000),
3 VALUE VARCHAR2(4000)
4 )
5 /
Type created.
SQL> create or replace TYPE NV_PAIR_TABLE
2 as TABLE of NV_PAIR_T
3 /
Type created.
SQL> create or replace FUNCTION PROCESS_JSON_DOCUMENT(P_JSON_PATH VARCHAR2, P_JSON_DOCUMENT VARCHAR2)
2 return NV_PAIR_TABLE PIPELINED
3 as
4 V_JSON_OBJECT JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOCUMENT);
5 V_KEY_LIST JSON_KEY_LIST := V_JSON_OBJECT.get_keys();
6 V_KEY_NAME VARCHAR2(4000);
7 V_JSON_PATH VARCHAR2(4000);
8 V_CHILD_DOCUMENT VARCHAR2(4000);
9 begin
10 for i in 1..V_KEY_LIST.count loop
11 V_KEY_NAME := V_KEY_LIST(i);
12 if (V_JSON_OBJECT.get_type(V_KEY_LIST(i)) <> 'OBJECT') then
13 pipe row (NV_PAIR_T(P_JSON_PATH || '.' || V_KEY_NAME,V_JSON_OBJECT.get_string(V_KEY_NAME)));
14 else
15 V_JSON_PATH := P_JSON_PATH || '.' || V_KEY_NAME;
16 V_CHILD_DOCUMENT := V_JSON_OBJECT.get_object(V_KEY_NAME).to_string();
17 for j in (select * from TABLE(PROCESS_JSON_DOCUMENT(V_JSON_PATH, V_CHILD_DOCUMENT))) loop
18 pipe row (NV_PAIR_T(J.JSON_PATH,J.VALUE));
19 end loop;
20 end if;
21 end loop;
22 end;
23 /
Function created.
SQL> column JSON_PATH format A32
SQL> column VALUE format A32
SQL> select *
2 from TABLE(PROCESS_JSON_DOCUMENT('$','{"A":"AA", "B":"BB", "C":"CC", "X" : {"A":"AA", "B":"BB", "C":"CC"}}'))
3 /
$.A AA
$.B BB
$.C CC
$.X.A AA
$.X.B BB
$.X.C CC
6 rows selected.
SQL>
Solution 2:[2]
I have below json not able to parse above function. I want to parse data tag value
{
"data": [
{
"6": {
"value": "test1"
},
"7": {
"value": "test2"
},
"8": {
"value": ""
},
"9": {
"value": 1
},
"10": {
"value": "test5"
},
"11": {
"value": ""
}
},
{
"6": {
"value": "test2"
},
"7": {
"value": "test3"
},
"8": {
"value": ""
},
"9": {
"value": 2
},
"10": {
"value": "test5"
},
"11": {
"value": ""
}
},
{
"6": {
"value": "test1"
},
"7": {
"value": "test2"
},
"8": {
"value": ""
},
"9": {
"value": 1
},
"10": {
"value": "test5"
},
"11": {
"value": ""
}
},
{
"6": {
"value": "test2"
},
"7": {
"value": "test3"
},
"8": {
"value": ""
},
"9": {
"value": 2
},
"10": {
"value": "test5"
},
"11": {
"value": ""
}
}
],
"fields": [
{
"id": 9,
"label": "COL1",
"type": "numeric"
},
{
"id": 6,
"label": "COL2",
"type": "text"
},
{
"id": 7,
"label": "COL3",
"type": "text"
},
{
"id": 8,
"label": "COL4",
"type": "text"
},
{
"id": 10,
"label": "COL5",
"type": "text"
},
{
"id": 11,
"label": "COL_DATE6",
"type": "date"
}
],
"metadata": {
"numFields": 6,
"numRecords": 4,
"skip": 0,
"totalRecords": 4
}
}
Solution 3:[3]
The following works to stop the execution of the loop and restart it. It's using a separate done variable to check in the loop.
However, note that this only works because sleep is using setTimeout during which the handlers from jQuery can be triggered. Without that it wouldn't work as JS is single-threaded, see this question. The stop handler would not be called and the loop run forever.
let done = false;
let a = 0;
let code = `while(!done){
await sleep(1000);
cons('step:'+a);
a++;
}`;
$('span').click(e => {
const target = $(e.target).text()
if (target === 'start') {
done = false
a = 0
cons('start');
eval("(async () => {" + code + "})()");
}
if (target === 'stop') {
done = true
}
});
function sleep(ms) {
return new Promise((resolve) => {
setTimeout(resolve, ms);
});
}
function cons(s)
{
$('div').html($('div').html()+ s +'<br>');
}span { cursor: pointer; }<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<span>start</span> <span>stop</span>
<div>...<br></div>Solution 4:[4]
Added at the request to show an example with a return variable and what is wrong with it. In order for it to work correctly, it must be inserted into each line of code in order to be able to interrupt the execution of eval at really any time, otherwise part of the code is still executed after clicking on the stop.
start stop
...
start
step:0
next:0
step:1
next:1
step:2
stop <-!!!!!
next:2 <-!!!!!
let a = 0;
let run = 0;
let code = `while(true){
if (run != 100) return;
cons('step:'+a);
await sleep(5000);
cons('next:'+a);
a++;
}`;
$('span').click(e => {
if ($(e.target).text() == 'start') {
run = 100;
cons('start');
eval("(async () => {" + code + "})()");
}
if ($(e.target).text() == 'stop') {
run = 0;
cons('stop');
}
});
function sleep(ms) {
return new Promise((resolve) => {
setTimeout(resolve, ms);
});
}
function cons(s)
{
$('div').html($('div').html()+ s +'<br>');
}span { cursor: pointer; }<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<span>start</span> <span>stop</span>
<div>...<br></div>Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | mark d drake |
| Solution 2 | sudip dutta |
| Solution 3 | matthiasgiger |
| Solution 4 | iNji 555 |