I have requirement to call PATCH method of a resource where user can update of the details mentioned in the request . Account ID which is the key is passed as URI. User can update any or all of these parameters. Since request parameters are not fixed i need to dynamically build UPDATE query.
{
"name": "Mule",
"phone": "888888888",
"balance": "2000",
"billingCity": "London"
}
Below is the dataweave that is written to build set query. I have tried all combinations to execute update query.
%dw 2.0
import * from dw::core::Strings
output application/json
---
((({
name: payload.name default "",
phone: payload.phone default "",
balance: payload.balance default "",
billingcity: payload.billingCity default ""
}pluck (($$)++'='++"\""++($)++"\"") )
reduce(item, acc="")-> acc ++ (if(!isEmpty(substringAfter(item,"="))) item++"," else "")) substringBeforeLast(",")) as String
DB query
UPDATE account
SET :query
WHERE idaccount = :accID
Input parameters
output application/java
---
{
query: vars.query,
accID: attributes.uriParams.accID as String
}
vars.query is expected as name="Max",phone="9999999999",balance="20000",billingcity="Bangalore" to work properly. However it is not working . Tried replacing escape character with "" but then it doesnt work.
"name=\"Max\",phone=\"9999999999\",balance=\"20000\",billingcity=\"Bangalore\""
Kindly let me know if any better ideas to make this work
Shekh Muenuddeen (NTTData) 
Forum Ambassador
Hey,
Another way to do it first create query variable and store below DataWeave logic
%dw 2.0
output application/json
var data = {
"name": "Mule",
"phone": "888888888",
"balance": "2000",
"billingCity": "London"
}
---
"UPDATE account SET " ++
((data filterObject ((value, key, index) -> (value != null and value != "")) mapObject ((value, key, index) -> {
myData : (key as String) ++ " = :" ++ (key as String)
})).*myData joinBy " AND ") ++ "WHERE idaccount = :accID"
Its gives you query like below I have stored it query variable
"UPDATE account SET name = :name AND phone = :phone AND balance = :balance AND billingCity = :billingCityWHERE idaccount = :accID"
Another DataWeave to stored the input parameters its stored like below DataWeave
%dw 2.0
output application/json
var data = {
"name": "Mule",
"phone": "888888888",
"balance": "2000",
"billingCity": "London"
}
---
{
idaccount : "12345678"
} ++
(data filterObject ((value, key, index) -> (value != null and value != ""))) default {}
Output
{
"idaccount": "12345678",
"name": "Mule",
"phone": "888888888",
"balance": "2000",
"billingCity": "London"
}
And Finally in the DataBase Update Operation I have provided the both variable query and inputparametes as below
SO its dynamic with Paramterized.
Regards,
Shekh
