Splitting a loop in a DSQL map

Map multiple loops to a single loop.

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 two different loops in the same JSON file and map it to a single loop in the output. The input data looks like this:

{
    "bicycles": [
        {
            "make": "MBIKE",
            "description": "27.5 inch electric mountain bike",
            "color": "blue",
            "price": 899.99
        },
        {
            "make": "EBIKE",
            "description": "Electric hybrid bike",
            "color": "grey/green",
            "price": 999.99
        },
        {
            "make": "WBIKE",
            "description": "Women's road bike",
            "color": "white",
            "price": 299.99
        }
    ],
    "scooters": [
        {
            "make": "ESCOOT",
            "description": "Camou electric scooter",
            "color": "black",
            "price": 749
        },
        {
            "make": "EVSCOOT",
            "description": "Folding electric velocity+ scooter",
            "color": "black",
            "price": 599.99
        },
        {
            "make": "KSCOOT",
            "description": "24 volt kid scooter",
            "color": "white",
            "price": 299.99
        }
    ]
}

The output structure looks like this:

{
    "items": [
        {
            "make": "",
            "description": "",
            "price": ""
        }
    ]
}

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 items element in the output structure and click Split.

This creates a new identical loop. It is needed in order to map both the input bicycles and scooters elements. Even though the structure in the map displays two different items loops after the split, the output will contain a single items array with the contents of both loops.

This change is only applied in the map and not propagated to the structure itself. If you right-click the new loop and click Go to Structure Element to open the structure, you can see that it has not changed.
Drag and drop the input bicycles element on the first items loop and the bicycles element on the second one.

Information noteTip: If needed, you can move or delete a split element by right-clicking it and clicking Move Up, Move Down, or Delete.

Results

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

{
   "items":[
      {
         "make":"MBIKE",
         "description":"27.5 inch electric mountain bike",
         "price":"899.99"
      },
      {
         "make":"EBIKE",
         "description":"Electric hybrid bike",
         "price":"999.99"
      },
      {
         "make":"WBIKE",
         "description":"Women's road bike",
         "price":"299.99"
      },
      {
         "make":"ESCOOT",
         "description":"Camou electric scooter",
         "price":"749.0"
      },
      {
         "make":"EVSCOOT",
         "description":"Folding electric velocity+ scooter",
         "price":"599.99"
      },
      {
         "make":"KSCOOT",
         "description":"24 volt kid scooter",
         "price":"299.99"
      }
   ]
}

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 itemsbycategory
SELECT {
   items = (
      FROM bicycles
      SELECT {
         make = make,
         description = description,
         price = price
      }
      UNION ALL
      FROM scooters
      SELECT {
         make = make,
         description = description,
         price = price
      }
   )
}

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