Home > Software design >  Does JSON_QUERY have a data limit in oracle 12c
Does JSON_QUERY have a data limit in oracle 12c

Time:08-24

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.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_QUERY.html#GUID-6D396EC4-D2AA-43D2-8F5D-08D646A4A2D9

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

  • Related