Blog Post

Azure Integration Services Blog
2 MIN READ

Data Mapper Patterns: Distinct Values

KentWeareMSFT's avatar
KentWeareMSFT
Icon for Microsoft rankMicrosoft
Aug 09, 2023

In this post, we are going to discuss how we can retrieve distinct values from an array of records. We will be able to achieve this using the Distinct Values function.

 

Pre-requisites

To build this demo, we will need a sample file and schema. Within the SampleFiles.zip attachment in this post you will find a sample message and a schema that can be used for both the Source and Destination. Within this file, you will notice that we have two instances of the value 12. We would like have a distinct list of values and subsequently exclude one of these 12s.

 

 

{
   "Array":[
       {
            "value":12
       },
       {
            "value":12
       },
       {
            "value":11
       },
       {
            "value":9
       }
   ]
}

 

 

Building our Map

Within the SampleFiles.zip file we will discover a sample schema that we can use for both our source and destination schema. When we add our source and destination schemas we now need to configure our map. Expand your destination so that it looks like the following image and then add all your source elements.

 

 

The way our solution works is that you need to establish a loop that will transfer all the records from the source to the destination. 

 

If we test our map at this point, we will see all of our records come across. 

 

We now want to add our Distinct Values function but want to add it to our existing loop. The way to do this is by clicking on the sign on our loop like the following.

 

In the Functions panel, search for Distince Values and click on it.

 

Expand the Distinc Values function. you should see <ArrayItem> is already mapped to Collection. We now need to drag value to Collation.

 

Click Save

 

Click Generate so that the underlying XSLT file can be created which is used by the Test experience.

 

We can test our map using the sample file (SampleArrayMessage.json) found in the SampleFiles.zip

 

Our output should reflect the following. Notice, we only have one value of 12.

 

Video Content

If you would like to see this content in video format, please check out the following YouTube video.

 

Updated Aug 09, 2023
Version 1.0
No CommentsBe the first to comment