Error in SOQL Query with dynamic variable containing a list in Mule 4

Question

Hello I'm trying to execute an SOQL query to Salesforce but it is giving me a error while trying to add a variable that has a string of Id's inside of it.

This is the information inside the variable that I need to use :

[
  "a123E00000Qv6PWQAZ",
  "a123E00000Qv6PXQAZ",
  "a123E00000Qv6PYQAZ",
  "a123E00000Qv6PZQAZ",]

Im currently transforming it to a string like this :

"(" ++ ((payload.items.id map ("'" ++ $ ++"'")) joinBy ",") ++ ")"

Giving me this :

"('a123E00000Qv6PWQAZ','a123E00000Qv6PXQAZ','a123E00000Qv6PYQAZ','a123E00000Qv6PZQAZ')"

I want to use it this way :

SELECT Id FROM Company WHERE Result__c = 'Plus' AND Id NOT IN (':upserted')

Im currently inserting the vars.upserted in the Parameters.

When I run the flow it throws me this error when querying :

"
'Plus' AND Id NOT IN ('('a123E00000Qv6PWQAZ','a123E00000Qv6PXQAZ'
                                 ^
ERROR at Row:1:Column:82
expecting a right parentheses, found 'a123E00000Qv6PWQAZ'"

Can you help in order to make the query work ?

Answer 1

You are adding extra parenthesis and quotes into the query. The use of quotes in the query seems to be from a query with a single value, not for a list.

Try removing them from the query:

SELECT Id FROM Company WHERE Result__c = 'Plus' AND Id NOT IN (:upserted)

and from the argument:

(payload map ("'" ++ $ ++"'")) joinBy "," 

So argument upserted equals to 'a123E00000Qv6PWQAZ','a123E00000Qv6PXQAZ','a123E00000Qv6PYQAZ','a123E00000Qv6PZQAZ'