How to convert key:value pair in json as key = value in mule

Question

I am trying to form where clause from the JSON file. I want to fetch the key:value pair from the where part of below input and convert it into key = value

Input:

{
   "Table":{
      "TableName":"Employee",
      "Columns":[
         "ID",
         "Name"
      ],
      "Where":{
         "ID":"A-0001",
         "Name":"xyz"
      }
   }
}

Expected Output: I want to get where ID = 'A-0001' and Name = 'xyz'

I am using mule 4. Please help.

Thanks in advance

Answer 1

Another take at a similar approach.

%dw 2.0
output application/json
---
"where " ++ ((payload.Table.Where mapObject {
     ($$): (($$) ++ "=" ++ "'" ++ ($) ++ "'") 
}) pluck ($) joinBy " and ")

Answer 2

Building dynamic SQL? I would suggest avoiding that and sticking to parameterized queries.

I would strongly urge you to use this path: https://docs.mulesoft.com/connectors/db/database-connector-examples#use-input-parameters-to-protect-database-queries

It would be really easy to make this parameterized because all you'd have to do is passing in payload.Table.Where as your parameterized object.

If you're pulling this from some kind of trusted source and have to do things this way I suppose you could ignore the columns array and do it this way pretty easily:

%dw 2.0
output application/json
---
if (payload.Table.Where?)
    "where " ++ ((payload.Table.Where pluck "$($$) = '$($)'") reduce ($$ ++ " and " ++ $))
else
    ""

I'm sure there is a better way to do this, but if you wanted to still use the columns array you could also do this.

%dw 2.0
output application/json
---
"where " ++ (
    payload.Table.Columns reduce ((col, wClause="") -> 
        if (not payload.Table.Where[col]?) wClause
        else wClause ++ (if (wClause != "") " and " else "") ++ ("$(col) = '$(payload.Table.Where[col])'")
    )
)