Hi,
I have a query on the JSON feature of 12c. I have the following JSON data
CREATE TABLE jsontry( id VARCHAR2(11),
str CLOB,
CONSTRAINT jsontry_PK PRIMARY KEY (id),
CONSTRAINT jsontry_CHK CHECK (str IS JSON) ENABLE
);
Insert into jsontry values ('1','{"a":"100","b":"200"}');
Insert into jsontry values ('2','{"c":"300"}');
Insert into jsontry values ('3','{"a":"400","c":"500","d":["100","200","300","400"]}');
commit;
SELECT a.id,jt.*
FROM jsontry a,
JSON_TABLE(str, '$.*'
COLUMNS (
"Attribute" varchar2(5) PATH '$',
"Value" varchar2(5) PATH '$'
)) "JT";
ID ATTRIBUTE VALUE
----------- --------- -----
1 100 100
1 200 200
2 300 300
3 400 400
3 500 500
3
6 rows selectedThe issue is that i want the column values to be shown for the attribute column and also i need the array value for id 3 to be also converted to attribute and value as shown below
Expected Output
ID ATTRIBUTE VALUE
----------- --------- -----
1 a 100
1 b 200
2 c 300
3 a 400
3 c 500
3 d 100
3 d 200
3 d 300
3 d 400
I tried a lot using the available JSON functions, but couldnt find any options to get the columns.
As always expecting a solution for my query,
There's a few parts to this. First you need to get the attribute names. I'm not aware of a way to do this dynamically.
So you need to inspect your document and list out the attributes you want in the columns clause:
select a.id, jt.*
from jsontry a,
json_table(str, '$'
columns (
"a" varchar2(5) path '$.a',
"b" varchar2(5) path '$.b',
"c" varchar2(5) path '$.c',
"d" varchar2(5) path '$.d'
)
) jt;Next you need to extract the elements of the array d. You can do this using the nested path clause:
select a.id,jt.*
from jsontry a,
json_table(str, '$'
columns (
"a" varchar2(5) path '$.a',
"b" varchar2(5) path '$.b',
"c" varchar2(5) path '$.c',
nested path '$.d[*]' columns (
"d" path '$' null on error
)
)
) "JT";
ID A B C D
----------- ----- ----- ----- --------------------
1 100 200
2 300
3 400 500 100
3 400 500 200
3 400 500 300
3 400 500 400Finally to convert these into rows you'll need to unpivot:
select * from (
select a.id,jt.*
from jsontry a,
json_table(a.str, '$'
columns (
"a" varchar2(5) path '$.a',
"b" varchar2(5) path '$.b',
"c" varchar2(5) path '$.c',
nested path '$.d[*]' columns (
"d" path '$' null on error
)
)
) "JT"
)
unpivot (
val for attrib in (
a as 'a', b as 'b', c as 'c', d as 'd'
)
)
order by 1, 2, 3;
ID A VAL
----------- - ----------
1 a 100
1 b 200
2 c 300
3 a 400
3 a 400
3 a 400
3 a 400
3 c 500
3 c 500
3 c 500
3 c 500
3 d 100
3 d 200
3 d 300
3 d 400
15 rows selectedNote the values for 3 a and c are repeated. To avoid this you'll need to manipulate the results so the non-array items only appear once. One way to do this is with a case expression like the following:
case
when row_number() over (partition by a.id order by d) = 1 then
jt.a
endIf you want to read more about the nested table option, head to:
https://blogs.oracle.com/jsondb/entry/the_new_sql_json_query3