# Mapping nested loops in a DSQL map

Map elements from an input containing multiple nested loops.

## 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 about a store from a JSON file with multiple nested levels and write it to a flat JSON structure. The input data looks like this:

```
{
    "name": "plusstore",
    "location": "saint louis",
    "departments": [
        {
            "name": "sports",
            "salesperson_id": 25,
            "items": [
                {
                    "name": "MBIKE",
                    "description": "27.5 inch electric mountain bike"
                },
                {
                    "name": "ESCOOT",
                    "description": "Camou electric scooter"
                }
            ]
        },
        {
            "name": "electronics",
            "salesperson_id": 32,
            "items": [
                {
                    "name": "Portable SSD T5",
                    "description": "1 TB SSD USB-C"
                },
                {
                    "name": "Extreme Portable SSD",
                    "description": "500 GB SSD USB-C"
                }
            ]
        }
    ],
    "salespersons": [
        {
            "name": "Antonio Guttierez",
            "id": 25
        },
        {
            "name": "Gina Monsanto",
            "id": 32
        }
    ]
}
```

The output structure looks like this:

```
{
    "items": [
        {
            "store_name": "",
            "store_location": "",
            "department_name": "",
            "salesperson_id": "",
            "salesperson_name": "",
            "item_name": ""
        }
    ]
}
```

## Procedure

1. 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. ![Mapping of elements.](https://help.qlik.com/talend/en-US/data-mapper-user-guide/Resources/images/tdm-dsql-map-editor-nested-loops-map.png)
2. Click the output items element and enter the following expression to set the looping context:
   ```
   FROM departments
   UNNEST items
   ```

   This means that the output will contain one items occurrence for each items element in each departments element in the input.
3. Map the following elements using drag-and-drop:
   * plusstore.name to plusstoreitems.items.store\_name
   * plusstore.location to plusstoreitems.items.store\_location
   * plusstore.departments.name to plusstoreitems.items.department\_name
   * plusstore.departments.salesperson\_id to plusstoreitems.items.salesperson\_id
   * plusstore.departments.items.name to plusstoreitems.items.item\_name

   You can see that in almost all elements, the expression generated is a simple relative path. This is not possible for store\_name and department\_name because the relative path name can only refer to plusstore.departments.items.name due to the looping context. For more information, see [DSQL element path](https://help.qlik.com/talend/en-US/data-mapper-user-guide/8.0/dsql-element-path).
4. Click the output items element and add the following lines under the existing expression:
   ```
   JOIN salespersons ON id = salesperson_id
   ```

   This JOIN clause is needed to map plusstore.salespersons.name with plusstoreitems.items.salesperson\_name because the input element is not in the current looping context. This clauses changes the looping context, and if you click the output item\_name, you can see that the expression is no longer a relative path and was replaced with an absolute path.
5. Drag and drop plusstore.salespersons.name on plusstoreitems.items.salesperson\_name.

   Since the looping context is now the input salespersons loop, the expression in salesperson\_name is the relative path name.

## 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":[
      {
         "store_name":"plusstore",
         "store_location":"saint louis",
         "department_name":"sports",
         "salesperson_id":"25",
         "salesperson_name":"Antonio Guttierez",
         "item_name":"MBIKE"
      },
      {
         "store_name":"plusstore",
         "store_location":"saint louis",
         "department_name":"sports",
         "salesperson_id":"25",
         "salesperson_name":"Antonio Guttierez",
         "item_name":"ESCOOT"
      },
      {
         "store_name":"plusstore",
         "store_location":"saint louis",
         "department_name":"electronics",
         "salesperson_id":"32",
         "salesperson_name":"Gina Monsanto",
         "item_name":"Portable SSD T5"
      },
      {
         "store_name":"plusstore",
         "store_location":"saint louis",
         "department_name":"electronics",
         "salesperson_id":"32",
         "salesperson_name":"Gina Monsanto",
         "item_name":"Extreme Portable SSD"
      }
   ]
}
```

You can also check the DSQL Script view to see what the full script looks like. You can see that some elements use relative paths, while others use absolute paths:

```
FROM plusstore
SELECT {
   items = (
      FROM plusstore.departments
      UNNEST items
      JOIN salespersons ON id = salesperson_id
      SELECT {
         store_name = plusstore.name,
         store_location = location,
         department_name = plusstore.departments.name,
         salesperson_id = salesperson_id,
         salesperson_name = name,
         item_name = plusstore.departments.items.name
      }
   )
}
```