Styjun

Some procedures I'm working on accept a JSON string as a varchar2 as an input parameter, which contains parameters sent as key-value pairs from our web server that are intended to filter query results. Depending on the use case, certain parameters may not appear in the incoming JSON. I'm wondering if there's any way that I can use the parameter values in the JSON string as conditions in a query, even if certain parameters may not return a value? Something like this:

v_json VARCHAR2 := '"paramA":"valueA","paramB":"valueB"';
//the JSON can include values for either paramA, paramB, and paramC
SELECT * 
FROM table
WHERE colA = paramA 
 AND colB > paramB
 AND colC LIKE paramC;

Since the JSON has no paramC, I'd need to run a query like this:

SELECT *
FROM table
WHERE colA = paramA
 AND colB > paramB
//condition C shouldn't be considered, since a null value would have
//produce the wrong results

In Oracle Version 12c or above, you can achieve this using JSON_EXISTS conditional and JSON_VALUE to get the value. Below is sample code based on your updated requirement.

declare
 v_json VARCHAR2(4000) := '"paramA":"valueA","paramB":"valueB"';
 l_cntA number;
 l_cntB number;
 l_cntC number;
 l_cnt number;
 v_sql varchar2(4000);
begin
 select count(1) into l_cntA from dual where JSON_EXISTS(v_json,'$.paramA');
 select count(1) into l_cntB from dual where JSON_EXISTS(v_json,'$.paramB');
 select count(1) into l_cntC from dual where JSON_EXISTS(v_json,'$.paramC');
v_sql := '
SELECT count(1) 
FROM table
WHERE '|| 
 case when l_cntA > 0 then 
 'colA=JSON_VALUE('''||v_json||''',''$.paramA'')' end ||
 case when (l_cntA > 0 and l_cntB > 0 ) then 
 ' AND ' end ||
 case when l_cntB > 0 then 
 'colB = JSON_VALUE('''||v_json||''',''$.paramB'')' end ||
 case when ((l_cntA > 0 or l_cntB > 0 ) and l_cntC > 0 ) then 
 ' AND ' end ||
 case when l_cntC > 0 then
 'colC LIKE ''%''||JSON_VALUE('''||v_json||''',''$.paramC'')||''%''' 
 end; 
DBMS_OUTPUT.PUT_LINE('v_sql' || v_sql); 

execute immediate v_sql into l_cnt;

DBMS_OUTPUT.PUT_LINE('v_name ' || l_cnt);

exception 
when others then
DBMS_OUTPUT.PUT_LINE('No Data Found'); 
end;

For Oracle version older then 12/18c, you can download,install and use open source PL/JSON package to achieve this.Click here to Download PL/JSON Hope this helps.