expand array to string in powershell

As Mathias R. Jessen points out, it is generally preferable to use a parameterized SQL query than to use string interpolation to bake values into a query string, not least for better security, but also to avoid round-trip conversion to and from strings for non-string values.

If you do want to stick with your string-interpolation approach, here's a solution based on the .psobject intrinsic member, which is a rich source of reflection about any object and allows enumerating its properties via the .Properties collection:

# Sample input object.
$line = [pscustomobject] @{ 
  'common.uuid' = '1-2-3'; 
  'user.user_id' = 'jdoe'; 
}

# Use string interpolation to embed all of the object's property values.
$q = @"
Values (
  $($line.psobject.Properties.Value.ForEach({ "'" + $_ + "'" }) -join ",`n  ")
)
"@

This solution makes two assumptions:

• You're interested in the values of all properties of the input object.

• You do not need nested property access (in the code in your question, a property access such as .'user.user_id' is a single property access targeting a property named verbatim user.user_id, even though the name itself suggests a property path (nested access)).

If these assumptions don't hold, see Daniel's helpful answer.

Note that, as in your question, all values are represented as strings ('...') in the resulting query fragment.

If you choose a parameterized-query approach instead, you can similarly use $line.psobject.Properties.Value to obtain an array of all property values. (Note that even though .Value is applied to a collection of elements, PowerShell conveniently collects its elements' .Value values, which is a feature known as member-access enumeration).

The above stores the following in variable $q:

Values (
  '1-2-3',
  'jdoe'
)

See this answer for an overview of PowerShell's string interpolation via expandable strings ("...").

As usual, mklement0 offers a great approach looping through the object's properties directly, though you may have to rearrange the sql statement to accommodate the order of the properties and/or add some additional filtering if you are not interested in all of the properties on the object.

I will go ahead and post my solution as it may offer some additional insight to how this can be done.

# Example object data
$lines = @(
    @{
        common = @{
            uuid                 = 'some uuid'
            owner                = 'some owner'
            current_phone_number = '222-555-1212'
            current_country_name = 'USA'
        }
        user   = @{
            user_id       = 'some userid'
            email_address = '[email protected]'
            ldap          = @{user_attributes = @{displayName = 'Some User' } }
        }
    }
    @{
        common = @{
            uuid                 = 'some other uuid'
            owner                = 'some other owner'
            current_phone_number = '222-555-1212'
            current_country_name = 'UK'
        }
        user   = @{
            user_id       = 'some other userid'
            email_address = '[email protected]'
            ldap          = @{user_attributes = @{displayName = 'Some other User' } }
        }
    }
)

# Create an array of the fields we are interested in, 
# in the order we would like them to appear in the sql statement
$fields = @(
    'common.uuid',
    'user.user_id',
    'user.ldap.user_attributes.displayName',
    'user.email_address',
    'common.owner',
    'common.current_phone_number',
    'common.current_country_name'
)

# loop through the fields forming the line 
# and then join them together using -join
$values = ($fields | ForEach-Object {
        "`t'`$(`$line.$_)'"
    }) -join ',`n'

# add in the rest of the statement
$q = "Values (`n $values `n)"

foreach ($line in $lines) {
    # Use ExpandString to expand the variables inside
    # our $q string
    $ExecutionContext.InvokeCommand.ExpandString($q)
}

Output

Values (
        'some uuid',
        'some userid',
        'Some User',
        '[email protected]',
        'some owner',
        '222-555-1212',
        'USA'
)
Values (
        'some other uuid',
        'some other userid',
        'Some other User',
        '[email protected]',
        'some other owner',
        '222-555-1212',
        'UK'
)