Skip to Main Content
  • Questions

  • JSON Simple Dot-Notation Access Returning Null

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, pawan.

Asked: February 08, 2019 - 3:40 pm UTC

Last updated: February 11, 2019 - 2:35 pm UTC

Version: Oracle 12 C

Viewed 1000+ times

You Asked

Hi All,

I am having an issue to retrieve data from JSON column based on key. Find below the json. I already validate the JSON. I didn't find any issue with the JSON.

"{
 "Test": "123.40.4",
 "allowedtables": [{
  "name": "t",
  "attributes": {
   "l": "A"
  },
  "groups": [{
   "name": "grp",
   "attributes": {
    "n": "AI"
   },
   "categories": [{
    "name": "cat",
    "attributes": {
     "n": "50"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "AO"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "536"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "60"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "3"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "70"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "DT"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "2017-12-31"
     }]
    }]
   }]
  },
  {
   "name": "grp",
   "attributes": {
    "n": "CL"
   },
   "categories": [{
    "name": "cat",
    "attributes": {
     "n": "100"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "110"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "120"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "0"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "130"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "140"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "150"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "160"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "3"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "170"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "180"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "190"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "2"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "200"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "C"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "1"
     }]
    }]
   }]
  },
  {
   "name": "grp",
   "attributes": {
    "n": "ID"
   },
   "categories": [{
    "name": "cat",
    "attributes": {
     "n": "10"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "H"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "102"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "R"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "861"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "S"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "17"
     }]
    }]
   }]
  },
  {
   "name": "grp",
   "attributes": {
    "n": "LO"
   },
   "categories": [{
    "name": "cat",
    "attributes": {
     "n": "20"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "DG"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "43"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "MI"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "27"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "30"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "DG"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "16"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "MI"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "42"
     }]
    }]
   },
   {
    "name": "cat",
    "attributes": {
     "n": "40"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "N"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "HR035"
     }]
    }]
   }]
  },
  {
   "name": "grp",
   "attributes": {
    "n": "Test"
   },
   "categories": [{
    "name": "cat",
    "attributes": {
     "n": "10"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "H"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "102"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "R"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "861"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "S"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "17"
     }]
    }]
   }]
  },
  {
   "name": "grp",
   "attributes": {
    "n": "Test"
   },
   "categories": [{
    "name": "cat",
    "attributes": {
     "n": "10"
    },
    "columns": [{
     "name": "col",
     "attributes": {
      "n": "H"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "102"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "R"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "861"
     }]
    },
    {
     "name": "col",
     "attributes": {
      "n": "S"
     },
     "values": [{
      "value": "col_value",
      "attributes": null,
      "data": "17"
     }]
    }]
   }]
  }]
 }]
}"

select getJSON.ColumnName.allowedtables from R1_TESTJSON getJSON
where getJSON.ColumnName.Test = '123.40.4';

select getJSON.ColumnName.allowedtables[*] from R1_TESTJSON getJSON
where getJSON.ColumnName.Test = '123.40.4';


JSON field in oracle table is CLOB type.

both queries return a null value. Any help would be highly appreciated.

Regards
Pawan


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/hx13lme2h9kayqv48xw0h4ng7

and Chris said...

Eee, that's a large JSON document!

Which is the source of your problem. Dot-notation always returns a varchar2(4000). The document you're returning is bigger than that!

Get around this by using json_query and specifying a larger varchar2:

select length ( 
         json_query (
           TestJSON, '$.allowedtables'
             returning varchar2(32767)
             error on error
         )
       ) l ,
       json_query (
         TestJSON, '$.allowedtables'
           returning varchar2(32767)
           error on error
       ) doc
from   R1_TESTJSON getJSON 
where getJSON.TestJSON.Test = '121.00.101';

L      DOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          
  4247 [{"name":"t","attributes":{"l":"A"},"groups":[ ...


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.