Sometimes when building maps, it becomes necessary to call a stage multiple times to process a subset of nested data. Take for example an Origin which may return an Account, but then also a list of Contacts in a structured format (such as JSON).
To process this single row of data, you will want to create 1 Account and 2 Contacts as defined in the "contacts" column (stored as a JSON Array). Starfish ETL has a feature called Queued Datasets which will allow us to define a stage for Contacts, and automatically repeat the stage for each row in the queue dataset.
To set the Queue Dataset, we at the beginning of each row we will set it to contacts column data, by calling SetQueuedJSON() in a VBScriptProcedure - Repeat Each Row.
- Sub VBScriptProcedure
SetQueuedJSON Origin("contacts"), "contacts"
SetQueuedJSON takes 2 parameters. The first is the JSON array data, and the second is the name of the dataset. Dataset names are optional - they can be left blank, you can have multiples if you need to process other data subsets such as lists of addresses, etc.
In addition to loading Queued Dataset from JSON, you could call SetQueuedDT() from a C# script which will take a .NET DataTable object as the parameter. This would be especially useful if you are retrieving your subset data from the result of a SmartQueryDT() function call.
Next you can add your Account stage as normal:
Then you can add your Contact stage, making sure to set the "Queue Behavior" to Repeat, and entering the name of Queued Dataset to use ("contacts").
Then you may proceed to set the mappings for your Contact stage. You can pull elements from your Queued Dataset by using the Queued() function and passing in the name of the field you want. Here is a simple example based on the data above. Note, you can mix mapping types of Queued() functions and pulling fields from the Origin.
Without any further scripting, Starfish ETL automatically processes the Contact stage twice, based on the data that was loaded into the "contacts" Queued Dataset.