Unrolling a loop in a DSQL map

Map multiple non-looping elements to the same looping element.

Before you begin

You have created an input and an output structure. You can use the JSON samples below to create your structures.

About this task

In this example, you will create a DSQL map to extract data from three non-looping elements and map it to a single loop in the output. The input data looks like this:

[
    {
        "make": "MBIKE",
        "description": "27.5 inch electric mountain bike",
        "tyres": {
            "brand": "hutchinson",
            "type": "Kraken 29x"
        },
        "seat": {
            "brand": "ergon",
            "type": "SR road sport gel"
        },
        "derailleur": {
            "brand": "shimano",
            "type": "ultegra D12"
        }
    },
    {
        "make": "EBIKE",
        "description": "Electric hybrid bike",
        "tyres": {
            "brand": "vittoria",
            "type": "Gravel terrend mix"
        },
        "seat": {
            "brand": "selle italia",
            "type": "SMTB black L3"
        },
        "derailleur": {
            "brand": "sram",
            "type": "GX 10"
        }
    },
    {
        "make": "WBIKE",
        "description": "Women's road bike",
        "tyres": {
            "brand": "michelin",
            "type": "power cup flex"
        },
        "seat": {
            "brand": "xlc",
            "type": "geltech ergo"
        },
        "derailleur": {
            "brand": "ceramicspeed",
            "type": "Eagle EAX"
        }
    }
]

The output structure looks like this:

[
    {
        "make": "",
        "description": "",
        "accessories": [
            {
                "category": "",
                "brand": "",
                "type": ""
            }
        ]
    }
]

Procedure

Create a new DSQL Map and add the input and output structures.
A first mapping between the root elements is automatically created. You can see the corresponding Data Shaping Query Language expression in the root element of the output structure.
Right-click the accessories element in the output structure and click Unroll, then repeat this step.
This creates two identical non-looping elements. They are needed in order to map the input tyres, seat, and derailleur elements to the same output accessories loop. Even though the structure in the map displays three different accessories elements after the unroll, the output will contain a single accessories array with the contents of the three elements.
Map the following elements using drag-and-drop:
- bikesaccessories.make to bikesaccessorieslist.make
- bikesaccessories.description to bikesaccessorieslist.description
- bikesaccessories.tyres to the first bikesaccessorieslist.accessories
- bikesaccessories.seat to the second bikesaccessorieslist.accessories
- bikesaccessories.derailleur to the third bikesaccessorieslist.accessories
Information noteTip: If needed, you can move or delete an unrolled element by right-clicking it and clicking Move Up, Move Down, or Delete.
Click the category element under the first accessories element and enter 'tyres' in the expression editor.
Repeat the previous step for the second and third accessories elements and enter 'seat' and 'derailleur' respectively.

Results

Your map is configured, you can use the Test Run feature to check the result. In this example, the following result is returned:

[
   {
      "make":"MBIKE",
      "description":"27.5 inch electric mountain bike",
      "accessories":[
         {
            "category":"tyres",
            "brand":"hutchinson",
            "type":"Kraken 29x"
         },
         {
            "category":"seat",
            "brand":"ergon",
            "type":"SR road sport gel"
         },
         {
            "category":"derailleur",
            "brand":"shimano",
            "type":"ultegra D12"
         }
      ]
   },
   {
      "make":"EBIKE",
      "description":"Electric hybrid bike",
      "accessories":[
         {
            "category":"tyres",
            "brand":"vittoria",
            "type":"Gravel terrend mix"
         },
         {
            "category":"seat",
            "brand":"selle italia",
            "type":"SMTB black L3"
         },
         {
            "category":"derailleur",
            "brand":"sram",
            "type":"GX 10"
         }
      ]
   },
   {
      "make":"WBIKE",
      "description":"Women's road bike",
      "accessories":[
         {
            "category":"tyres",
            "brand":"michelin",
            "type":"power cup flex"
         },
         {
            "category":"seat",
            "brand":"xlc",
            "type":"geltech ergo"
         },
         {
            "category":"derailleur",
            "brand":"ceramicspeed",
            "type":"Eagle EAX"
         }
      ]
   }
   ]

You can also check the DSQL Script view to see how this is handled with Data Shaping Query Language using the UNION ALL clause. For more information, see the Talend Data Shaping Language Reference Guide. In this example, the following script is generated:

FROM bikesaccessories
SELECT {
   make = make,
   description = description,
   accessories = (
      SELECT {
         category = 'tyres',
         brand = tyres.brand,
         type = tyres.type
      }
      UNION ALL
      SELECT {
         category = 'seat',
         brand = seat.brand,
         type = seat.type
      }
      UNION ALL
      FROM 1 TO 1
      SELECT {
         category = 'derailleur',
         brand = derailleur.brand,
         type = derailleur.type
      }
   )
}

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here