expand array to string in powershell

Question

Well my English is too bad to explain what i want but i will try

There are some very big multiline string - part of sql query

$q =
     "Values 
        (
            '$($line.'common.uuid')',
            '$($line.'user.user_id')',
            '$($line.'user.ldap.user_attributes.displayName')',
            '$($line.'user.email_address')',
            '$($line.'common.owner')',
            '$($line.'common.current_phone_number')',
            '$($line.'common.current_country_name')',
        )"

That string is very hard to edit Are there any way to do like that in simple way? Or only something like foreach $field in $fields?

$fields = '
common.uuid
user.user_id
user.ldap.user_attributes.displayName
user.email_address
.....'

$q = '(
$line.($fields somehow here)
)'

There can be about 100 lines of values and its much easier to edit that list in $fields instead of $q

Thanks!

How are you querying your SQL db? It sounds like you'll want to parameterize the query before doing anything else... — Mathias R. Jessen, Sep 18 '21 at 12:25
Right, but can you show us the rest of the code? The code that actually executes the query — Mathias R. Jessen, Sep 18 '21 at 14:21

mklement0 · Answer 1 · 2022-03-11T00:23:30.810

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 ("...").

Daniel · Answer 2 · 2021-09-18T16:33:47.293

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 = 'some@email.add'
            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 = 'some_other@email.add'
            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',
        'some@email.add',
        'some owner',
        '222-555-1212',
        'USA'
)
Values (
        'some other uuid',
        'some other userid',
        'Some other User',
        'some_other@email.add',
        'some other owner',
        '222-555-1212',
        'UK'
)

Nice; I had assumed that the code in the question is functional, but `common.uuid` may indeed be a _nested_ property access rather than a single property with an unusual name. — mklement0, Sep 18 '21 at 16:49
@mklement0 You are probably right. That's how it looks judging from the single quotes surrounding the field names. I thought it might be a typo though and went with my assumption. — Daniel, Sep 18 '21 at 16:57

expand array to string in powershell

2 Answers2