solved ! the problem was in the code, you have to select only the flattened columns cause SAS doesnt understand the ORACLE CLOB or JSON data type.  This works like a charm proc sql _method ; connect to oracle(user=user password='xxxx' PATH = pathname); execute (alter session set current_schema = schema) by oracle; create table tablesas as select * from connection to oracle ( select j.* from user.tableora d, json_table ( d.JSON_field2, '$' columns ( id PATH '$.idnum', date1 PATH '$.date1', date2 PATH '$.date2') )j WHERE json_exists(d.JSON_field1 , '$[*]?(@.idnum == 11)') ); DISCONNECT FROM ORACLE; quit; ... View more