PowerShell nested object reference

Question

Should be simple, but my inexperience is showing.

Using the similar data, I need to create INSERT to SQL Server in PowerShell 5:

{
   "Libraries": {
      "Reported": "2018-09-01T12:00:16",
      "Locations": {
         "Branch": [
            {
               "ID": "100",
               "Address": "1 Elm Street",
               "City": "Anytown",
               "State": "ST",
               "ZIP": "23466",
               "Phone": "999-123-6543",
               "Fax": "999-123-8395",
               "Admin": "Doe, Jane"
            },
            {
               "ID": "101",
               "Address": "4 Main Street",
               "City": "Anytown",
               "State": "ST",
               "ZIP": "23456",
               "Phone": "999-123-4567",
               "Fax": "999-123-4568",
               "Admin": "Mouse, Noni"
            }
         ]
      }
   }
}

First, I want to get a list as follows:

Branch  Admin        Address                           Phone         Fax
------  ---------    --------------------------------  ------------  -------------
100     Doe, Jane    1 Elm Street, Anytown, ST 23466   999-123-6543  999-123-8395
101     Mouse, Noni  4 Main Street, Anytown, ST 23456  999-123-4567  999-123-4568

I should do this like so, but I cannot find the way for a proper delve into the object structure:

Get-Content -Path c:\reports\libraries.json -raw | ConvertFrom-Json  | ...

This will eventually feed Invoke-SQLCmd for:

Insert into Branch 
   (ID,Address,City,State,ZIP,Phone,Fax,Admin,Reviewed) 
Values
   (list from above)

The DB Reviewed column will be Reported from the JSON.

To get the view mentioned: Get-Content -Raw c:\reports\libraries.json | ConvertFrom-Json | ForEach-Object { $_.Libraries.Locations.Branch } | Select -Property @{Name='Branch';Expression='ID'}, Admin, @{Name='Address';Expression={ $( $_.Address + ', ' + $_.City + ', ' + $_.State + ' ' + $_.ZIP ) } }, Phone, Fax | Format-Table — Tony
– Tony, Commented Sep 17, 2018 at 15:15

mklement0 · Accepted Answer · 2023-05-30 19:52:38Z

Here's how to extract the branches as an array of [pscustomobject]s from your JSON input, as well as the Reported property value:

# Read the JSON file into a hierarchy of custom objects.
$objsFromJson = Get-Content -Raw t.json | ConvertFrom-Json

# Use dot notation to access the property values of interest.
$branches = $objsFromJson.Libraries.Locations.Branch
$reported = $objsFromJson.Libraries.Reported

To integrate them into a string containing an INSERT INTO SQL statement:

# Construct the arguments to the VALUES clause.
# Note: Assumes that all values are *strings*.
# Note: Only a limited number of arguments, presumably up to 1000, are supported.
$valuesClauseArgs = $branches | ForEach-Object {
  # Create a list of single-quoted property values enclosed in parentheses.
  "('" + ($_.psobject.properties.Value -join "', '") + "', '$reported')"
}

# Synthesize and output the full SQL statement
@"
INSERT INTO Branch 
  (ID,Address,City,State,ZIP,Phone,Fax,Admin,Reviewed) 
VALUES
  $($valuesClauseArgs -join ",`n  ");
"@

With your sample input, the above yields the following string:

INSERT INTO Branch 
  (ID,Address,City,State,ZIP,Phone,Fax,Admin,Reviewed) 
VALUES
  ('100', '1 Elm Street', 'Anytown', 'ST', '23466', '999-123-6543', '999-123-8395', 'Doe, Jane', '09/01/2018 12:00:16'),
  ('101', '4 Main Street', 'Anytown', 'ST', '23456', '999-123-4567', '999-123-4568', 'Mouse, Noni', '09/01/2018 12:00:16');

Collectives™ on Stack Overflow

PowerShell nested object reference

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related