Mule code to convert elements in array to individual element qoutes

Question

I want to create a dynamic insert query in mulesoft as part of the system API that is can be used to interact with DB for multiple tables as per the calling processing API.

I want to below outout

"Ronaldo","GOAT"

But I'm getting below output

"Ronaldo, GOAT"

Can someone please help me, I'm trying to create a dynamic insert but values needs to be in double quotes.

Below is my current logic,

PAYLOAD

{
    "table": "std",
    "Payload": {
        "studentName" : "Ronaldo" ,
        "course" : "GOAT"
    }
}

CODE

%dw 2.0
output application/json

var table = "insert into " ++ payload.table

var value  = valuesOf(payload.Payload) reduce ((item, accumulator) -> item  ++"," ++ accumulator)

var key = keysOf(payload.Payload) reduce ((item, accumulator) -> item ++ "," ++ accumulator)

---

table ++ key ++ "(VALUES  " ++ value ++");"

OUTPUT

"Ronadlo","GOAT"

Answer 1

The script can be simpler and there are some possible improvements:

• Use the namesOf() function instead of keysOf(). The former returns keys as an Array of Strings while the later returns an Array of Keys, a different type that has to be converted and could case issues with comparisons.
• Use map() to transform the values to add double quotes before and after each String in the array. Example: if a is an array the a map ("\""++$++"\"") will concatenate a double quote before and after each string of a.
• We can encapsulate the entire transformation in a function to avoid repeating it for both variables.
• Once you get an array of Strings just use the joinBy() function to join the Strings with a separator (a comma in this case).
• The output format should be Java if you want to use the output for an SQL query. Using JSON will require extra conversions and be less performant. Java is the native format of Mule.
• Assuming that the output is an SQL insert the syntax is wrong. I used the standard syntax for it.

%dw 2.0
output application/java
fun listOfString(a)=a map ("\""++$++"\"") joinBy ","
var values = listOfString(valuesOf(payload.Payload))
var columns = listOfString(namesOf(payload.Payload))
---
"insert into " ++  payload.table ++ " (" ++ columns ++ ") VALUES (" ++ values ++");"

Output:

insert into std ("studentName","course") VALUES ("Ronaldo","GOAT");