Return variables from nested loops in powershell

Question

I am a PowerShell beginner and I just want to know what how to return data from nested loops in a proper way. I have a simple task to get a list of DBs in AWS Glue, then get the list of tables from these databases and create a file with results in the following format:

| DBname   | TableName      |
| -------- | -------------- |
| DB1      | Table1         |
| DB1      | Table2         |
| DB1      | Table3         |
| DB2      | Table1         |
| DB2      | Table2         |

Here is the code that I wrote:

$dbs = aws glue get-databases  --region eu-central-1 --profile test | ConvertFrom-Json 
foreach ($db in $dbs) {
      
        foreach ($dbname in $db.DatabaseList.Name) {
                $tables = aws glue get-tables --database-name $dbname  --region eu-central-1 --profile test | ConvertFrom-Json 
                $resuts = $dbname, ",", $tables.TableList.Name      
                Write-Host $resuts   
        }
}

and the result is like:

| DBname   | TableName                     |
| -------- | ----------------------------- |
| DB1      | Table1 Table2 Table3          |
| DB2      | Table1 Table2                 |

How to return the data in a proper format? Since some of the DBs don't have any tables inside, I can't use the Export-CSV command

Answer 1

Create one new object per table, each of which has the database and table name as properties, then assign all the output from the outer loop to a variable:

$dbs = aws glue get-databases  --region eu-central-1 --profile test | ConvertFrom-Json 
$tableObjects = foreach ($db in $dbs) {
    foreach ($dbname in $db.DatabaseList.Name) {
        $tables = aws glue get-tables --database-name $dbname  --region eu-central-1 --profile test | ConvertFrom-Json 
        foreach($tableName in $tables.TableList.Name){
            [pscustomobject]@{
                DBName = $dbname
                TableName = $tableName
            }
        }
    }
}

Now $tableObjects will give the desired about when formatted as a table:

PS ~> $tableObjects |Format-Table

DBname   TableName     
------   ---------
DB1      Table1        
DB1      Table2        
DB1      Table3        
DB2      Table1        
DB2      Table2