I need an array to be extracted from a json column in oracle 12c. JSON_QUERY is working for some rows while it isnt returning anything for a few rows.
I'm trying to extract the array which is in the 56th row and the array contains about 11000 characters.
select json_query(test_column, '$.test' ) from test_table;
It is a valid JSON, but it returns empty column.
Create table test_table(test_column clob);
alter table test_table add constraint CK_test
CHECK (test_column IS JSON (LAX));
insert into test_table values(<<below json data>>);
Here is my complete json:
{
"wkymxntlkllqlktdonOrsxr": 1,
"workkgrxxmxntds": "5g63w8gw_54",
"lxgklxntdtyRxfxrxnlx": {
"lxgklxntdtyds": "wgwgg4w81",
"loqntrylosx": "qS"
},
"lkllqlktdonTywxlosx": {
"losx": "GROSS_TO_NxT"
},
"wkymxntGroqwds": "53g1g6wg_1",
"wrdmkryLdvxsdnmqrdssdltdons": [{
"mqrdssdltdonds": "sxkg1x6s-w43w-4f65-w58h-w5fgwx18111x",
"mqrdssdltdonlosx": {
"losx": "qS",
"nkmx": "qndtxs Stktxs Fxsxrkl"
},
"mqrdssdltdonLxvxllosx": {
"losx": "FxsxRkL"
}
},
{
"mqrdssdltdonds": "1hw1wk1f-xsk3-4s44-hwkg-wllg68x3fwh1",
"mqrdssdltdonlosx": {
"losx": "kL",
"nkmx": "klkhkmk"
},
"mqrdssdltdonLxvxllosx": {
"losx": "STkTx"
}
}],
"wrdmkryWorkxsdnmqrdssdltdons": [{
"mqrdssdltdonds": "sxkg1x6s-w43w-4f65-w58h-w5fgwx18111x",
"mqrdssdltdonlosx": {
"losx": "qS",
"nkmx": "qndtxs Stktxs Fxsxrkl"
},
"mqrdssdltdonLxvxllosx": {
"losx": "FxsxRkL"
}
},
{
"mqrdssdltdonds": "1hw1wk1f-xsk3-4s44-hwkg-wllg68x3fwh1",
"mqrdssdltdonlosx": {
"losx": "kL",
"nkmx": "klkhkmk"
},
"mqrdssdltdonLxvxllosx": {
"losx": "STkTx"
}
}],
"wkySlhxsqlxRxfxrxnlx": {
"wkySlhxsqlxds": "1xlf18ff-3gww-gw6f-h4w3-83fx34sk1f38",
"wkywxrdosds": "wgww-w1"
},
"test": [{
"woldlyTywxds": "Rxgqlkrwky",
"woldlyOwtdonds": "Monxtkrywky",
"lldxntOwtdondnstknlxds": "61w16gw_1",
"lkllqlktdonMxthosds": "Hoqrlywky",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "rxgqlkrHoqrsWorkxs",
"dnwqtVklqx": {
"qqkntdty": 4g.g,
"qndtlosx": {
"losx": "HqR"
}
}
},
{
"dnwqtVkrdkhlxds": "kwwldxsHoqrlyRktx",
"dnwqtVklqx": {
"rktx": 46,
"hksxqndtlosx": {
"losx": "HqR"
},
"lqrrxnlylosx": "qSs"
}
},
{
"dnwqtVkrdkhlxds": "skysWorkxsdnmqrdssdltdon",
"dnwqtVklqx": {
"qqkntdty": 5.g,
"qndtlosx": {
"losx": "skY"
}
}
},
{
"dnwqtVkrdkhlxds": "slhxsqlxsWorkwxrdosHoqrs",
"dnwqtVklqx": {
"qqkntdty": 4g.g,
"qndtlosx": {
"losx": "HqR"
}
}
},
{
"dnwqtVkrdkhlxds": "slhxsqlxsWorkwxrdosskys",
"dnwqtVklqx": {
"qqkntdty": 5.g,
"qndtlosx": {
"losx": "skY"
}
}
}],
"workwxrdoss": [{
"wxrdosds": "wgww-g1-g1Tgg:gg:gg.ggg-g4:gg",
"wxrdoslosx": "sxfkqlt",
"stkrtsktxTdmx": "wgww-g1-g1Tgg:gg:gg.ggg-g4:gg",
"xnssktxTdmx": "wgww-g1-g1Tgg:gg:gg.ggg-g4:gg"
}]
},
{
"woldlyTywxds": "4g1k",
"woldlyOwtdonds": "lkslksdnglontrdhqtdon",
"lldxntOwtdondnstknlxds": "5gwwg458_1",
"lkllqlktdonMxthosds": "kssoldktxhksxlkslksdngwxrlxntkgx",
"kwwldlkhdldtyOwtdons": [{
"owtdonds": "FdrstwkywxrdosOfqsTkxMonth"
}],
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "tkxkhdldtylosx",
"dnwqtVklqx": "ROTH"
},
{
"dnwqtVkrdkhlxds": "kssoldktxsxfxrrklwxrlxntkgx",
"dnwqtVklqx": {
"wxrlxntkgx": 3g.g
}
},
{
"dnwqtVkrdkhlxds": "sxfdnxslontrdhqtdonwlknkwwldlkhlxxkrndngs",
"dnwqtVklqx": {
"kggrxgktxSxlxltors": [{
"lldxntOwtdondnstknlxds": "5g615w31_1"
},
{
"lldxntOwtdondnstknlxds": "5g813w18_1"
},
{
"lldxntOwtdondnstknlxds": "5gww18ww_1"
},
{
"lldxntOwtdondnstknlxds": "13884g11_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w3g3_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14661_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1558_1"
},
{
"lldxntOwtdondnstknlxds": "5gww415g_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1616_1"
},
{
"lldxntOwtdondnstknlxds": "1413881g_1"
},
{
"lldxntOwtdondnstknlxds": "13884g1w_1"
},
{
"lldxntOwtdondnstknlxds": "1385155w_1"
},
{
"lldxntOwtdondnstknlxds": "13851561_1"
},
{
"lldxntOwtdondnstknlxds": "w31156g1_1"
},
{
"lldxntOwtdondnstknlxds": "5gw88341_1"
},
{
"lldxntOwtdondnstknlxds": "5gww8g15_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8ww11_1"
},
{
"lldxntOwtdondnstknlxds": "5g641433_1"
},
{
"lldxntOwtdondnstknlxds": "511163w6_1"
},
{
"lldxntOwtdondnstknlxds": "5gwwg513_1"
},
{
"lldxntOwtdondnstknlxds": "w311585g_1"
},
{
"lldxntOwtdondnstknlxds": "51g1g43w_1"
},
{
"lldxntOwtdondnstknlxds": "5g813863_1"
},
{
"lldxntOwtdondnstknlxds": "61w16gw_1"
},
{
"lldxntOwtdondnstknlxds": "5g813843_1"
},
{
"lldxntOwtdondnstknlxds": "13884ggw_1"
},
{
"lldxntOwtdondnstknlxds": "51wg4483_1"
},
{
"lldxntOwtdondnstknlxds": "14138851_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14486_1"
},
{
"lldxntOwtdondnstknlxds": "51wg4488_1"
},
{
"lldxntOwtdondnstknlxds": "5g813ww1_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1w45_1"
},
{
"lldxntOwtdondnstknlxds": "5g813w38_1"
},
{
"lldxntOwtdondnstknlxds": "13884g11_1"
},
{
"lldxntOwtdondnstknlxds": "5g816w4g_1"
},
{
"lldxntOwtdondnstknlxds": "5gw15g1w_1"
},
{
"lldxntOwtdondnstknlxds": "5gw148g4_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14863_1"
},
{
"lldxntOwtdondnstknlxds": "5gww181w_1"
},
{
"lldxntOwtdondnstknlxds": "5gww8g86_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w16w_1"
},
{
"lldxntOwtdondnstknlxds": "5g816gg3_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1831_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14131_1"
},
{
"lldxntOwtdondnstknlxds": "51g1gw58_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w33w_1"
},
{
"lldxntOwtdondnstknlxds": "51gg1131_1"
},
{
"lldxntOwtdondnstknlxds": "51gg4g55_1"
},
{
"lldxntOwtdondnstknlxds": "5g816g31_1"
},
{
"lldxntOwtdondnstknlxds": "51gg4ggw_1"
}]
}
}]
},
{
"woldlyTywxds": "4g1k",
"woldlyOwtdonds": "lkslksdnglontrdhqtdon",
"lldxntOwtdondnstknlxds": "5gwwg458_1",
"lkllqlktdonMxthosds": "kssoldktxhksxlkslksdngwxrlxntkgx",
"kwwldlkhdldtyOwtdons": [{
"owtdonds": "FdrstwkywxrdosOfqsTkxMonth"
}],
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "tkxkhdldtylosx",
"dnwqtVklqx": "TRksdTdONkL"
},
{
"dnwqtVkrdkhlxds": "kssoldktxsxfxrrklwxrlxntkgx",
"dnwqtVklqx": {
"wxrlxntkgx": ww.g
}
},
{
"dnwqtVkrdkhlxds": "sxfdnxslontrdhqtdonwlknkwwldlkhlxxkrndngs",
"dnwqtVklqx": {
"kggrxgktxSxlxltors": [{
"lldxntOwtdondnstknlxds": "5g615w31_1"
},
{
"lldxntOwtdondnstknlxds": "5g813w18_1"
},
{
"lldxntOwtdondnstknlxds": "5gww18ww_1"
},
{
"lldxntOwtdondnstknlxds": "13884g11_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w3g3_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14661_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1558_1"
},
{
"lldxntOwtdondnstknlxds": "5gww415g_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1616_1"
},
{
"lldxntOwtdondnstknlxds": "1413881g_1"
},
{
"lldxntOwtdondnstknlxds": "13884g1w_1"
},
{
"lldxntOwtdondnstknlxds": "1385155w_1"
},
{
"lldxntOwtdondnstknlxds": "13851561_1"
},
{
"lldxntOwtdondnstknlxds": "w31156g1_1"
},
{
"lldxntOwtdondnstknlxds": "5gw88341_1"
},
{
"lldxntOwtdondnstknlxds": "5gww8g15_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8ww11_1"
},
{
"lldxntOwtdondnstknlxds": "5g641433_1"
},
{
"lldxntOwtdondnstknlxds": "511163w6_1"
},
{
"lldxntOwtdondnstknlxds": "5gwwg513_1"
},
{
"lldxntOwtdondnstknlxds": "w311585g_1"
},
{
"lldxntOwtdondnstknlxds": "51g1g43w_1"
},
{
"lldxntOwtdondnstknlxds": "5g813863_1"
},
{
"lldxntOwtdondnstknlxds": "61w16gw_1"
},
{
"lldxntOwtdondnstknlxds": "5g813843_1"
},
{
"lldxntOwtdondnstknlxds": "13884ggw_1"
},
{
"lldxntOwtdondnstknlxds": "51wg4483_1"
},
{
"lldxntOwtdondnstknlxds": "14138851_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14486_1"
},
{
"lldxntOwtdondnstknlxds": "51wg4488_1"
},
{
"lldxntOwtdondnstknlxds": "5g813ww1_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1w45_1"
},
{
"lldxntOwtdondnstknlxds": "5g813w38_1"
},
{
"lldxntOwtdondnstknlxds": "13884g11_1"
},
{
"lldxntOwtdondnstknlxds": "5g816w4g_1"
},
{
"lldxntOwtdondnstknlxds": "5gw15g1w_1"
},
{
"lldxntOwtdondnstknlxds": "5gw148g4_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14863_1"
},
{
"lldxntOwtdondnstknlxds": "5gww181w_1"
},
{
"lldxntOwtdondnstknlxds": "5gww8g86_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w16w_1"
},
{
"lldxntOwtdondnstknlxds": "5g816gg3_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1831_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14131_1"
},
{
"lldxntOwtdondnstknlxds": "51g1gw58_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w33w_1"
},
{
"lldxntOwtdondnstknlxds": "51gg1131_1"
},
{
"lldxntOwtdondnstknlxds": "51gg4g55_1"
},
{
"lldxntOwtdondnstknlxds": "5g816g31_1"
},
{
"lldxntOwtdondnstknlxds": "51gg4ggw_1"
}]
}
}]
},
{
"woldlyTywxds": "4g1k",
"woldlyOwtdonds": "lkslksdnglontrdhqtdon",
"lldxntOwtdondnstknlxds": "5gwwg458_1",
"lkllqlktdonMxthosds": "kssoldktxNonsxfxrrklwxrlxntkgx",
"kwwldlkhdldtyOwtdons": [{
"owtdonds": "FdrstwkywxrdosOfqsTkxMonth"
}],
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "kssoldktxNonsxfxrrklwxrlxntkgx",
"dnwqtVklqx": {
"wxrlxntkgx": 31.g
}
},
{
"dnwqtVkrdkhlxds": "sxfdnxslontrdhqtdonwlknkwwldlkhlxxkrndngs",
"dnwqtVklqx": {
"kggrxgktxSxlxltors": [{
"lldxntOwtdondnstknlxds": "5g615w31_1"
},
{
"lldxntOwtdondnstknlxds": "5g813w18_1"
},
{
"lldxntOwtdondnstknlxds": "5gww18ww_1"
},
{
"lldxntOwtdondnstknlxds": "13884g11_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w3g3_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14661_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1558_1"
},
{
"lldxntOwtdondnstknlxds": "5gww415g_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1616_1"
},
{
"lldxntOwtdondnstknlxds": "1413881g_1"
},
{
"lldxntOwtdondnstknlxds": "13884g1w_1"
},
{
"lldxntOwtdondnstknlxds": "1385155w_1"
},
{
"lldxntOwtdondnstknlxds": "13851561_1"
},
{
"lldxntOwtdondnstknlxds": "w31156g1_1"
},
{
"lldxntOwtdondnstknlxds": "5gw88341_1"
},
{
"lldxntOwtdondnstknlxds": "5gww8g15_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8ww11_1"
},
{
"lldxntOwtdondnstknlxds": "5g641433_1"
},
{
"lldxntOwtdondnstknlxds": "511163w6_1"
},
{
"lldxntOwtdondnstknlxds": "5gwwg513_1"
},
{
"lldxntOwtdondnstknlxds": "w311585g_1"
},
{
"lldxntOwtdondnstknlxds": "51g1g43w_1"
},
{
"lldxntOwtdondnstknlxds": "5g813863_1"
},
{
"lldxntOwtdondnstknlxds": "61w16gw_1"
},
{
"lldxntOwtdondnstknlxds": "5g813843_1"
},
{
"lldxntOwtdondnstknlxds": "13884ggw_1"
},
{
"lldxntOwtdondnstknlxds": "51wg4483_1"
},
{
"lldxntOwtdondnstknlxds": "14138851_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14486_1"
},
{
"lldxntOwtdondnstknlxds": "51wg4488_1"
},
{
"lldxntOwtdondnstknlxds": "5g813ww1_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1w45_1"
},
{
"lldxntOwtdondnstknlxds": "5g813w38_1"
},
{
"lldxntOwtdondnstknlxds": "13884g11_1"
},
{
"lldxntOwtdondnstknlxds": "5g816w4g_1"
},
{
"lldxntOwtdondnstknlxds": "5gw15g1w_1"
},
{
"lldxntOwtdondnstknlxds": "5gw148g4_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14863_1"
},
{
"lldxntOwtdondnstknlxds": "5gww181w_1"
},
{
"lldxntOwtdondnstknlxds": "5gww8g86_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w16w_1"
},
{
"lldxntOwtdondnstknlxds": "5g816gg3_1"
},
{
"lldxntOwtdondnstknlxds": "5gww1831_1"
},
{
"lldxntOwtdondnstknlxds": "5gw14131_1"
},
{
"lldxntOwtdondnstknlxds": "51g1gw58_1"
},
{
"lldxntOwtdondnstknlxds": "5gw8w33w_1"
},
{
"lldxntOwtdondnstknlxds": "51gg1131_1"
},
{
"lldxntOwtdondnstknlxds": "51gg4g55_1"
},
{
"lldxntOwtdondnstknlxds": "5g816g31_1"
},
{
"lldxntOwtdondnstknlxds": "51gg4ggw_1"
}]
}
}]
},
{
"woldlyTywxds": "4g1k",
"woldlyOwtdonds": "lkslksdnglontrdhqtdon",
"lldxntOwtdondnstknlxds": "5gwwg458_1",
"lkllqlktdonMxthosds": "OrgkndzktdonwrofdtShkrxlontrdhqtdon",
"kwwldlkhdldtyOwtdons": [{
"owtdonds": "FdrstwkywxrdosOfqsTkxMonth"
}],
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "orgkndzktdonlontrdhqtdonkmoqnt",
"dnwqtVklqx": {
"kmoqnt": 1g,
"lqrrxnlylosx": "qSs"
}
}]
}],
"stktqtorywoldlydnwqts": [{
"woldlyTywxds": "dnlomxTkx",
"woldlyOwtdonds": "Stknskrs",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "wdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
},
{
"dnwqtVkrdkhlxds": "fdldngStktqs",
"dnwqtVklqx": "SdNGLx"
},
{
"dnwqtVkrdkhlxds": "nonRxsdsxntkldxndnsdlktor",
"dnwqtVklqx": {
"dnsdlktor": fklsx
}
},
{
"dnwqtVkrdkhlxds": "w4Formwgwgdnsdlktor",
"dnwqtVklqx": {
"dnsdlktor": trqx
}
},
{
"dnwqtVkrdkhlxds": "qsxHdghxrRktxdnsdlktor",
"dnwqtVklqx": {
"dnsdlktor": fklsx
}
}],
"stktqtorymqrdssdltdon": {
"mqrdssdltdonds": "sxkg1x6s-w43w-4f65-w58h-w5fgwx18111x",
"mqrdssdltdonLxvxllosx": {
"losx": "FxsxRkL"
}
}
},
{
"woldlyTywxds": "qnxmwloymxntdnsqrknlx",
"woldlyOwtdonds": "Stknskrs",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "wrdmkryWorkStktxqdWdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
},
{
"dnwqtVkrdkhlxds": "wdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
},
{
"dnwqtVkrdkhlxds": "lxgklxntdtyWdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
}],
"stktqtorymqrdssdltdon": {
"mqrdssdltdonds": "sxkg1x6s-w43w-4f65-w58h-w5fgwx18111x",
"mqrdssdltdonLxvxllosx": {
"losx": "FxsxRkL"
}
}
},
{
"woldlyTywxds": "Mxsdlkrx",
"woldlyOwtdonds": "Stknskrs",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "wdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
},
{
"dnwqtVkrdkhlxds": "lxgklxntdtyWdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
}],
"stktqtorymqrdssdltdon": {
"mqrdssdltdonds": "sxkg1x6s-w43w-4f65-w58h-w5fgwx18111x",
"mqrdssdltdonLxvxllosx": {
"losx": "FxsxRkL"
}
}
},
{
"woldlyTywxds": "SoldklSxlqrdty",
"woldlyOwtdonds": "Stknskrs",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "wdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
},
{
"dnwqtVkrdkhlxds": "lxgklxntdtyWdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
}],
"stktqtorymqrdssdltdon": {
"mqrdssdltdonds": "sxkg1x6s-w43w-4f65-w58h-w5fgwx18111x",
"mqrdssdltdonLxvxllosx": {
"losx": "FxsxRkL"
}
}
},
{
"woldlyTywxds": "dnlomxTkx",
"woldlyOwtdonds": "Stknskrs",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "fdldngStktqs",
"dnwqtVklqx": "SdNGLx"
},
{
"dnwqtVkrdkhlxds": "wdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
}],
"stktqtorymqrdssdltdon": {
"mqrdssdltdonds": "1hw1wk1f-xsk3-4s44-hwkg-wllg68x3fwh1",
"mqrdssdltdonLxvxllosx": {
"losx": "STkTx"
}
}
},
{
"woldlyTywxds": "qnxmwloymxntdnsqrknlx",
"woldlyOwtdonds": "Stknskrs",
"woldlydnwqts": [{
"dnwqtVkrdkhlxds": "wdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
},
{
"dnwqtVkrdkhlxds": "lxgklxntdtySqdRktx",
"dnwqtVklqx": {
"wxrlxntkgx": w.g
}
},
{
"dnwqtVkrdkhlxds": "lxgklxntdtyWdthholsdngStktqs",
"dnwqtVklqx": "SqhmxlT"
}],
"stktqtorymqrdssdltdon": {
"mqrdssdltdonds": "1hw1wk1f-xsk3-4s44-hwkg-wllg68x3fwh1",
"mqrdssdltdonLxvxllosx": {
"losx": "STkTx"
}
}
}],
"stktqtoryxldgdhdldtydnwqts": [{
"dnwqtVkrdkhlxds": "tkxRxwortdngllkssdfdlktdonlosx",
"dnwqtVklqx": "Ww"
}],
"wrdmkrylostkllolktdons": [{
"lostkllolktdonds": "sxwT1",
"kllolktdonwxrlxntkgx": {
"wxrlxntkgx": "1gg"
}
}],
"wkymxntsdstrdhqtdondnstrqltdons": [{
"dnstrqltdonds": "5g63w8gw_54",
"wrxlxsxnlxOrsxr": 1g,
"wkymxntMxthoslosx": "lHxlK",
"wkymxntsdstrdhqtdon": {
"rxmkdndnghklknlxdnsdlktor": trqx
},
"wkymxntsdshqrsxmxnt": {
"sdshqrsxmxntwkrtylosx": "ksw"
}
}]
}
CodePudding user response：
The key bit of information is the version of your database. Most posters here don't give us that information. You did; very good!
Unfortunately, in Oracle 12 (even in 12.2), json_query can only return varchar2, which is limited to 4000 bytes, or 32767 bytes if you have extended maximum string size.
Oracle itself realized that this is too limiting, and they introduced support for returning CLOB (as was illustrated in MT0's answer, and suggested by another poster in a comment to your question). Alas, that only happened later. In Oracle "12c" there is, indeed, a limit on what json_query can return - it's the general limit on varchar2 strings.
CodePudding user response：
Use RETURNING CLOB to tell the function to return a CLOB and not the default VARCHAR2 data type, which is limited to 4000 bytes:
select json_query(test_column, '$.test' RETURNING CLOB ) from test_table;
Which, for your sample data (if you correct for the invalid syntax with the unquoted string literals in the JSON), outputs:
JSON_QUERY(TEST_COLUMN,'$.TEST'RETURNINGCLOB) [{"woldlyTywxds":"Rxgqlkrwky","woldlyOwtdonds":"Monxtkrywky","lldxntOwtdondnstknlxds":"61w16gw_1","lkllqlktdonMxthosds":"Hoqrlywky","woldlydnwqts":[{"dnwqtVkrdkhlxds":"rxgqlkrHoqrsWorkxs","dnwqtVklqx":{"qqkntdty":"4g.g","qndtlosx":{"losx":"HqR"}}},{"dnwqtVkrdkhlxds":"kwwldxsHoqrlyRktx","dnwqtVklqx":{"rktx":46,"hksxqndtlosx":{"losx":"HqR"},"lqrrxnlylosx":"qSs"}},{"dnwqtVkrdkhlxds":"skysWorkxsdnmqrdssdltdon","dnwqtVklqx":{"qqkntdty":"5.g","qndtlosx":{"losx":"skY"}}},{"dnwqtVkrdkhlxds":"slhxsqlxsWorkwxrdosHoqrs","dnwqtVklqx":{"qqkntdty":"4g.g","qndtlosx":{"losx":"HqR"}}},{"dnwqtVkrdkhlxds":"slhxsqlxsWorkwxrdosskys","dnwqtVklqx":{"qqkntdty":"5.g","qndtlosx":{"losx":"skY"}}}],"workwxrdoss":[{"wxrdosds":"wgww-g1-g1Tgg:gg:gg.ggg-g4:gg","wxrdoslosx":"sxfkqlt","stkrtsktxTdmx":"wgww-g1-g1Tgg:gg:gg.ggg-g4:gg","xnssktxTdmx":"wgww-g1-g1Tgg:gg:gg.ggg-g4:gg"}]},{"woldlyTywxds":"4g1k","woldlyOwtdonds":"lkslksdnglontrdhqtdon","lldxntOwtdondnstknlxds":"5gwwg458_1","lkllqlktdonMxthosds":"kssoldktxhksxlkslksdngwxrlxntkgx","kwwldlkhdldtyOwtdons":[{"owtdonds":"FdrstwkywxrdosOfqsTkxMonth"}],"woldlydnwqts":[{"dnwqtVkrdkhlxds":"tkxkhdldtylosx","dnwqtVklqx":"ROTH"},{"dnwqtVkrdkhlxds":"kssoldktxsxfxrrklwxrlxntkgx","dnwqtVklqx":{"wxrlxntkgx":"3g.g"}},{"dnwqtVkrdkhlxds":"sxfdnxslontrdhqtdonwlknkwwldlkhlxxkrndngs","dnwqtVklqx":{"kggrxgktxSxlxltors":[{"lldxntOwtdondnstknlxds":"5g615w31_1"},{"lldxntOwtdondnstknlxds":"5g813w18_1"},{"lldxntOwtdondnstknlxds":"5gww18ww_1"},{"lldxntOwtdondnstknlxds":"13884g11_1"},{"lldxntOwtdondnstknlxds":"5gw8w3g3_1"},{"lldxntOwtdondnstknlxds":"5gw14661_1"},{"lldxntOwtdondnstknlxds":"5gww1558_1"},{"lldxntOwtdondnstknlxds":"5gww415g_1"},{"lldxntOwtdondnstknlxds":"5gww1616_1"},{"lldxntOwtdondnstknlxds":"1413881g_1"},{"lldxntOwtdondnstknlxds":"13884g1w_1"},{"lldxntOwtdondnstknlxds":"1385155w_1"},{"lldxntOwtdondnstknlxds":"13851561_1"},{"lldxntOwtdondnstknlxds":"w31156g1_1"},{"lldxntOwtdondnstknlxds":"5gw88341_1"},{"lldxntOwtdondnstknlxds":"5gww8g15_1"},{"lldxntOwtdondnstknlxds":"5gw8ww11_1"},{"lldxntOwtdondnstknlxds":"5g641433_1"},{"lldxntOwtdondnstknlxds":"511163w6_1"},{"lldxntOwtdondnstknlxds":"5gwwg513_1"},{"lldxntOwtdondnstknlxds":"w311585g_1"},{"lldxntOwtdondnstknlxds":"51g1g43w_1"},{"lldxntOwtdondnstknlxds":"5g813863_1"},{"lldxntOwtdondnstknlxds":"61w16gw_1"},{"lldxntOwtdondnstknlxds":"5g813843_1"},{"lldxntOwtdondnstknlxds":"13884ggw_1"},{"lldxntOwtdondnstknlxds":"51wg4483_1"},{"lldxntOwtdondnstknlxds":"14138851_1"},{"lldxntOwtdondnstknlxds":"5gw14486_1"},{"lldxntOwtdondnstknlxds":"51wg4488_1"},{"lldxntOwtdondnstknlxds":"5g813ww1_1"},{"lldxntOwtdondnstknlxds":"5gww1w45_1"},{"lldxntOwtdondnstknlxds":"5g813w38_1"},{"lldxntOwtdondnstknlxds":"13884g11_1"},{"lldxntOwtdondnstknlxds":"5g816w4g_1"},{"lldxntOwtdondnstknlxds":"5gw15g1w_1"},{"lldxntOwtdondnstknlxds":"5gw148g4_1"},{"lldxntOwtdondnstknlxds":"5gw14863_1"},{"lldxntOwtdondnstknlxds":"5gww181w_1"},{"lldxntOwtdondnstknlxds":"5gww8g86_1"},{"lldxntOwtdondnstknlxds":"5gw8w16w_1"},{"lldxntOwtdondnstknlxds":"5g816gg3_1"},{"lldxntOwtdondnstknlxds":"5gww1831_1"},{"lldxntOwtdondnstknlxds":"5gw14131_1"},{"lldxntOwtdondnstknlxds":"51g1gw58_1"},{"lldxntOwtdondnstknlxds":"5gw8w33w_1"},{"lldxntOwtdondnstknlxds":"51gg1131_1"},{"lldxntOwtdondnstknlxds":"51gg4g55_1"},{"lldxntOwtdondnstknlxds":"5g816g31_1"},{"lldxntOwtdondnstknlxds":"51gg4ggw_1"}]}}]},{"woldlyTywxds":"4g1k","woldlyOwtdonds":"lkslksdnglontrdhqtdon","lldxntOwtdondnstknlxds":"5gwwg458_1","lkllqlktdonMxthosds":"kssoldktxhksxlkslksdngwxrlxntkgx","kwwldlkhdldtyOwtdons":[{"owtdonds":"FdrstwkywxrdosOfqsTkxMonth"}],"woldlydnwqts":[{"dnwqtVkrdkhlxds":"tkxkhdldtylosx","dnwqtVklqx":"TRksdTdONkL"},{"dnwqtVkrdkhlxds":"kssoldktxsxfxrrklwxrlxntkgx","dnwqtVklqx":{"wxrlxntkgx":"ww.g"}},{"dnwqtVkrdkhlxds":"sxfdnxslontrdhqtdonwlknkwwldlkhlxxkrndngs","dnwqtVklqx":{"kggrxgktxSxlxltors":[{"lldxntOwtdondnstknlxds":"5g615w31_1"},{"lldxntOwtdondnstknlxds":"5g813w18_1"},{"lldxntOwtdondnstknlxds":"5gww18ww_1"},{"lldxntOwtdondnstknlxds":"13884g11_1"},{"lldxntOwtdondnstknlxds":"5gw8w3g3_1"},{"lldxntOwtdondnstknlxds":"5gw14661_1"},{"lldxntOwtdondnstknlxds":"5gww1558_1"},{"lldxntOwtdondnstknlxds":"5gww415g_1"},{"lldxntOwtdondnstknlxds":"5gww1616_1"},{"lldxntOwtdondnstknlxds":"1413881g_1"},{"lldxntOwtdondnstknlxds":"13884g1w_1"},{"lldxntOwtdondnstknlxds":"1385155w_1"},{"lldxntOwtdondnstknlxds":"13851561_1"},{"lldxntOwtdondnstknlxds":"w31156g1_1"},{"lldxntOwtdondnstknlxds":"5gw88341_1"},{"lldxntOwtdondnstknlxds":"5gww8g15_1"},{"lldxntOwtdondnstknlxds":"5gw8ww11_1"},{"lldxntOwtdondnstknlxds":"5g641433_1"},{"lldxntOwtdondnstknlxds":"511163w6_1"},{"lldxntOwtdondnstknlxds":"5gwwg513_1"},{"lldxntOwtdondnstknlxds":"w311585g_1"},{"lldxntOwtdondnstknlxds":"51g1g43w_1"},{"lldxntOwtdondnstknlxds":"5g813863_1"},{"lldxntOwtdondnstknlxds":"61w16gw_1"},{"lldxntOwtdondnstknlxds":"5g813843_1"},{"lldxntOwtdondnstknlxds":"13884ggw_1"},{"lldxntOwtdondnstknlxds":"51wg4483_1"},{"lldxntOwtdondnstknlxds":"14138851_1"},{"lldxntOwtdondnstknlxds":"5gw14486_1"},{"lldxntOwtdondnstknlxds":"51wg4488_1"},{"lldxntOwtdondnstknlxds":"5g813ww1_1"},{"lldxntOwtdondnstknlxds":"5gww1w45_1"},{"lldxntOwtdondnstknlxds":"5g813w38_1"},{"lldxntOwtdondnstknlxds":"13884g11_1"},{"lldxntOwtdondnstknlxds":"5g816w4g_1"},{"lldxntOwtdondnstknlxds":"5gw15g1w_1"},{"lldxntOwtdondnstknlxds":"5gw148g4_1"},{"lldxntOwtdondnstknlxds":"5gw14863_1"},{"lldxntOwtdondnstknlxds":"5gww181w_1"},{"lldxntOwtdondnstknlxds":"5gww8g86_1"},{"lldxntOwtdondnstknlxds":"5gw8w16w_1"},{"lldxntOwtdondnstknlxds":"5g816gg3_1"},{"lldxntOwtdondnstknlxds":"5gww1831_1"},{"lldxntOwtdondnstknlxds":"5gw14131_1"},{"lldxntOwtdondnstknlxds":"51g1gw58_1"},{"lldxntOwtdondnstknlxds":"5gw8w33w_1"},{"lldxntOwtdondnstknlxds":"51gg1131_1"},{"lldxntOwtdondnstknlxds":"51gg4g55_1"},{"lldxntOwtdondnstknlxds":"5g816g31_1"},{"lldxntOwtdondnstknlxds":"51gg4ggw_1"}]}}]},{"woldlyTywxds":"4g1k","woldlyOwtdonds":"lkslksdnglontrdhqtdon","lldxntOwtdondnstknlxds":"5gwwg458_1","lkllqlktdonMxthosds":"kssoldktxNonsxfxrrklwxrlxntkgx","kwwldlkhdldtyOwtdons":[{"owtdonds":"FdrstwkywxrdosOfqsTkxMonth"}],"woldlydnwqts":[{"dnwqtVkrdkhlxds":"kssoldktxNonsxfxrrklwxrlxntkgx","dnwqtVklqx":{"wxrlxntkgx":"31.g"}},{"dnwqtVkrdkhlxds":"sxfdnxslontrdhqtdonwlknkwwldlkhlxxkrndngs","dnwqtVklqx":{"kggrxgktxSxlxltors":[{"lldxntOwtdondnstknlxds":"5g615w31_1"},{"lldxntOwtdondnstknlxds":"5g813w18_1"},{"lldxntOwtdondnstknlxds":"5gww18ww_1"},{"lldxntOwtdondnstknlxds":"13884g11_1"},{"lldxntOwtdondnstknlxds":"5gw8w3g3_1"},{"lldxntOwtdondnstknlxds":"5gw14661_1"},{"lldxntOwtdondnstknlxds":"5gww1558_1"},{"lldxntOwtdondnstknlxds":"5gww415g_1"},{"lldxntOwtdondnstknlxds":"5gww1616_1"},{"lldxntOwtdondnstknlxds":"1413881g_1"},{"lldxntOwtdondnstknlxds":"13884g1w_1"},{"lldxntOwtdondnstknlxds":"1385155w_1"},{"lldxntOwtdondnstknlxds":"13851561_1"},{"lldxntOwtdondnstknlxds":"w31156g1_1"},{"lldxntOwtdondnstknlxds":"5gw88341_1"},{"lldxntOwtdondnstknlxds":"5gww8g15_1"},{"lldxntOwtdondnstknlxds":"5gw8ww11_1"},{"lldxntOwtdondnstknlxds":"5g641433_1"},{"lldxntOwtdondnstknlxds":"511163w6_1"},{"lldxntOwtdondnstknlxds":"5gwwg513_1"},{"lldxntOwtdondnstknlxds":"w311585g_1"},{"lldxntOwtdondnstknlxds":"51g1g43w_1"},{"lldxntOwtdondnstknlxds":"5g813863_1"},{"lldxntOwtdondnstknlxds":"61w16gw_1"},{"lldxntOwtdondnstknlxds":"5g813843_1"},{"lldxntOwtdondnstknlxds":"13884ggw_1"},{"lldxntOwtdondnstknlxds":"51wg4483_1"},{"lldxntOwtdondnstknlxds":"14138851_1"},{"lldxntOwtdondnstknlxds":"5gw14486_1"},{"lldxntOwtdondnstknlxds":"51wg4488_1"},{"lldxntOwtdondnstknlxds":"5g813ww1_1"},{"lldxntOwtdondnstknlxds":"5gww1w45_1"},{"lldxntOwtdondnstknlxds":"5g813w38_1"},{"lldxntOwtdondnstknlxds":"13884g11_1"},{"lldxntOwtdondnstknlxds":"5g816w4g_1"},{"lldxntOwtdondnstknlxds":"5gw15g1w_1"},{"lldxntOwtdondnstknlxds":"5gw148g4_1"},{"lldxntOwtdondnstknlxds":"5gw14863_1"},{"lldxntOwtdondnstknlxds":"5gww181w_1"},{"lldxntOwtdondnstknlxds":"5gww8g86_1"},{"lldxntOwtdondnstknlxds":"5gw8w16w_1"},{"lldxntOwtdondnstknlxds":"5g816gg3_1"},{"lldxntOwtdondnstknlxds":"5gww1831_1"},{"lldxntOwtdondnstknlxds":"5gw14131_1"},{"lldxntOwtdondnstknlxds":"51g1gw58_1"},{"lldxntOwtdondnstknlxds":"5gw8w33w_1"},{"lldxntOwtdondnstknlxds":"51gg1131_1"},{"lldxntOwtdondnstknlxds":"51gg4g55_1"},{"lldxntOwtdondnstknlxds":"5g816g31_1"},{"lldxntOwtdondnstknlxds":"51gg4ggw_1"}]}}]},{"woldlyTywxds":"4g1k","woldlyOwtdonds":"lkslksdnglontrdhqtdon","lldxntOwtdondnstknlxds":"5gwwg458_1","lkllqlktdonMxthosds":"OrgkndzktdonwrofdtShkrxlontrdhqtdon","kwwldlkhdldtyOwtdons":[{"owtdonds":"FdrstwkywxrdosOfqsTkxMonth"}],"woldlydnwqts":[{"dnwqtVkrdkhlxds":"orgkndzktdonlontrdhqtdonkmoqnt","dnwqtVklqx":{"kmoqnt":"1g","lqrrxnlylosx":"qSs"}}]}]
db<>fiddle here