Home » SQL & PL/SQL » SQL & PL/SQL » json fetching using JSON_TABLE (Oracle 12c)
json fetching using JSON_TABLE [message #675773] |
Thu, 18 April 2019 06:31 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
--not working
SELECT
jt.slab_id,
jt.rule_id,
substr(jt.group_code, instr(jt.group_code, ':', 1, 1) + 1) group_code,
jt.no_of_authorizer
FROM
JSON_TABLE ( '{
"data": {
"matrixName": "aaaaaa",
"authMode": "Random",
"matrixInfo": [{
"startSlab": "11",
"endSlab": "222",
"ruleInfo": [{
"ruleId": "s1_r1",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg0_"
}]
}],
"slabId": "1"
},
{
"startSlab": "444",
"endSlab": "55656",
"ruleInfo": [{
"ruleId": "s2_r1",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg1_"
}]
},
{
"ruleId": "s2_r2",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg2_"
}]
}],
"slabId": "2"
}]
}
}'
, '$.data'
COLUMNS (
slab_id NUMBER PATH '$.matrixInfo[*].slabId',
rule_id VARCHAR2 ( 30 ) PATH '$.matrixInfo.ruleInfo[*].ruleId',
NESTED PATH '$.matrixInfo.ruleInfo.groupInfo[*]'
COLUMNS (
no_of_authorizer NUMBER PATH '$.noOfAuth',
priority NUMBER PATH '$.priority',
group_code VARCHAR2 ( 150 ) PATH '$.groupCode'
)
)
)
AS "JT";
--working
SELECT
jt.slab_id,
jt.rule_id,
substr(jt.group_code, instr(jt.group_code, ':', 1, 1) + 1) group_code,
jt.no_of_authorizer
FROM
JSON_TABLE ( '{
"data": {
"matrixName": "aaaaaa",
"authMode": "Random",
"matrixInfo": [{
"slabId": "1",
"startSlab": "11",
"endSlab": "222",
"ruleInfo": [{
"ruleId": "s1_r1",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg0_"
}]
}]
},
{ "slabId": "2",
"startSlab": "444",
"endSlab": "55656",
"ruleInfo": [{
"ruleId": "s2_r1",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg1_"
}]
},
{
"ruleId": "s2_r2",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg2_"
}]
}]
}]
}
}'
, '$.data'
COLUMNS (
slab_id NUMBER PATH '$.matrixInfo[*].slabId',
rule_id VARCHAR2 ( 30 ) PATH '$.matrixInfo.ruleInfo[*].ruleId',
NESTED PATH '$.matrixInfo.ruleInfo.groupInfo[*]'
COLUMNS (
no_of_authorizer NUMBER PATH '$.noOfAuth',
priority NUMBER PATH '$.priority',
group_code VARCHAR2 ( 150 ) PATH '$.groupCode'
)
)
)
AS "JT";
Hi All,
I could not fetch slabId from the first query however the second query fetched it when i just changed the place of slabIds.
Please help me to find what is going wrong.
Thanks,
|
|
|
|
|
Re: json fetching using JSON_TABLE [message #675777 is a reply to message #675776] |
Thu, 18 April 2019 08:53 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
@Blackswan - those are JSON queries, no tables involved, you can copy and paste and run them in your DB as is, assuming your DB is a high enough version.
@sss111ind - expected output and precise oracle version would help.
I get all null values from both in my 12.1.0.2.0
|
|
|
Re: json fetching using JSON_TABLE [message #675792 is a reply to message #675777] |
Sat, 20 April 2019 03:51 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
Sorry for late reply,
We are using the below version.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Excepted output from first query would be:-
SLAB_ID RULE_ID GROUP_CODE NO_OF_AUTHORIZER
1 s1_r1 DEMOCORP2_fsdg0_ 1
2 s2_r1 DEMOCORP2_fsdg1_ 1
2 s2_r2 DEMOCORP2_fsdg2_ 1
[Updated on: Sat, 20 April 2019 03:52] Report message to a moderator
|
|
|
Re: json fetching using JSON_TABLE [message #675794 is a reply to message #675792] |
Sat, 20 April 2019 05:25 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You simply got lucky with "working" one. In general, Oracle should raise error since '$.matrixInfo[*].slabId' doesn't map to a singleton. Same way is '$.ruleInfo[*].ruleId'. Anyway, correct query would be:
SELECT
jt.slab_id,
jt.rule_id,
substr(jt.group_code, instr(jt.group_code, ':', 1, 1) + 1) group_code,
jt.no_of_authorizer
FROM
JSON_TABLE ( '{
"data": {
"matrixName": "aaaaaa",
"authMode": "Random",
"matrixInfo": [{
"startSlab": "11",
"endSlab": "222",
"ruleInfo": [{
"ruleId": "s1_r1",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg0_"
}]
}],
"slabId": "1"
},
{
"startSlab": "444",
"endSlab": "55656",
"ruleInfo": [{
"ruleId": "s2_r1",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg1_"
}]
},
{
"ruleId": "s2_r2",
"groupInfo": [{
"noOfAuth": "1",
"groupCode": "DEMOCORP2_fsdg2_"
}]
}],
"slabId": "2"
}]
}
}'
, '$.data.matrixInfo[*]'
COLUMNS
(
slab_id NUMBER PATH '$.slabId',
NESTED PATH '$.ruleInfo[*]'
COLUMNS
(
rule_id VARCHAR2 ( 30 ) PATH '$.ruleId',
NESTED PATH '$.groupInfo[*]'
COLUMNS
(
no_of_authorizer NUMBER PATH '$.noOfAuth',
priority NUMBER PATH '$.priority',
group_code VARCHAR2 ( 150 ) PATH '$.groupCode'
)
)
)
)
AS "JT"
/
SLAB_ID RULE_ID GROUP_CODE NO_OF_AUTHORIZER
---------- ------------------------------ -------------------- ----------------
1 s1_r1 DEMOCORP2_fsdg0_ 1
2 s2_r1 DEMOCORP2_fsdg1_ 1
2 s2_r2 DEMOCORP2_fsdg2_ 1
SQL>
SY.
[Updated on: Sat, 20 April 2019 05:40] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 18:55:24 CDT 2024
|