Search code examples
sqljsonoracle-databaseclob

Accessing JSON inside CLOB column

I'm trying to access the columns stored inside this CLOB of JSON. However, because it is not version 12C of Oracle I cannot use dot notation to reference the column names like "table.column"

I am really struggling. I have tried to use dbms_lob.substr to extract it but i just end up getting the full CLOB.

My screenshot attached is displayed when running the following : SELECT * FROM TRANSFORM_OB_BB_SIT_OWNER.BUCKETS WHERE bucket_name ='LatestApplicationVersions'

However, I want to be able to access 'PersonalCountryOfNationality' where it is = 'United Kingdom' enter image description here

Solution

  • If you want to work with JSON with a version older than 12c, I recommend using the PLJSON package, here is a link:

    https://github.com/pljson/pljson/tree/develop

    You can find exemples here:

    https://github.com/pljson/pljson/tree/develop/examples