In this example, you will learn how to create a JSON message from results of an SQL query.

This example uses the same set up as the Create new XML message example.

See Frends GitHub for more for more information about the MicrosoftSQL ExecuteQuery Task.

The MicrosoftSQL ExecuteQuery Task returns a JToken called Data, which contains two JObjects.
In this example, we will focus on the first object in the JToken and two specific properties marked with an arrow in the picture below.

To create a JSON message from the MicrosoftSQL ExecuteQuery Task result, we use Foreach Task and a low-code reference with dot notation.

Upon closer inspection of the Send JSON Message Task configuration, we can see that the Message field contains the JSON message we are formulating. The Message field is set to the JSON type, and the JSON input mode allows you to enter valid JSON as input instead of freeform text.

see Frends GitHub for more information about the Web RestRequest Tasks.

When sending the REST request, Frends will populate the low-code reference {{#var.product.ProductID}} with the actual value that, in our example case, is '680'.

By applying the same logic, the full HTTP request JSON message with populated data is:

This is a simple example Process that creates a JSON message using a Code Element. It begins with a SQL query for product information, and a Foreach Element that loops over the products returned by the query. For each product, two JSON messages are created and sent to a REST endpoint. The Process has two parallel paths that execute the message creation and sending.

In the following module, you will find the configuration details for each Element used in the Process. Further below, there are screenshots that show the Process execution. Study these options and test them in your Frends Environment, if you have access to one.
​

Below are screenshots of the configuration for each of the Elements used in this Process.

In the example Process, the Microsoft ExecuteQuery Task retrieves only 2 products from the table. The connection string is stored in an Environment Variable and is hidden. In the configuration illustration, you can see an example result of the query with the details of one product.

Microsoft ExecuteQuery Frends Task GitHub page.

The Foreach Element loops over the two queried products.

This Code Element creates a simple JSON message as a JObject.

Note how the product attributes are referred to with a #var reference. Another note is on a simple C# statement example that sets the Date attribute.

You can experiment with different C# statements in the statement, for example, changing all the product name letters to uppercase or lowercase.

This Code Element creates another message with JObject and JArray instances.

Here is an overview of the Process execution.

As the Microsoft ExecuteQuery Task returns two products as a result, the Foreach Task will be executed two times.

The Web RESTRequest Task results show an example of the first Foreach Iteration result.

The next article is Introduction to Creating messages with StringBuilder.