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

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.
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.
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.
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.
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
      }
   )
}

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 – let us know how we can improve!

Leave your feedback here