{"id":13804,"date":"2025-01-28T17:38:55","date_gmt":"2025-01-28T16:38:55","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=13804"},"modified":"2025-01-28T17:39:04","modified_gmt":"2025-01-28T16:39:04","slug":"avoid-implicit-type-conversion-in-json-access","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/","title":{"rendered":"Avoid Implicit Type Conversion in JSON Access"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

Before comparing two values, the Oracle Database automatically ensures that both values have the same data type. It converts one of the values to match the data type of the other value. The SQL Language Reference manual<\/a> describes when and how implicit data conversions happen. However, Oracle recommends that you convert data types explicitly. This ensures consistent results and better performance.<\/p>\n\n\n\n

I am using JSON relational duality views in my current project. During development, I have stumbled across some implicit data conversions that I was unaware of and that are causing poor performance. I will use a simplified example to show you what I mean.<\/p>\n\n\n\n

    \n
  1. Setup<\/a><\/li>\n\n\n\n
  2. JSON-relational Duality View<\/a><\/li>\n\n\n\n
  3. JSON Data Types<\/a><\/li>\n\n\n\n
  4. Explicit Conversion<\/a><\/li>\n\n\n\n
  5. JSON Collection View<\/a><\/li>\n\n\n\n
  6. Conclusion<\/a><\/li>\n<\/ol>\n\n\n\n

    1. Setup<\/h2>\n\n\n\n

    I’ve tested this example with an Oracle Database 23.6 and 23.7. <\/p>\n\n\n\n

    In a schema of your choice you can run the following setup script:<\/p>\n\n\n\n

    1) Setup table dept and emp<\/span><\/path><\/path><\/svg><\/span>
    drop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> emp;<\/span><\/span>\ndrop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> dept;<\/span><\/span>\n<\/span>\ncreate<\/span> <\/span>table<\/span> dept (<\/span><\/span>\n   deptno <\/span>number<\/span>(<\/span>2<\/span>, <\/span>0<\/span>)      <\/span>not<\/span> <\/span>null<\/span> <\/span>constraint<\/span> dept_pk <\/span>primary key<\/span>,<\/span><\/span>\n   dname  <\/span>varchar2<\/span>(<\/span>14<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   loc    <\/span>varchar2<\/span>(<\/span>13<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ncreate<\/span> <\/span>table<\/span> emp (<\/span><\/span>\n   empno    <\/span>number<\/span>(<\/span>4<\/span>, <\/span>0<\/span>)      <\/span>not<\/span> <\/span>null<\/span>  <\/span>constraint<\/span> emp_pk <\/span>primary key<\/span>,<\/span><\/span>\n   ename    <\/span>varchar2<\/span>(<\/span>10<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   job      <\/span>varchar2<\/span>(<\/span>9<\/span> <\/span>char<\/span>)  <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   mgr      <\/span>number<\/span>(<\/span>4<\/span>, <\/span>0<\/span>)                <\/span>constraint<\/span> emp_mgr_fk <\/span>references<\/span> emp,<\/span><\/span>\n   hiredate <\/span>date<\/span>              <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   sal      <\/span>number<\/span>(<\/span>7<\/span>, <\/span>2<\/span>)      <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   comm     <\/span>number<\/span>(<\/span>7<\/span>, <\/span>2<\/span>),<\/span><\/span>\n   deptno   <\/span>number<\/span>(<\/span>2<\/span>, <\/span>0<\/span>)      <\/span>not<\/span> <\/span>null<\/span>  <\/span>constraint<\/span> emp_deptno_fk <\/span>references<\/span> dept<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ninsert<\/span> <\/span>into<\/span> dept (deptno, dname, loc)<\/span><\/span>\nvalues<\/span> (<\/span>10<\/span>, <\/span>'ACCOUNTING'<\/span>, <\/span>'NEW YORK'<\/span>),<\/span><\/span>\n       (<\/span>20<\/span>, <\/span>'RESEARCH'<\/span>,   <\/span>'DALLAS'<\/span>),<\/span><\/span>\n       (<\/span>30<\/span>, <\/span>'SALES'<\/span>,      <\/span>'CHICAGO'<\/span>),<\/span><\/span>\n       (<\/span>40<\/span>, <\/span>'OPERATIONS'<\/span>, <\/span>'BOSTON'<\/span>);<\/span><\/span>\n<\/span>\ninsert<\/span> <\/span>into<\/span> emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)<\/span><\/span>\nvalues<\/span> (<\/span>7566<\/span>, <\/span>'JONES'<\/span>,  <\/span>'MANAGER'<\/span>,   <\/span>7839<\/span>, <\/span>date<\/span> <\/span>'1981-04-02'<\/span>, <\/span>2975<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7698<\/span>, <\/span>'BLAKE'<\/span>,  <\/span>'MANAGER'<\/span>,   <\/span>7839<\/span>, <\/span>date<\/span> <\/span>'1981-05-01'<\/span>, <\/span>2850<\/span>, <\/span>null<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7782<\/span>, <\/span>'CLARK'<\/span>,  <\/span>'MANAGER'<\/span>,   <\/span>7839<\/span>, <\/span>date<\/span> <\/span>'1981-06-09'<\/span>, <\/span>2450<\/span>, <\/span>null<\/span>, <\/span>10<\/span>),<\/span><\/span>\n       (<\/span>7788<\/span>, <\/span>'SCOTT'<\/span>,  <\/span>'ANALYST'<\/span>,   <\/span>7566<\/span>, <\/span>date<\/span> <\/span>'1987-04-19'<\/span>, <\/span>3000<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7902<\/span>, <\/span>'FORD'<\/span>,   <\/span>'ANALYST'<\/span>,   <\/span>7566<\/span>, <\/span>date<\/span> <\/span>'1981-12-03'<\/span>, <\/span>3000<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7499<\/span>, <\/span>'ALLEN'<\/span>,  <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-02-20'<\/span>, <\/span>1600<\/span>,  <\/span>300<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7521<\/span>, <\/span>'WARD'<\/span>,   <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-02-22'<\/span>, <\/span>1250<\/span>,  <\/span>500<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7654<\/span>, <\/span>'MARTIN'<\/span>, <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-09-28'<\/span>, <\/span>1250<\/span>, <\/span>1400<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7844<\/span>, <\/span>'TURNER'<\/span>, <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-09-08'<\/span>, <\/span>1500<\/span>,    <\/span>0<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7900<\/span>, <\/span>'JAMES'<\/span>,  <\/span>'CLERK'<\/span>,     <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-12-03'<\/span>,  <\/span>950<\/span>, <\/span>null<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7934<\/span>, <\/span>'MILLER'<\/span>, <\/span>'CLERK'<\/span>,     <\/span>7782<\/span>, <\/span>date<\/span> <\/span>'1982-01-23'<\/span>, <\/span>1300<\/span>, <\/span>null<\/span>, <\/span>10<\/span>),<\/span><\/span>\n       (<\/span>7369<\/span>, <\/span>'SMITH'<\/span>,  <\/span>'CLERK'<\/span>,     <\/span>7902<\/span>, <\/span>date<\/span> <\/span>'1980-12-17'<\/span>,  <\/span>800<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7839<\/span>, <\/span>'KING'<\/span>,   <\/span>'PRESIDENT'<\/span>, <\/span>null<\/span>, <\/span>date<\/span> <\/span>'1981-11-17'<\/span>, <\/span>5000<\/span>, <\/span>null<\/span>, <\/span>10<\/span>),<\/span><\/span>\n       (<\/span>7876<\/span>, <\/span>'ADAMS'<\/span>,  <\/span>'CLERK'<\/span>,     <\/span>7788<\/span>, <\/span>date<\/span> <\/span>'1987-05-23'<\/span>, <\/span>1100<\/span>, <\/span>null<\/span>, <\/span>20<\/span>);<\/span><\/span>\n<\/span>\nbegin<\/span><\/span>\n   <\/span>dbms_stats.<\/span>gather_table_stats<\/span>(<\/span>user<\/span>, <\/span>'dept'<\/span>);<\/span><\/span>\n   <\/span>dbms_stats.<\/span>gather_table_stats<\/span>(<\/span>user<\/span>, <\/span>'emp'<\/span>);<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nalter session<\/span> <\/span>set<\/span> nls_date_format = <\/span>'YYYY-MM-DD'<\/span>;<\/span><\/span>\ncolumn<\/span> deptno format a6<\/span><\/span>\ncolumn<\/span> dname format a10<\/span><\/span>\ncolumn<\/span> loc format a9<\/span><\/span>\ncolumn<\/span> empid format a8<\/span><\/span>\ncolumn<\/span> empno format a5<\/span><\/span>\ncolumn<\/span> ename format a6<\/span><\/span>\ncolumn<\/span> mgr format a4<\/span><\/span>\ncolumn<\/span> sal format a4<\/span><\/span>\ncolumn<\/span> comm format a4<\/span><\/span>\n<\/span>\nselect<\/span> * <\/span>from<\/span> dept;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> emp;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    Table EMP dropped.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable DEPT dropped.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable DEPT created.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable EMP created.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\n4 rows inserted.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\n14 rows inserted.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nPL\/SQL procedure successfully completed.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nSession altered.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nDEPTNO DNAME      LOC      <\/span><\/span>\n------ ---------- ---------<\/span><\/span>\n    10 ACCOUNTING NEW YORK <\/span><\/span>\n    20 RESEARCH   DALLAS   <\/span><\/span>\n    30 SALES      CHICAGO  <\/span><\/span>\n    40 OPERATIONS BOSTON   <\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nEMPNO ENAME  JOB        MGR HIREDATE    SAL COMM DEPTNO<\/span><\/span>\n----- ------ --------- ---- ---------- ---- ---- ------<\/span><\/span>\n 7566 JONES  MANAGER   7839 1981-04-02 2975          20<\/span><\/span>\n 7698 BLAKE  MANAGER   7839 1981-05-01 2850          30<\/span><\/span>\n 7782 CLARK  MANAGER   7839 1981-06-09 2450          10<\/span><\/span>\n 7788 SCOTT  ANALYST   7566 1987-04-19 3000          20<\/span><\/span>\n 7902 FORD   ANALYST   7566 1981-12-03 3000          20<\/span><\/span>\n 7499 ALLEN  SALESMAN  7698 1981-02-20 1600  300     30<\/span><\/span>\n 7521 WARD   SALESMAN  7698 1981-02-22 1250  500     30<\/span><\/span>\n 7654 MARTIN SALESMAN  7698 1981-09-28 1250 1400     30<\/span><\/span>\n 7844 TURNER SALESMAN  7698 1981-09-08 1500    0     30<\/span><\/span>\n 7900 JAMES  CLERK     7698 1981-12-03  950          30<\/span><\/span>\n 7934 MILLER CLERK     7782 1982-01-23 1300          10<\/span><\/span>\n 7369 SMITH  CLERK     7902 1980-12-17  800          20<\/span><\/span>\n 7839 KING   PRESIDENT      1981-11-17 5000          10<\/span><\/span>\n 7876 ADAMS  CLERK     7788 1987-05-23 1100          20<\/span><\/span>\n<\/span><\/span>\n14 rows selected. <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The model has primary and foreign keys which are required for the duality views. However, the constraints do not need to be enabled.<\/p>\n\n\n\n
    2. JSON-relational Duality View<\/h2>\n\n\n\n
    Now let’s create an updateable duality view for the model we created earlier.<\/p>\n\n\n\n
    2) Create duality view dept_dv<\/span><\/path><\/path><\/svg><\/span>
    create or replace<\/span> json duality <\/span>view<\/span> dept_dv <\/span>as<\/span><\/span>\ndept @<\/span>insert<\/span> @<\/span>update<\/span> @<\/span>delete<\/span><\/span>\n{<\/span><\/span>\n   _id: deptno<\/span><\/span>\n   dname<\/span><\/span>\n   loc<\/span><\/span>\n   emps: emp @<\/span>insert<\/span> @<\/span>update<\/span> @<\/span>delete<\/span><\/span>\n      {<\/span><\/span>\n         empno<\/span><\/span>\n         ename<\/span><\/span>\n         job<\/span><\/span>\n         emp @unnest @link(<\/span>from<\/span>: [mgr])<\/span><\/span>\n            {<\/span><\/span>\n               mgr    : empno @nocheck<\/span><\/span>\n               mgrname: ename @nocheck<\/span><\/span>\n            }<\/span><\/span>\n         hiredate<\/span><\/span>\n         sal<\/span><\/span>\n         comm<\/span><\/span>\n      }<\/span><\/span>\n};<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    Json duality view DEPT_DV created.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    We can query the department 10<\/code> as follows:<\/p>\n\n\n\n
    3) Query deptno 10 in dept_dv<\/span><\/path><\/path><\/svg><\/span>
    column<\/span> json_data format a50<\/span><\/span>\nalter session<\/span> disable parallel query;<\/span><\/span>\nset<\/span> pagesize <\/span>1000<\/span><\/span>\n<\/span>\nselect<\/span> json_serialize(dv.data <\/span>returning<\/span> <\/span>varchar2<\/span> pretty) <\/span>as<\/span> json_data<\/span><\/span>\n  <\/span>from<\/span> dept_dv dv<\/span><\/span>\n <\/span>where<\/span> dv.data.<\/span>"_id"<\/span> = <\/span>10<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    Session altered.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nJSON_DATA<\/span><\/span>\n--------------------------------------------------<\/span><\/span>\n{<\/span><\/span>\n  "_id" : 10,<\/span><\/span>\n  "_metadata" :<\/span><\/span>\n  {<\/span><\/span>\n    "etag" : "E0146035FE26EE16D4968A21E6350D81",<\/span><\/span>\n    "asof" : "00002604AFC8A4BC"<\/span><\/span>\n  },<\/span><\/span>\n  "dname" : "ACCOUNTING",<\/span><\/span>\n  "loc" : "NEW YORK",<\/span><\/span>\n  "emps" :<\/span><\/span>\n  [<\/span><\/span>\n    {<\/span><\/span>\n      "empno" : 7782,<\/span><\/span>\n      "ename" : "CLARK",<\/span><\/span>\n      "job" : "MANAGER",<\/span><\/span>\n      "mgr" : 7839,<\/span><\/span>\n      "mgrname" : "KING",<\/span><\/span>\n      "hiredate" : "1981-06-09T00:00:00",<\/span><\/span>\n      "sal" : 2450,<\/span><\/span>\n      "comm" : null<\/span><\/span>\n    },<\/span><\/span>\n    {<\/span><\/span>\n      "empno" : 7839,<\/span><\/span>\n      "ename" : "KING",<\/span><\/span>\n      "job" : "PRESIDENT",<\/span><\/span>\n      "hiredate" : "1981-11-17T00:00:00",<\/span><\/span>\n      "sal" : 5000,<\/span><\/span>\n      "comm" : null<\/span><\/span>\n    },<\/span><\/span>\n    {<\/span><\/span>\n      "empno" : 7934,<\/span><\/span>\n      "ename" : "MILLER",<\/span><\/span>\n      "job" : "CLERK",<\/span><\/span>\n      "mgr" : 7782,<\/span><\/span>\n      "mgrname" : "CLARK",<\/span><\/span>\n      "hiredate" : "1982-01-23T00:00:00",<\/span><\/span>\n      "sal" : 1300,<\/span><\/span>\n      "comm" : null<\/span><\/span>\n    }<\/span><\/span>\n  ]<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The where clause in line 7 contains an implicit conversion. It’s not that obvious and the execution plan does not help to spot it either.<\/p>\n\n\n\n
    4) Execution plan<\/span><\/path><\/path><\/svg><\/span>
    PLAN_TABLE_OUTPUT<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\nSQL_ID  8nns7xgu0v6vu, child number 4<\/span><\/span>\n-------------------------------------<\/span><\/span>\nselect json_serialize(dv.data returning varchar2 pretty) as json_data   <\/span><\/span>\nfrom dept_dv dv  where dv.data."_id" = 10<\/span><\/span>\n <\/span><\/span>\nPlan hash value: 2166484641<\/span><\/span>\n <\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n|   0 | SELECT STATEMENT            |         |       |       |     7 (100)|          |<\/span><\/span>\n|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    10 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  2 |   INDEX UNIQUE SCAN         | EMP_PK  |     1 |       |     0   (0)|          |<\/span><\/span>\n|   3 |  SORT GROUP BY              |         |     1 |    38 |            |          |<\/span><\/span>\n|*  4 |   TABLE ACCESS FULL         | EMP     |     5 |   190 |     3   (0)| 00:00:01 |<\/span><\/span>\n|   5 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  6 |   INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)|          |<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nPredicate Information (identified by operation id):<\/span><\/span>\n---------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   2 - access("OUTER_ALIAS2"."EMPNO"=:B1)<\/span><\/span>\n   4 - filter("OUTER_ALIAS1"."DEPTNO"=:B1)<\/span><\/span>\n   6 - access("DEPTNO"=10)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The highlighted lines show that a unique index scan has been performed for deptno 10<\/code>. This looks good. So where does the implicit conversion take place?<\/p>\n\n\n\n
    3. JSON Data Types<\/h2>\n\n\n\n
    The condition dv.data.\"_id\" = 10<\/code> in the where clause compares a JSON data type with a numeric data type. As a result, the Oracle Database needs to convert one of the values. It decided to convert the JSON to a numeric value. That’s what we see in the execution plan.<\/p>\n\n\n\n
    Really? Can we prove this claim? Yes, with the following statement.<\/p>\n\n\n\n
    5) Determine JSON data type<\/span><\/path><\/path><\/svg><\/span>
    select<\/span> is_json_condition, value<\/span><\/span>\n  <\/span>from<\/span> (<\/span><\/span>\n          <\/span>select<\/span> deptno <\/span>is<\/span> json <\/span>as<\/span> is_json,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(value) <\/span>as<\/span> is_json_value,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(array) <\/span>as<\/span> is_json_array,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(object) <\/span>as<\/span> is_json_object,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar) <\/span>as<\/span> is_json_scalar,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>number<\/span>) <\/span>as<\/span> is_json_scalar_number,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar string) <\/span>as<\/span> is_json_scalar_string,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>binary_double<\/span>) <\/span>as<\/span> is_json_scalar_binary_double,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>binary_float<\/span>) <\/span>as<\/span> is_json_scalar_binary_float,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>date<\/span>) <\/span>as<\/span> is_json_scalar_date,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>timestamp<\/span>) <\/span>as<\/span> is_json_scalar_timestamp,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>timestamp with time zone<\/span>) <\/span>as<\/span> is_json_scalar_timestamp_with_time_zone,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>null<\/span>) <\/span>as<\/span> is_json_scalar_null,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar <\/span>boolean<\/span>) <\/span>as<\/span> is_json_scalar_boolean,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar binary) <\/span>as<\/span> is_json_scalar_binary,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar interval year to month) <\/span>as<\/span> is_json_scalar_interval_year_to_month,<\/span><\/span>\n                 deptno <\/span>is<\/span> json(scalar interval day to second) <\/span>as<\/span> is_json_scalar_interval_day_to_second<\/span><\/span>\n            <\/span>from<\/span> (<\/span>select<\/span> dv.data.<\/span>"_id"<\/span> <\/span>as<\/span> deptno <\/span>from<\/span> dept_dv dv <\/span>where<\/span> <\/span>rownum<\/span> = <\/span>1<\/span>)<\/span><\/span>\n       ) src <\/span>unpivot<\/span> (<\/span><\/span>\n          value <\/span>for<\/span> is_json_condition <\/span>in<\/span> (<\/span><\/span>\n             is_json,<\/span><\/span>\n             is_json_value,<\/span><\/span>\n             is_json_array,<\/span><\/span>\n             is_json_object,<\/span><\/span>\n             is_json_scalar,<\/span><\/span>\n             is_json_scalar_number,<\/span><\/span>\n             is_json_scalar_string,<\/span><\/span>\n             is_json_scalar_binary_double,<\/span><\/span>\n             is_json_scalar_binary_float,<\/span><\/span>\n             is_json_scalar_date,<\/span><\/span>\n             is_json_scalar_timestamp,<\/span><\/span>\n             is_json_scalar_timestamp_with_time_zone,<\/span><\/span>\n             is_json_scalar_null,<\/span><\/span>\n             is_json_scalar_boolean,<\/span><\/span>\n             is_json_scalar_binary,<\/span><\/span>\n             is_json_scalar_interval_year_to_month,<\/span><\/span>\n             is_json_scalar_interval_day_to_second<\/span><\/span>\n          )<\/span><\/span>\n       );<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    IS_JSON_CONDITION                       VALUE<\/span><\/span>\n--------------------------------------- -----<\/span><\/span>\nIS_JSON                                 true <\/span><\/span>\nIS_JSON_VALUE                           true <\/span><\/span>\nIS_JSON_ARRAY                           false<\/span><\/span>\nIS_JSON_OBJECT                          false<\/span><\/span>\nIS_JSON_SCALAR                          true <\/span><\/span>\nIS_JSON_SCALAR_NUMBER                   true <\/span><\/span>\nIS_JSON_SCALAR_STRING                   false<\/span><\/span>\nIS_JSON_SCALAR_BINARY_DOUBLE            false<\/span><\/span>\nIS_JSON_SCALAR_BINARY_FLOAT             false<\/span><\/span>\nIS_JSON_SCALAR_DATE                     false<\/span><\/span>\nIS_JSON_SCALAR_TIMESTAMP                false<\/span><\/span>\nIS_JSON_SCALAR_TIMESTAMP_WITH_TIME_ZONE false<\/span><\/span>\nIS_JSON_SCALAR_NULL                     false<\/span><\/span>\nIS_JSON_SCALAR_BOOLEAN                  false<\/span><\/span>\nIS_JSON_SCALAR_BINARY                   false<\/span><\/span>\nIS_JSON_SCALAR_INTERVAL_YEAR_TO_MONTH   false<\/span><\/span>\nIS_JSON_SCALAR_INTERVAL_DAY_TO_SECOND   false<\/span><\/span>\n<\/span><\/span>\n17 rows selected. <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    In line 20 we query dv.data.\"_id\"<\/code> and use the IS JSON condition<\/a> to determine the data type. The most granular type is JSON scalar number. <\/p>\n\n\n\n
    4. Explicit Conversion<\/h2>\n\n\n\n
    So, we know now, that we need to convert a JSON scalar number to a number. And how do we do that? – By using a SQL\/JSON path expression method<\/a>, as in the next example.<\/p>\n\n\n\n
    7) Query deptno 10 in dept_dv with explicit type conversion<\/span><\/path><\/path><\/svg><\/span>
    select<\/span> json_serialize(dv.data <\/span>returning<\/span> <\/span>varchar2<\/span> pretty) <\/span>as<\/span> json_data<\/span><\/span>\n  <\/span>from<\/span> dept_dv dv<\/span><\/span>\n <\/span>where<\/span> dv.data.<\/span>"_id"<\/span>.<\/span>number<\/span>() = <\/span>10<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    We used the .number()<\/code> method in this example. The execution plan for this query looks now like this:<\/p>\n\n\n\n
    8) Execution plan querying dept_dv with explicit type conversion<\/span><\/path><\/path><\/svg><\/span>
    PLAN_TABLE_OUTPUT<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\nSQL_ID  4rjg8g02bph16, child number 2<\/span><\/span>\n-------------------------------------<\/span><\/span>\nselect json_serialize(dv.data returning varchar2 pretty) as json_data   <\/span><\/span>\nfrom dept_dv dv  where dv.data."_id".number() = 10<\/span><\/span>\n <\/span><\/span>\nPlan hash value: 2166484641<\/span><\/span>\n <\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n|   0 | SELECT STATEMENT            |         |       |       |     7 (100)|          |<\/span><\/span>\n|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    10 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  2 |   INDEX UNIQUE SCAN         | EMP_PK  |     1 |       |     0   (0)|          |<\/span><\/span>\n|   3 |  SORT GROUP BY              |         |     1 |    38 |            |          |<\/span><\/span>\n|*  4 |   TABLE ACCESS FULL         | EMP     |     5 |   190 |     3   (0)| 00:00:01 |<\/span><\/span>\n|   5 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  6 |   INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)|          |<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nPredicate Information (identified by operation id):<\/span><\/span>\n---------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   2 - access("OUTER_ALIAS2"."EMPNO"=:B1)<\/span><\/span>\n   4 - filter("OUTER_ALIAS1"."DEPTNO"=:B1)<\/span><\/span>\n   6 - access("DEPTNO"=10)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The plan looks the same as without calling the .number()<\/code> method, but in this case, we did not rely on implicit type conversion.<\/p>\n\n\n\n
    We’ve proven, that a .number()<\/code> call does not make things worse. But are there cases where such an explicit type conversion results in a better execution plan?<\/p>\n\n\n\n
    5. JSON Collection View<\/h2>\n\n\n\n
    Let’s say we need a view that filters the data in the duality view. For example, for security purposes. One way to achieve this is to create a JSON collection view. From a consumer point of view, such a view behaves 100% the same as a duality view. The only difference is that it is read-only and allows the full SQL grammar to define such a view.<\/p>\n\n\n\n
    Here’s an example.<\/p>\n\n\n\n
    9) Create JSON collection view dept_cv<\/span><\/path><\/path><\/svg><\/span>
    create or replace<\/span> json collection <\/span>view<\/span> dept_cv <\/span>as<\/span><\/span>\nselect<\/span> dv.data<\/span><\/span>\n  <\/span>from<\/span> dept_dv dv<\/span><\/span>\n <\/span>where<\/span> dv.data.<\/span>"_id"<\/span>.<\/span>number<\/span>() = <\/span>10<\/span> <\/span>or<\/span> dv.data.loc.string() = <\/span>'DALLAS'<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    Json collection view DEPT_CV created.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Now, let’s run a query with implicit type conversion.<\/p>\n\n\n\n
    10) Query deptno 10 in dept_cv<\/span><\/path><\/path><\/svg><\/span>
    column<\/span> json_data format a50<\/span><\/span>\nalter session<\/span> disable parallel query;<\/span><\/span>\nset<\/span> pagesize <\/span>1000<\/span><\/span>\n<\/span>\nselect<\/span> json_serialize(cv.data <\/span>returning<\/span> <\/span>varchar2<\/span> pretty) <\/span>as<\/span> json_data<\/span><\/span>\n  <\/span>from<\/span> dept_cv cv<\/span><\/span>\n <\/span>where<\/span> cv.data.<\/span>"_id"<\/span> = <\/span>10<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    Session altered.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nJSON_DATA<\/span><\/span>\n--------------------------------------------------<\/span><\/span>\n{<\/span><\/span>\n  "_id" : 10,<\/span><\/span>\n  "_metadata" :<\/span><\/span>\n  {<\/span><\/span>\n    "etag" : "E0146035FE26EE16D4968A21E6350D81",<\/span><\/span>\n    "asof" : "00002604B0C08AAE"<\/span><\/span>\n  },<\/span><\/span>\n  "dname" : "ACCOUNTING",<\/span><\/span>\n  "loc" : "NEW YORK",<\/span><\/span>\n  "emps" :<\/span><\/span>\n  [<\/span><\/span>\n    {<\/span><\/span>\n      "empno" : 7782,<\/span><\/span>\n      "ename" : "CLARK",<\/span><\/span>\n      "job" : "MANAGER",<\/span><\/span>\n      "mgr" : 7839,<\/span><\/span>\n      "mgrname" : "KING",<\/span><\/span>\n      "hiredate" : "1981-06-09T00:00:00",<\/span><\/span>\n      "sal" : 2450,<\/span><\/span>\n      "comm" : null<\/span><\/span>\n    },<\/span><\/span>\n    {<\/span><\/span>\n      "empno" : 7839,<\/span><\/span>\n      "ename" : "KING",<\/span><\/span>\n      "job" : "PRESIDENT",<\/span><\/span>\n      "hiredate" : "1981-11-17T00:00:00",<\/span><\/span>\n      "sal" : 5000,<\/span><\/span>\n      "comm" : null<\/span><\/span>\n    },<\/span><\/span>\n    {<\/span><\/span>\n      "empno" : 7934,<\/span><\/span>\n      "ename" : "MILLER",<\/span><\/span>\n      "job" : "CLERK",<\/span><\/span>\n      "mgr" : 7782,<\/span><\/span>\n      "mgrname" : "CLARK",<\/span><\/span>\n      "hiredate" : "1982-01-23T00:00:00",<\/span><\/span>\n      "sal" : 1300,<\/span><\/span>\n      "comm" : null<\/span><\/span>\n    }<\/span><\/span>\n  ]<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The execution plan of the query above looks like this:<\/p>\n\n\n\n
    11) Execution plan querying dept_cv with implicit type conversion<\/span><\/path><\/path><\/svg><\/span>
    PLAN_TABLE_OUTPUT<\/span><\/span>\n--------------------------------------------------------------------------------------<\/span><\/span>\nSQL_ID  gkhradvxjrwfq, child number 4<\/span><\/span>\n-------------------------------------<\/span><\/span>\nselect json_serialize(cv.data returning varchar2 pretty) as json_data   <\/span><\/span>\nfrom dept_cv cv  where cv.data."_id" = 10<\/span><\/span>\n <\/span><\/span>\nPlan hash value: 1318549807<\/span><\/span>\n <\/span><\/span>\n--------------------------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |<\/span><\/span>\n--------------------------------------------------------------------------------------<\/span><\/span>\n|   0 | SELECT STATEMENT            |        |       |       |     9 (100)|          |<\/span><\/span>\n|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |<\/span><\/span>\n|   3 |  SORT GROUP BY              |        |     1 |    38 |            |          |<\/span><\/span>\n|*  4 |   TABLE ACCESS FULL         | EMP    |     5 |   190 |     3   (0)| 00:00:01 |<\/span><\/span>\n|*  5 |  TABLE ACCESS FULL          | DEPT   |     1 |    20 |     3   (0)| 00:00:01 |<\/span><\/span>\n--------------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nPredicate Information (identified by operation id):<\/span><\/span>\n---------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   2 - access("OUTER_ALIAS2"."EMPNO"=:B1)<\/span><\/span>\n   4 - filter("OUTER_ALIAS1"."DEPTNO"=:B1)<\/span><\/span>\n   5 - filter((("DEPTNO"=10 OR "LOC"='DALLAS') AND <\/span><\/span>\n              JSON_VALUE(JSON_SCALAR("DEPTNO" JSON NULL ON NULL ) FORMAT OSON , '$' <\/span><\/span>\n              RETURNING NUMBER NULL ON ERROR TYPE(STRICT) )=10))<\/span><\/span>\n <\/span><\/span>\nSQL Analysis Report (identified by operation id\/Query Block Name\/Object Alias):<\/span><\/span>\n-------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   5 -  SEL$95C0DFA4 \/ "OUTER_ALIAS0"@"SEL$3"<\/span><\/span>\n           -  The following columns have predicates which preclude their <\/span><\/span>\n              use as keys in index range scan. Consider rewriting the <\/span><\/span>\n              predicates.<\/span><\/span>\n                "DEPTNO"<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The SQL analysis report at the bottom is interesting. It clearly states that no index range scan was used and that we should rewrite the query to avoid implicit type conversion.<\/p>\n\n\n\n
    Let’s do that.<\/p>\n\n\n\n
    12) Query deptno 10 in dept_cv with explicit type conversion<\/span><\/path><\/path><\/svg><\/span>
    select<\/span> json_serialize(cv.data <\/span>returning<\/span> <\/span>varchar2<\/span> pretty) <\/span>as<\/span> json_data<\/span><\/span>\n  <\/span>from<\/span> dept_cv cv<\/span><\/span>\n <\/span>where<\/span> cv.data.<\/span>"_id"<\/span>.<\/span>number<\/span>() = <\/span>10<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    And now the execution plan has changed for the better.<\/p>\n\n\n\n
    13) Execution plan querying dept_cv with explicit type conversion<\/span><\/path><\/path><\/svg><\/span>
    PLAN_TABLE_OUTPUT<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\nSQL_ID  cca5xrgndnmkd, child number 2<\/span><\/span>\n-------------------------------------<\/span><\/span>\nselect json_serialize(cv.data returning varchar2 pretty) as json_data   <\/span><\/span>\nfrom dept_cv cv  where cv.data."_id".number() = 10<\/span><\/span>\n <\/span><\/span>\nPlan hash value: 2166484641<\/span><\/span>\n <\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n|   0 | SELECT STATEMENT            |         |       |       |     7 (100)|          |<\/span><\/span>\n|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    10 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  2 |   INDEX UNIQUE SCAN         | EMP_PK  |     1 |       |     0   (0)|          |<\/span><\/span>\n|   3 |  SORT GROUP BY              |         |     1 |    38 |            |          |<\/span><\/span>\n|*  4 |   TABLE ACCESS FULL         | EMP     |     5 |   190 |     3   (0)| 00:00:01 |<\/span><\/span>\n|   5 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |<\/span><\/span>\n|*  6 |   INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)|          |<\/span><\/span>\n---------------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nPredicate Information (identified by operation id):<\/span><\/span>\n---------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   2 - access("OUTER_ALIAS2"."EMPNO"=:B1)<\/span><\/span>\n   4 - filter("OUTER_ALIAS1"."DEPTNO"=:B1)<\/span><\/span>\n   6 - access("DEPTNO"=10)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Making an index unique scan possible.<\/p>\n\n\n\n
    6. Conclusion<\/h2>\n\n\n\n
    Always use a SQL\/JSON path expression method<\/a> (binary()<\/code>, boolean()<\/code>, date()<\/code>, dateWithTime()<\/code>, number()<\/code>, string()<\/code>, …) when comparing a JSON value to a non-JSON value in SQL. This way you avoid implicit type conversions, improve the readability of your code, and give the Oracle Database everything it needs to create an optimal execution plan.<\/p>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    Introduction Before comparing two values, the Oracle Database automatically ensures that both values have the same data type. It converts one of the values to match the data type of the other value. The SQL Language Reference manual describes when and how implicit data conversions happen. However, Oracle recommends that you convert [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":13817,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[140,85],"class_list":["post-13804","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-oracle-26ai","tag-sql"],"yoast_head":"\nAvoid Implicit Type Conversion in JSON Access - Philipp Salvisberg's Blog<\/title>\n<meta name=\"description\" content=\"Discover why explicit JSON value conversion in SQL is essential for best performance and how to apply it in your code.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Avoid Implicit Type Conversion in JSON Access - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"Discover why explicit JSON value conversion in SQL is essential for best performance and how to apply it in your code.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-01-28T16:38:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-28T16:39:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2025\/01\/avoid-implicit-type-conversion-in-json-access.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"1024\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Avoid Implicit Type Conversion in JSON Access\",\"datePublished\":\"2025-01-28T16:38:55+00:00\",\"dateModified\":\"2025-01-28T16:39:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/\"},\"wordCount\":705,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/01\\\/avoid-implicit-type-conversion-in-json-access.webp\",\"keywords\":[\"Oracle 26ai\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/\",\"name\":\"Avoid Implicit Type Conversion in JSON Access - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/01\\\/avoid-implicit-type-conversion-in-json-access.webp\",\"datePublished\":\"2025-01-28T16:38:55+00:00\",\"dateModified\":\"2025-01-28T16:39:04+00:00\",\"description\":\"Discover why explicit JSON value conversion in SQL is essential for best performance and how to apply it in your code.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/01\\\/avoid-implicit-type-conversion-in-json-access.webp\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/01\\\/avoid-implicit-type-conversion-in-json-access.webp\",\"width\":1024,\"height\":1024,\"caption\":\"Avoid Implicit Type Conversion in JSON Access\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2025\\\/01\\\/28\\\/avoid-implicit-type-conversion-in-json-access\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Avoid Implicit Type Conversion in JSON Access\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Avoid Implicit Type Conversion in JSON Access - Philipp Salvisberg's Blog","description":"Discover why explicit JSON value conversion in SQL is essential for best performance and how to apply it in your code.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/","og_locale":"en_US","og_type":"article","og_title":"Avoid Implicit Type Conversion in JSON Access - Philipp Salvisberg's Blog","og_description":"Discover why explicit JSON value conversion in SQL is essential for best performance and how to apply it in your code.","og_url":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2025-01-28T16:38:55+00:00","article_modified_time":"2025-01-28T16:39:04+00:00","og_image":[{"width":1024,"height":1024,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2025\/01\/avoid-implicit-type-conversion-in-json-access.webp","type":"image\/webp"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Avoid Implicit Type Conversion in JSON Access","datePublished":"2025-01-28T16:38:55+00:00","dateModified":"2025-01-28T16:39:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/"},"wordCount":705,"commentCount":0,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2025\/01\/avoid-implicit-type-conversion-in-json-access.webp","keywords":["Oracle 26ai","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/","url":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/","name":"Avoid Implicit Type Conversion in JSON Access - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2025\/01\/avoid-implicit-type-conversion-in-json-access.webp","datePublished":"2025-01-28T16:38:55+00:00","dateModified":"2025-01-28T16:39:04+00:00","description":"Discover why explicit JSON value conversion in SQL is essential for best performance and how to apply it in your code.","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2025\/01\/avoid-implicit-type-conversion-in-json-access.webp","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2025\/01\/avoid-implicit-type-conversion-in-json-access.webp","width":1024,"height":1024,"caption":"Avoid Implicit Type Conversion in JSON Access"},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2025\/01\/28\/avoid-implicit-type-conversion-in-json-access\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Avoid Implicit Type Conversion in JSON Access"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=13804"}],"version-history":[{"count":19,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13804\/revisions"}],"predecessor-version":[{"id":13824,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/13804\/revisions\/13824"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/13817"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=13804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=13804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=13804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}