Best way to manage and manipulate large sql queries in Mule

Question

I have a requirement to run a large sql query in mule, but the query changes based on the payload. I have to modify the column names, the where and group by conditions etc based on the payload.

Currently I place a template query in resources folder say test.sql. And in the query I place some keyword to be replaced like "replaceColumn". And I use a set variable component to replace that keyword with required keyword like "Field1Column"

variable: formQuery

%dw 2.0
output application/java
var query = readUrl('classpath://test.sql','text/plain')
---
query replace "replaceColumn" with payload.Field1Column

In the DB select component I simply put #[vars.formQuery]

This solution works for me, but gets difficult to replace many parts of the query by nesting the replace operator.

What is a good way to do it?

Answer 1

You can do this recursive replace based on map with key/values as described here https://simpleflatservice.com/mule4/RecursiveReplaceByMap.html

%dw 2.0
var x="The quick brown fox jumps over the lazy dog"
var myMap={fox:'chicken', dog:"me"}
var keys=myMap pluck ($$)
fun changeMe ( value, index ) = 
  if ( index < sizeOf(keys)  ) 
  changeMe( (value replace ( keys[index] ) with ( myMap[keys[index]] ) ) , index+1 ) 
  else value
output application/json
---
changeMe(x,0)

output

"The quick brown chicken jumps over the lazy me"