Using DataWeave to Manipulate JSON in HTTP Request and DB Query

Manipulating and sending JSON objects and arrays in HTTP Requests and DB Queries

Introduction

MuleSoft developers face challenges regularly, some of them related to use cases where JSON objects and JSON arrays have to be passed as query parameters to HTTP requests, or as input parameters to DB queries.

This blog post aims at demonstrating how developers can accomplish such scenarios.

Sending JSON Data as a Query Parameter in HTTP Request

When building RESTful APIs or making HTTP requests, passing complex data as query parameters is often required. By serializing the JSON objects / JSON arrays and adding them to the request we can interact with APIs that expect JSON data in query parameters.

JSON objects can be used to send multiple key-value pairs in a single parameter, providing feasibility even in GET methods to send configurations or such.

JSON arrays can be used to send multiple values in a single parameter, facilitating batch operations or filtering data based on multiple criteria.

Common Mistake

As an example, let’s examine an User Management API which accepts a JSON array with IDs and returns all matching users. This will be based on making API calls to https://webhook.site

# Examplar JSON array
[ "USER1","USER2","USER3","USER4" ]

Developers usually pass the JSON data to query parameters section without serializing as per the example below

Passing JSON Array Directly under Query Parameters

Passing JSON Object Directly under Query Parameters

Leading to failures due to conversion errors as demonstrated below.

Error when JSON Array is passed into Query Parameters

Error when above JSON Object is passed into Query Parameters

For any output type defined (application/json or application/java) HTTP request component will finally try to convert the provided query parameters to String which is a Java data type which results in an error.

Therefore in these scenarios, serializing JSON data is mandatory and can be made by simply converting JSON data to text/string, which can be done utilizing built-in functions in DataWeave.

Using “write” function

Write function converts any object into string or binary format. Find the official documentation here:

write | MuleSoft Documentation

Passing JSON array of IDs we can use the write function and it will be converted to string and passed to the end system.

{
 Ids : write([
     "USER1","USER2","USER3","USER4"
  ],"application/json")
}

Using Write Function to Pass JSON Array

On the API receiver end, the request would look like the encoded URL below:

Received Request details on the End System

Decoding the above URL would look like this:

Decoding the URL Received

Using “indent” (true/false)

You can also make use of this writer option which will remove indent from the input JSON and make the Request URL appear much cleaner.

{
 Ids : write([
     "USER1","USER2","USER3","USER4"
  ],"application/json", {"indent" : false})
}

Received Request details on the End System with indent setting

If the same URL is decoded then the result would look like this:

Decoding the URL

In Both cases here end system can parse the JSON from the query parameter as the JSON Array does not loose it’s capability.

Alternate Usage

By following this approach, script in query parameter section will be much cleaner as you perform the serialization separately in a Set-Variable:

 %dw 2.0
 output text/plain
 ---
 write(your_json_here,'text/plain', {"indent" : false}) 

Similarly and alternatively, that same write function can be embedded in a DataWeave function, allowing future reuse:

%dw 2.0
fun stringify(json) = ( 
  write(json, 'application/java', {"indent" : false})
)

In all the above cases Output should be application/java or text/plain . This it to avoid escape characters being added into Strings.

Constructing “IN” SQL query clause with JSON Array

In database interactions, the “IN” clause proves to be valuable when filtering results based on a predefined set of values, which can be achieved by modifying JSON to construct dynamic “IN” clauses in SQL queries. This approach enables more flexible and efficient database queries, enhancing the application’s performance.

There are 2 changes needed for a JSON array in order to work with IN clause:

1. () should be used instead of [] .
2. single quotes ' should be used instead of double quotes " .

As per the below snapshot. we can’t use replace function on JSON Array directly.

Dataweave Error when using replace on Array

Combining “write” and “replace” functions

We will convert the JSON Array to String first using the write function as shown above then use the replace function for all items.

Changing the JSON Array to IN clause format using Write Function

%dw 2.0
output application/java
---
write(payload, "application/json", {"indent" : false}) 
replace "[" with "(" 
replace "]" with ")"
replace "\"" with "'"

Output type must be Java or text/plain

Using “reduce” function

Reduce function is preferred when there is an array and we have to perform an operation which can shorten the input array.

reduce | MuleSoft Documentation

We will use the reduce operation to add all the elements to a string and append the brackets.

Changing the JSON Array to IN clause format using Reduce Function

%dw 2.0
output application/java  
---
('(' ++ (payload 
reduce ((item, acc = '') -> 
acc ++ "'" ++ item ++ "',"))
 ++ ')') replace ',)' with ')'

Conclusion and References

MuleSoft developers can utilise and extend DataWeave’s built-in functions to manipulate JSON structures (objects and arrays) to better interact with HTTP requests and DB operations.

• https://help.mulesoft.com/s/question/0D52T00004mXY7rSAG/send-array-list-through-query-parameters-on-http-request-and-in-operatormule-4
• https://help.mulesoft.com/s/question/0D52T00005fqs2NSAQ/adding-json-to-a-query-parameter
• https://help.mulesoft.com/s/question/0D52T00005gjfwXSAQ/want-to-know-better-way-to-convert-array-to-the-comma-separated-string-so-i-can-use-in-sql-where-clause