Return variables from nested loops in powershell-CodePudding

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

CodePudding user response：

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