Author Posts

October 17, 2014 at 7:38 am

Having made substantial progress accessing objects returned by an Invoke-RestMethod and is it possible to dig out items that are nested within a single response?

contactId             : 598
isPrimaryContact      : True
salutation            : 
firstName             : KAREN
lastName                    : MCPHERSON
postAddressIds          : @{DEF=513; DEL=513; BIL=513}
communication           : @{emails=; telephones=; messagingVoips=; websites=}
contactStatus              : @{current=}
relationshipToAccount : @{isSupplier=False; isStaff=False}
marketingDetails         : @{isReceiveEmailNewsletter=True}
financialDetails          : @{priceListId=2; nominalCode=0; creditLimit=0; creditTermDays=0; currencyId=1; discountPercentage=0.00}
assignment               : @{current=}
organisation              : @{organisationId=598; name=ROSENTHAL MONTESSORI ELEMENTARY}
createdByid               : 0
createdOn                 : 2014-03-17T10:02:54.000-04:00
updatedOn                : 2014-03-17T10:02:55.000-04:00
contactTags              : 8

What I'm trying to do is to create a simple name and address file assembled from one or more JSON responses.

With the above, I can pipe to:

 Select-Object contactId, firstName, lastname | ConvertTo.CSV 

and end up with a nicely formatted csv file...

"598","McPherson", "Karen"
"322","Schmoe", "Joe" 

etc. etc. 

I'd like to include the Organization.Name (which is embedded in the hash table in the organization object.

Question 1. Can I dig out the name, so that it appears in my list?

"598","ROSENTHAL MONTESSORI ELEMENTARY", "McPherson", "Karen"
"322","Springfield High School", Schmoe", "Joe" 

I tried

Select-Object organization.name, contactId, firstName, lastname 
and 
Select-Object organization.[1] contactId, firstName, lastname 

but these don't return anything in the organization field.

Question 2,
There is also an address ID contained within the record as a hash table:

 
postAddressIds        : @{DEF=513; DEL=513; BIL=513}

and I want to use the DELivery address ID....which I think need to use to make another JSON request for the address.
Somehow this all needs to get in a loop...

In SQL this would look like:

SELECT contactid, organization, firstName,lastName, address1, address2, city,state zip
FROM contacts, address
WHERE address.contactsid = contacts.contactid

Thanks so much for any ideas.

October 17, 2014 at 7:52 am

You would need to use a constructed property for this, something along these lines:

$properties = @(
    'contactId'
    @{ Name = 'OrganizationName'; Expression = { $_.organisation['name'] } }
    'firstName'
    'lastName'
)

$results | Select-Object $properties | ConvertTo-CSV

The values that you pass to Select-Object's -Property parameter can either be strings, or hashtables with Name / Expression values that allow you to do more complex things.

October 17, 2014 at 8:12 am

Hi, Dave....thanks for your reply.

I ran the code to define the $properties object but still got a blank organization field.

If I just do a printout of the defined $properties object I get:

PS C:\users\larry\powershell> $properties

contactId

Name                           Value                                                                                                           
----                                 -----                                                                                                           
Expression                   $_.organisation['name']                                                                                        
Name                           OrganizationName                                                                                                
firstName
lastName

So, I'm assuming that $results is the original result returned from the Invoke-RestMethod, right?
This is what I'm piping to the SELECT $properties.

contactId             : 598
isPrimaryContact      : True
salutation            : 
firstName             : KAREN
lastName                    : MCPHERSON
postAddressIds          : @{DEF=513; DEL=513; BIL=513}
communication           : @{emails=; telephones=; messagingVoips=; websites=}
contactStatus              : @{current=}
relationshipToAccount : @{isSupplier=False; isStaff=False}
marketingDetails         : @{isReceiveEmailNewsletter=True}
financialDetails          : @{priceListId=2; nominalCode=0; creditLimit=0; creditTermDays=0; currencyId=1; discountPercentage=0.00}
assignment               : @{current=}
organisation              : @{organisationId=598; name=ROSENTHAL MONTESSORI ELEMENTARY}
createdByid               : 0
createdOn                 : 2014-03-17T10:02:54.000-04:00
updatedOn                : 2014-03-17T10:02:55.000-04:00
contactTags              : 8