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

Create a new DSQL map and add the input and output structures.
A first mapping between the root elements is automatically created.
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.
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
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.
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

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