How to pass array of JSON values to oracle Query Using XQUERY.
One can come across a scenario for generating a SQL query or any query with the randomly passed array of input values in the WHERE clause.
Eg:
The below JSON variable — ControllingPU contains array of values.
“ControllingPU”: [“011385”,”011914"]
The values — 011385 and 011914 are required to be applied in the WHERE condition of SQL Query. This can be achieved using IN Clause in the WHERE conditions. But how to achieve the format and add the values randomly where SQL understands and parse the same???
Expected SQL Query:
SELECT * FROM <<Table_Name>> WHERE PROJECT_PU in (‘011385’,’011914’);- — — -Framing the WHERE condition using IN Clause.
PROJECT_PU values will filter the data in <<Table_Name>> and return the desired result which user is interested to look into.
This challenge can be resolved with the below generic XQUERY code snippet.
Code:
let $columns :=<root>
<ControllingPU>PROJECT_PU</ControllingPU> (: Line A :)
</root>
return
let $payload :=util:parseXML(util:toXML($input.PORdd))
return
let $condition :=””
for $filter at $index in fn:distinct-values($input.PORdd/*[.!=’’]/name())
return
if($index != fn:count(fn:distinct-values($input.PORdd/*[.!=’’]/name())))
then
for $case at $in in ($payload/*[name()=$filter]/text())
return
if(fn:count($payload/*[name()=$filter]/text()) = 1)
then
$condition ||$columns/*[name()=$filter]/text() ||” in (‘“ || fn:string-join($case, “,”) || “‘“||”’)”
else
if($in = 1)
then
$condition || $columns/*[name()=$filter]/text() ||” in (‘“|| fn:string-join($case, “,”) || “‘“||”,”
else
if($in != fn:count($payload/*[name()=$filter]/text()))
then
$condition || “‘“ || fn:string-join($case, “,”) || “‘“||”,”
else
$condition || “‘“ || fn:string-join($case, “,”) || “‘) and “
else
for $case at $in in ($payload/*[name()=$filter]/text())
return
if(fn:count($payload/*[name()=$filter]/text()) = 1)
then
$condition ||$columns/*[name()=$filter]/text() ||” in (‘“ || fn:string-join($case, “,”) || “‘“||”’)”
else
if($in = 1)
then
$condition || $columns/*[name()=$filter]/text() ||” in (‘“|| fn:string-join($case, “,”) || “‘“||”,”
else
if($in != fn:count($payload/*[name()=$filter]/text()))
then
$condition || “‘“ || fn:string-join($case, “,”) || “‘“||”,”
else
$condition || “‘“ || fn:string-join($case, “,”) || “‘)”
The above code returns a TEXT value by framing the IN Clause = “PROJECT_PU in (‘011385’,’011914’)”
Points to remember while using the XQUERY code → (: Line A :) -> 2nd line in the code
1. XQUERY is generic and can work for any number of variables with array of values.
If the user wants to add another variable like PROJECT_NUMBER then frame the JSON as below –
JSON Input –
{
“ControllingPU”: [“011385”,”011914"],
“ProjectNumber”:[“ABCD”,”PQRS”]
}
2. To handle the additional variables like ProjectNumber, XQUERY needs to be modified like below- Go to the line — (: Line A :) and add another line like below
let $columns :=<root>
<ControllingPU>PROJECT_PU</ControllingPU> (: Line A :)
<ProjectNumber>PROJECT_NUMBER</ProjectNumber>
</root>
Here PROJECT_PU and PROJECT_NUMBER should be the actual fields names available in the table and the parent tags <ControllingPU> and <ProjectNumber> Should match with the JSON input.
After the customizations, XQUERY returns the output like below –
Output — PROJECT_PU in (‘011385’,’011914’) and PROJECT_NUMBER in (‘ABCD’,’PQRS’)
Capture the Output in to a variable — “v_output” and the pass the same in SQL statement.
SQL Query — SELECT * FROM <<TABLE_NAME>> WHERE {$v_output};
Which gets decoded to below during the run time.
SELECT * FROM <<TABLE_NAME>> WHERE PROJECT_PU in (‘011385’,’011914’) and PROJECT_NUMBER in (‘ABCD’,’PQRS’);
This generic XQUERY code can be invoked in any of the XQUERY editor and generate the desired result.
Happy Coding!!!
