# Transforming hierarchical recursive data to flat CSV format

Flatten a hierarchical JSON structure of employees into a CSV format, using the Data Shaping Query Language RECURSIVE ON and PARENT clauses.

## About this task

In this example, you have a hierarchical JSON structure containing employee data organized by reporting structure, with a president at the top level and nested teams of managers and employees. You will flatten this structure into a CSV file with columns for employee number, employee name, job title, manager ID, and salary.

The input JSON follows an organizational hierarchy with nested team elements as follows:

```
{
  "employees": [
    {
      "id": "E001",
      "name": "Alice Johnson",
      "role": "President",
      "salary": 150000,
      "team": [
        {
          "id": "E002",
          "name": "Bob Smith",
          "role": "VP Engineering",
          "salary": 120000,
          "team": [
            {
              "id": "E003",
              "name": "Carol Williams",
              "role": "Engineering Manager",
              "salary": 95000,
              "team": [
                {
                  "id": "E004",
                  "name": "David Brown",
                  "role": "Senior Developer",
                  "salary": 85000,
                  "team": []
                },
                {
                  "id": "E005",
                  "name": "Emma Davis",
                  "role": "Developer",
                  "salary": 75000,
                  "team": []
                }
              ]
            }
          ]
        },
        {
          "id": "E006",
          "name": "Frank Miller",
          "role": "VP Sales",
          "salary": 115000,
          "team": [
            {
              "id": "E007",
              "name": "Grace Lee",
              "role": "Sales Manager",
              "salary": 85000,
              "team": [
                {
                  "id": "E008",
                  "name": "Henry Chen",
                  "role": "Sales Representative",
                  "salary": 65000,
                  "team": []
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}
```

The output is a flat CSV structure with the following columns:

```
EMPNO,ENAME,JOB,MGR,SAL
```

## Procedure

1. Create a new DSQL map and add the input and output structures.

   A first mapping between the root elements is automatically created.
2. Click the output row looping element and enter the following expression:
   ```
   FROM employees RECURSIVE ON team PARENT $p
   ```

   The FROM employees RECURSIVE ON team clause iterates through all employees and recursively descends into the nested team arrays at every level of the hierarchy. The PARENT $p clause assigns the parent object to the variable $p. For the root element president, $p is empty. For all other employees, $p contains the data of their direct manager.
3. Map the input fields to the output elements as follows:
   1. id on EMPNO
   2. name on ENAME
   3. role on JOB
   4. salary on SAL
4. Click the output MGR element and enter the following clause:
   ```
   IF (hasValue($p)) $p.id
   ```

   The clause checks if the variable $p has a value before assigning the parent ID. If empty, it assigns an empty string. Otherwise, it assigns the parent ID.
5. Execute the DSQL map using the Test Run feature.

## Results

The hierarchical JSON structure is flattened into individual employee records with manager relationships preserved as follows:

```
EMPNO,ENAME,JOB,MGR,SAL
E001,Alice Johnson,President,,150000
E002,Bob Smith,VP Engineering,E001,120000
E003,Carol Williams,Engineering Manager,E002,95000
E004,David Brown,Senior Developer,E003,85000
E005,Emma Davis,Developer,E003,75000
E006,Frank Miller,VP Sales,E001,115000
E007,Grace Lee,Sales Manager,E006,85000
E008,Henry Chen,Sales Representative,E007,65000
```