I am trying to retrieve information from a json file in the form of a table using powershell. The file has the same key at multiple levels with different values(in the form of arrays].
{
"id": "Process_01.00.0005",
"icsProject": {
"version": "01.00.0005",
"percentageComplete": 100,
"modelType": "FREEFORM",
"projectCode": "PROCESS",
"processorCount": {
"MESSAGE_TRACKER": 1,
"TRANSFORMER": 8,
"TYPE_DEFINITIONS": 1,
"ASSIGNMENT": 8,
"FOR": 1,
"CONTENT_BASED_ROUTER": 2,
"ACTIVITY_STREAM_LOGGER": 2,
"SOURCE": 1,
"TARGET": 8
},
"projectStyle": "App Driven Orchestration",
"smartTags": "app:erp, app:atpdatabase, app:ftp, app:rest,style:App Driven Orchestration",
"orchestration": {
"type": "ORCHESTRATION",
"activities": [
{
"type": "RECEIVE",
"normalizedFields": {
"adapterType": "TECHNOLOGY",
"adapterName": "REST",
"adapterPluginID": "rest",
"endpointConfigurationSummary": "Trigger OIC REST Integration: Post /ProcessPayments/",
"adapterEndpointConfiguration": "INBOUND",
"integrationOperationName": "Post",
"outboundInvocationOperationName": "no_data",
"outboundInvocationService": "no_data",
"outboundInvocationProtocol": "no_data",
"outboundInvocationConnectivityMode": "no_data",
"outboundInvocationAuthenticationType": "no_data",
"outboundInvocationTLS": "no_data",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "no_data",
"outboundInvocationFunctionalPattern": "no_data",
"outboundInvocationArtifactType": "no_data",
"outboundInvocationArtifactValue": "no_data",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "no_data",
"inboundInvocationInvocationStyle": "NOTIFICATION",
"inboundInvocationFunctionalPattern": "TRIGGER",
"inboundInvocationProtocol": "REST",
"inboundInvocationTLS": "1_WAY_SSL",
"inboundInvocationAuthenticationType": "MULTI_TOKEN",
"inboundInvocationConnectivityMode": "CLOUD",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "/ProcessPayments/",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "LABEL",
"assignments": [
{
"type": "ASSIGNMENT",
"xpathExpression": "xp20:format-dateTime(string(fn:current-dateTime()),\"[Y0001]-[M01]-[D01]\")\n"
},
{
"type": "ASSIGNMENT",
"xpathExpression": "\"\""
},
{
"type": "ASSIGNMENT",
"xpathExpression": "\"\""
},
{
"type": "ASSIGNMENT",
"xpathExpression": "\"\""
},
{
"type": "ASSIGNMENT",
"xpathExpression": "\"\""
}
]
},
{
"type": "TRANSFORMER",
"sources": [
"rest"
],
"target": "atpdatabase"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "TECHNOLOGY",
"adapterName": "Oracle ATP",
"adapterPluginID": "atpdatabase",
"endpointConfigurationSummary": "Execute Pure SQL Query in Oracle ATP",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "GetRunDates_REQUEST",
"outboundInvocationOperationName": "no_data",
"outboundInvocationService": "no_data",
"outboundInvocationProtocol": "JDBC",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "TECHNOLOGY_CREDENTIALS",
"outboundInvocationTLS": "1_WAY_SSL",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "REAL_TIME",
"outboundInvocationArtifactType": "PURESQL",
"outboundInvocationArtifactValue": "Select * from DATES WHERE RUN_DATE = #DateParam",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "no_data",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "LABEL",
"assignments": [
{
"type": "ASSIGNMENT",
"xpathExpression": "concat(fn:current-time(),\" Number of Payment Runs: \", count($GetRunDates/nsmpr5:GetRunDatesOutputCollection/nsmpr5:GetRunDatesOutput),\" (\",oraext:create-delimited-string($GetRunDates/nsmpr5:GetRunDatesOutputCollection/nsmpr5:GetRunDatesOutput/nsmpr5:PAY_GROUP,\",\"),\")\")"
}
]
},
{
"type": "FOR",
"parallel": "false",
"xpathExpression": "$GetRunDates/nsmpr2:GetRunDatesOutputCollection/nsmpr2:GetRunDatesOutput",
"activities": [
{
"type": "ROUTER",
"routes": [
{
"type": "ROUTER_ROUTE",
"xpathExpression": "$CurrRecord/nsmpr2:GetRunDatesOutput/nsmpr2:CHQ_TMPL_ID = \"\"",
"activities": [
{
"type": "ACTIVITY_STREAM_LOGGER"
}
]
},
{
"type": "ROUTER_ROUTE",
"xpathExpression": "true",
"activities": [
{
"type": "TRANSFORMER",
"sources": [
"rest"
],
"target": "erp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "APPLICATION",
"adapterName": "Oracle ERP Cloud",
"adapterPluginID": "erp",
"endpointConfigurationSummary": "Submit ESS Job Request in ERP Cloud",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "SubmitESSJobRequest",
"outboundInvocationOperationName": "SubmitESSJobRequest",
"outboundInvocationService": "ErpIntegrationService",
"outboundInvocationProtocol": "SOAP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_PASSWORD_TOKEN",
"outboundInvocationTLS": "1_WAY_SSL",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "REAL_TIME",
"outboundInvocationArtifactType": "BUSINESS_SERVICE",
"outboundInvocationArtifactValue": "ErpIntegrationService",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "NO",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "TRANSFORMER",
"sources": [
"rest",
"erp"
],
"target": "erp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "APPLICATION",
"adapterName": "Oracle ERP Cloud",
"adapterPluginID": "erp",
"endpointConfigurationSummary": "Download ESS Job Execution Details in ERP Cloud",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "DownloadESSJobExecutionDetails",
"outboundInvocationOperationName": "DownloadESSJobExecutionDetails",
"outboundInvocationService": "ErpIntegrationService",
"outboundInvocationProtocol": "SOAP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_PASSWORD_TOKEN",
"outboundInvocationTLS": "1_WAY_SSL",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "REAL_TIME",
"outboundInvocationArtifactType": "BUSINESS_SERVICE",
"outboundInvocationArtifactValue": "ErpIntegrationService",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "NO",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "TRANSFORMER",
"sources": [
"rest",
"erp"
],
"target": "ftp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "TECHNOLOGY",
"adapterName": "FTP",
"adapterPluginID": "ftp",
"endpointConfigurationSummary": "Write file to SFTP server in ASCII format",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "Write",
"outboundInvocationOperationName": "no_data",
"outboundInvocationService": "no_data",
"outboundInvocationProtocol": "sFTP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_CREDENTIALS",
"outboundInvocationTLS": "NO_TLS",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "FILE_UPLOAD",
"outboundInvocationArtifactType": "FILE",
"outboundInvocationArtifactValue": "FileName:Payment.pdf,FIPS:No",
"outboundInvocationAttachmentSupport": "ATTACHMENT_UPLOAD",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "no_data",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
}
]
}
]
},
{
"type": "ROUTER",
"routes": [
{
"type": "ROUTER_ROUTE",
"xpathExpression": "$CurrRecord/nsmpr2:GetRunDatesOutput/nsmpr2:EFT_TMPL_ID = \"\"",
"activities": [
{
"type": "ACTIVITY_STREAM_LOGGER"
}
]
},
{
"type": "ROUTER_ROUTE",
"xpathExpression": "true",
"activities": [
{
"type": "TRANSFORMER",
"sources": [
"rest"
],
"target": "erp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "APPLICATION",
"adapterName": "Oracle ERP Cloud",
"adapterPluginID": "erp",
"endpointConfigurationSummary": "Submit ESS Job Request in ERP Cloud",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "SubmitESSJobRequest",
"outboundInvocationOperationName": "SubmitESSJobRequest",
"outboundInvocationService": "ErpIntegrationService",
"outboundInvocationProtocol": "SOAP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_PASSWORD_TOKEN",
"outboundInvocationTLS": "1_WAY_SSL",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "REAL_TIME",
"outboundInvocationArtifactType": "BUSINESS_SERVICE",
"outboundInvocationArtifactValue": "ErpIntegrationService",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "NO",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "TRANSFORMER",
"sources": [
"rest",
"erp"
],
"target": "erp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "APPLICATION",
"adapterName": "Oracle ERP Cloud",
"adapterPluginID": "erp",
"endpointConfigurationSummary": "Download ESS Job Execution Details in ERP Cloud",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "DownloadESSJobExecutionDetails",
"outboundInvocationOperationName": "DownloadESSJobExecutionDetails",
"outboundInvocationService": "ErpIntegrationService",
"outboundInvocationProtocol": "SOAP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_PASSWORD_TOKEN",
"outboundInvocationTLS": "1_WAY_SSL",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "REAL_TIME",
"outboundInvocationArtifactType": "BUSINESS_SERVICE",
"outboundInvocationArtifactValue": "ErpIntegrationService",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "NO",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "TRANSFORMER",
"sources": [
"rest",
"erp"
],
"target": "ftp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "TECHNOLOGY",
"adapterName": "FTP",
"adapterPluginID": "ftp",
"endpointConfigurationSummary": "Write file to SFTP server in binary format",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "Write",
"outboundInvocationOperationName": "no_data",
"outboundInvocationService": "no_data",
"outboundInvocationProtocol": "sFTP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_CREDENTIALS",
"outboundInvocationTLS": "NO_TLS",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "FILE_UPLOAD",
"outboundInvocationArtifactType": "FILE",
"outboundInvocationArtifactValue": "FileName:PaymentEFT.zip,FIPS:No",
"outboundInvocationAttachmentSupport": "ATTACHMENT_UPLOAD",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "no_data",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
}
]
}
]
},
{
"type": "LABEL",
"assignments": [
{
"type": "ASSIGNMENT",
"xpathExpression": "concat($Log_3,fn:current-time(),\" Submitted \",$CurrRecord/nsmpr5:GetRunDatesOutput/nsmpr5:PAY_GROUP,\" - \",$CurrRecord/nsmpr5:GetRunDatesOutput/nsmpr5:RUN_DATE,\"\n\")"
}
]
}
]
},
{
"type": "LABEL",
"assignments": [
{
"type": "ASSIGNMENT",
"xpathExpression": "concat(fn:current-time(),\" Payment Run Finished\")"
}
]
},
{
"type": "TRANSFORMER",
"sources": [
"rest"
],
"target": "ftp"
},
{
"type": "INVOKE",
"normalizedFields": {
"adapterType": "TECHNOLOGY",
"adapterName": "FTP",
"adapterPluginID": "ftp",
"endpointConfigurationSummary": "Write (append) file to SFTP server in ASCII format",
"adapterEndpointConfiguration": "OUTBOUND",
"integrationOperationName": "Write",
"outboundInvocationOperationName": "no_data",
"outboundInvocationService": "no_data",
"outboundInvocationProtocol": "sFTP",
"outboundInvocationConnectivityMode": "CLOUD",
"outboundInvocationAuthenticationType": "USERNAME_CREDENTIALS",
"outboundInvocationTLS": "NO_TLS",
"outboundInvocationOAuthProvider": "no_data",
"outboundInvocationInvocationStyle": "SYNC_REQUEST_RESPONSE",
"outboundInvocationFunctionalPattern": "FILE_UPLOAD",
"outboundInvocationArtifactType": "FILE",
"outboundInvocationArtifactValue": "FileName:OICLogs.csv,FIPS:No",
"outboundInvocationAttachmentSupport": "no_data",
"outboundInvocationPaginatedRetrieval": "no_data",
"outboundInvocationCustomArtifact": "no_data",
"inboundInvocationInvocationStyle": "no_data",
"inboundInvocationFunctionalPattern": "no_data",
"inboundInvocationProtocol": "no_data",
"inboundInvocationTLS": "no_data",
"inboundInvocationAuthenticationType": "no_data",
"inboundInvocationConnectivityMode": "no_data",
"inboundInvocationArtifactType": "no_data",
"inboundInvocationArtifactValue": "no_data",
"inboundInvocationAttachmentSupport": "no_data",
"inboundInvocationCustomArtifact": "no_data"
}
},
{
"type": "STOP"
}
]
},
"metrics": {
"actions": {
"parallelForEach": []
}
}
}
}
I have come up with the following code to get to them individually but this path can change in other json files.
$ActivityTypes = Get-Content -Path $pathToJsonFile | ConvertFrom-Json
$ActivityTypes.icsProject.orchestration.activities | ? type -eq 'RECEIVE' | Select-Object -expand normalizedFields
$ActivityTypes.icsProject.orchestration.activities | ? type -eq 'LABEL' | Select-Object -expand assignments
$ActivityTypes.icsProject.orchestration.activities | ? type -eq 'TRANSFORMER' | Select-Object -expand sources
$ActivityTypes.icsProject.orchestration.activities | ? type -eq 'INVOKE' | Select-Object -expand normalizedFields
$ActivityTypes.icsProject.orchestration.activities | ? type -eq 'FOR' | Select-Object -expand activities | Select-Object -expand routes | Select-Object -expand activities | ? type -eq 'INVOKE' | Select-Object -expand normalizedFields
$ActivityTypes.icsProject.orchestration.activities | ? type -eq 'FOR' | Select-Object -expand activities | Select-Object -expand routes | Select-Object -expand activities | ? type -eq 'RECEIVE' | Select-Object -expand normalizedFields
Is there a way to get to this normalizedFields key at the least no matter the path.
At the end I am trying to get to this. Name and value given below are what I get when i expand normalizedFields
