Mule 4 - Convert Array of strings to single string-CodePudding

I have an array of primary keys that i need for a SQL update statement. My goal here is to NOT loop through each primary key and execute an update statement many times. What i want to do is create a string and only execute the statement 1 time.

Here is my array of primary keys

["1234-114600000034-000093-ABCDE","1234-034-114600000378-FGH","1234- 
 114600000034-1146000-JKLMN","1234-1146034-00380-OPQRS","1234- 
 114600000034-1181-TUVWX"]

My desired output would be a string like this

('1234-114600000034-000093-ABCDE','1234-034-114600000378-FGH','1234- 
 114600000034-1146000-JKLMN','1234-1146034-00380-OPQRS','1234- 
 114600000034-1181-TUVWX')

My SQL statement would look something like this

UPDATE [TABLE_NAME] SET [COLUMN_NAME] = 1 WHERE ID IN ('1234-114600000034-000093-ABCDE','1234-034-114600000378-FGH','1234- 
 114600000034-1146000-JKLMN','1234-1146034-00380-OPQRS','1234- 
 114600000034-1181-TUVWX')

Any help would be greatly appreciated.

CodePudding user response：

You can use the joinBy function and then prepend and append the quote and parenthesis.

%dw 2.0
output application/json
var ids = ["1234-114600000034-000093-ABCDE","1234-034-114600000378-FGH",
    "1234-114600000034-1146000-JKLMN","1234-1146034-00380-OPQRS",
    "1234-114600000034-1181-TUVWX"]
---
"UPDATE [TABLE_NAME] SET [COLUMN_NAME] = 1 WHERE ID IN ('" 
        (ids joinBy  "','")    "')"

CodePudding user response：

One of the ways to do so could be using reduce.

Script

%dw 2.0
output application/java
var inp = ["1234-114600000034-000093-ABCDE","1234-034-114600000378-FGH","1234-114600000034-1146000-JKLMN","1234-1146034-00380-OPQRS","1234-114600000034-1181-TUVWX"]
---
('('    (inp reduce ((item, acc = '') -> acc    "'"    item    "',"  ))    ')') replace ',)' with ')'

Output

('1234-114600000034-000093-ABCDE','1234-034-114600000378-FGH','1234-114600000034-1146000-JKLMN','1234-1146034-00380-OPQRS','1234-114600000034-1181-TUVWX')