Need help in Building dynamic select query in Mule 4 DW 2.0

Question

I have the following query params coming to my API

{
    "PERSON_ID": "123456",
    "LAST_MODIFIED_DATE": "2011-12-15T00:00:00",
    "START_DATE": "2011-12-15T00:00:00",
    "END_DATE": "2012-12-15T00:00:00"
}

I need to build a different dynamic SQL query based on the input parameters which can be any of the these given below - (either LAST_MODIFIED_DATE or (START_DATE and END_DATE) will be based. But PERSON_ID can be passed any of the other params

select * from Person where PERSON_ID = 123456 
select * from Person where LAST_MODIFIED_DATE = 2011-12-15T00:00:00 
select * from Person where PERSON_ID = 123456 and LAST_MODIFIED_DATE = 2011-12-15T00:00:00 
select * from Person where PERSON_ID = 123456 and LAST_MODIFIED_DATE between 2011-12-15T00:00:00 and 2012-12-15T00:00:00
select * from Person where LAST_MODIFIED_DATE between 2011-12-15T00:00:00 and 2012-12-15T00:00:00
or if no query params are passed it will be 
select * from Person

my DW logic is not able to cater to this requirement.

Tried below but not able to get the required results. Could you please suggest a recommended approach or best solution

%dw 2.0
output application/json
var data = {
    "PERSON_ID": "123456",
    "LAST_MODIFIED_DATE": "2011-12-15T00:00:00",
    "START_DATE": "2011-12-15T00:00:00",
    "END_DATE": "2012-12-15T00:00:00"
}
"Select * from Person Where " ++
((data filterObject ((value, key, index) -> (value != null and value != "")) mapObject ((value, key, index) -> {
    (myData : "LAST_MODIFIED_DATE between " ++ (key as String)) if ((key as String) == "START_DATE"),
    (myData : "AND " ++ (key as String)) if ((key as String) == "END_DATE"),
    (myData : (key as String) ++ " = :" ++ (key as String)) if ((key as String) == "PERSON_ID"),
     myData : (key as String) ++ " = :" ++ (key as String)
})).*myData joinBy  " AND ")

Later in the DB connector I am setting the above output variable as query in SQL String

Answer 1

It is a very common use case with a lot of possible solutions. What I like to do is I create an Array with all the conditions as items. Then join them using joinBy function with a ' AND ' string to join those condition.

If you follow this the use of Conditional Elements makes the complex and dynamic SQL generation very readable and clear IMO

Updated the following based on Aled's suggestion to avoid SQL Injection For Example

%dw 2.0
output text/plain
fun generateWhereClause(queryParams) = 
    [
        ("PERSON_ID = :PERSON_ID") if(queryParams.PERSON_ID?),
        ("LAST_MODIFIED_DATE = :LAST_MODIFIED_DATE") if(queryParams.LAST_MODIFIED_DATE?),
        ("LAST_MODIFIED_DATE >= :START_DATE") if(queryParams.START_DATE?),
        ("LAST_MODIFIED_DATE <= :END_DATE") if(queryParams.END_DATE?),
    ] joinBy " AND "
---
'SELECT * FROM person WHERE $(generateWhereClause(vars.queryParams))'

This will generate a parameterized SQL Statement, and you can pass your query parameters as the input parameter in the select operation.

<db:select config-ref="dbConfig">
    <db:sql>#[payload]</db:sql>
    <db:input-parameters>
      #[vars.queryParams // whereever you have your params]
    </db:input-parameters>
  </db:select>

I have dropped between operator and used >= and <= operators because between does not fit the pattern easily and makes the conditions in the array complex. If you want to use it though, you can.