Creating and Using Key-Value Lookups in MuleSoft (Mule 4)

Lookups or key-value pairs are really helpful while developing integrations. Suppose, we get a value from the source system but the target system requires a corresponding value for that key(incoming data). One way is to store such cross-reference in a database table and fetch values. What if, these values are not changed frequently, and accessing a database will probably pose an overhead. A better approach would be to have a static lookup file included within the Mule application. This lookup file can be a .csv, JSON, XML, Excel, etc. file. For simplicity, in this blog, we will be using CSV files as an example for creating and using lookups in Mule 4. A CSV file is easy to maintain and use. Let's start with the below use case.

Let's take a use case of Country codes. From the source system, the country field contains Country Name e.g. India but our target system requires 2 character Country Code e.g. IN as input in one of the fields. Now such fields will hardly change in a real-time scenario. We can create a csv file(country_codes.csv) as shown below.

CSV File- country_codes.csv

United States,US
Czech Republic,CZ

From the source system, we are getting an address payload as shown below.

Source Payload

"address": {
"streetAdd": "street1",
"streetAdd2": "address2",
"city": "City1",
"state": "State1",
"country": "Indonesia",
"zip": "123456"

Our target system expects only country code to be sent in the payload. Below are the steps to accomplish this


1. Place the file country_codes.csv under src/main/resources. In this example, we will place it inside a  folder named lookup. If you are using a different folder update classpath accordingly.
2. Now, we can refer to the file inside the dataweave using the readUrl() dataweave function.
3. Let us assume input data is stored in payload, our dataweave transformation will look as below.
%dw 2.0
output application/json
var csvData = readUrl("classpath://lookup/country_codes.csv","application/csv")

fun lookupCC(country: String) : String = 
(csvData filter ((item, index) -> item.Country == country))[0].CountryCode as String
    addressLine: payload.address.streetAdd ++ " " ++ payload.address.streetAdd2,
    state: payload.address.state,
    countryCode: lookupCC(
4. We have created a function called lookupCC() which will take one String input and match the incoming value of Country in the lookup csv file and give the country code as output. The output of the above code will look like this:

Output Payload

  "add": {
    "addressLine": "street1 address2",
    "city": "City1",
    "state": "State1",
    "countryCode": "ID"
5. What if the country name is not available in the csv file, the output will come as null. In such cases, we can update the function and provide the default value. For example:
fun lookupCC(country: String) : String = 
(csvData filter ((item, index) -> item.Country == country))[0].CountryCode as String default "--"
If we don't find any matching value in the lookup file it will default country code as "--".

Similarly, we can extend the function and the csv file to fetch values with multiple inputs and conditions.


Please share your valuable feedback 😊😊😊


Post a Comment

Popular posts from this blog

DateTime formatting using xp20:format-dateTime ()

Create Delimited String from XML Nodes and Vice Versa in SOA 12c

Import and Export MDS artifacts in SOA 12c