0

I am new to Powershell.

I have a gz zip file which consists one json will have json data in the below format.

{
"results": [{
    "_id": "1",
    "AccountPartyRelationshipList": {
        "AccountPartyRelationship": [{
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }, {
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }]
    },
    "AlternateIdList": {
        "AlternateId": [{
            "value": "A",
            "name": "B"
        }, {
            "value": "C",
            "name": "D"
        }]
    },
    "SourceInfo": {
        "AccountClosedDate": "A",
        "SystemCSIID": "B",
        "SourceName": "C",
        "AccountLastUpdateDate": "D",
        "SourceCSIID": "E",
        "AccountOpenDate": "F",
        "SystemName": "G"
    },
    "CustomerAccount": {
        "Status": "CLOSED",
        "Address": [{
            "AddressId": "A",
            "AddressType": ["B"],
            "FormatCode": "C",
            "FreeFormatAddress": "D"
        }]
    }
}, {
    "_id": "2",
    "AccountPartyRelationshipList": {
        "AccountPartyRelationship": [{
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }, {
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }]
    },
    "AlternateIdList": {
        "AlternateId": [{
            "value": "A",
            "name": "B"
        }, {
            "value": "C",
            "name": "D"
        }]
    },
    "SourceInfo": {
        "AccountClosedDate": "A",
        "SystemCSIID": "B",
        "SourceName": "B",
        "AccountLastUpdateDate": "C",
        "SourceCSIID": "D",
        "AccountOpenDate": "E",
        "SystemName": "F"
    },
    "CustomerAccount": {
        "Status": "CLOSED",
        "Address": [{
            "AddressId": "A",
            "AddressType": ["B"],
            "FormatCode": "C",
            "FreeFormatAddress": "D"
        }]
    }

}]}{
"results": [{
    "_id": "3",
    "AccountPartyRelationshipList": {
        "AccountPartyRelationship": [{
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }, {
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }]
    },
    "AlternateIdList": {
        "AlternateId": [{
            "value": "A",
            "name": "B"
        }, {
            "value": "C",
            "name": "D"
        }]
    },
    "SourceInfo": {
        "AccountClosedDate": "A",
        "SystemCSIID": "B",
        "SourceName": "C",
        "AccountLastUpdateDate": "D",
        "SourceCSIID": "E",
        "AccountOpenDate": "F",
        "SystemName": "G"
    },
    "CustomerAccount": {
        "Status": "CLOSED",
        "Address": [{
            "AddressId": A,
            "AddressType": ["B"],
            "FormatCode": "C",
            "FreeFormatAddress": "D"
        }]
    }
}, {
    "_id": "4",
    "AccountPartyRelationshipList": {
        "AccountPartyRelationship": [{
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }, {
            "PartyIdValue": "A",
            "SourceSystem": "B",
            "PartyIdType": "C",
            "RelationshipType": "D",
            "PartyId": "E"
        }]
    },
    "AlternateIdList": {
        "AlternateId": [{
            "value": "A",
            "name": "B"
        }, {
            "value": "A",
            "name": "B"
        }, {
            "value": "C",
            "name": "D"
        }]
    },
    "SourceInfo": {
        "AccountClosedDate": "A",
        "SystemCSIID": "B",
        "SourceName": "B",
        "AccountLastUpdateDate": "C",
        "SourceCSIID": "D",
        "AccountOpenDate": "E",
        "SystemName": "F"
    },
    "CustomerAccount": {
        "Status": "CLOSED",
        "Address": [{
            "AddressId": "A",
            "AddressType": ["B"],
            "FormatCode": "C",
            "FreeFormatAddress": "D"
        }]
    }

}]}

I know we can find many solutions here in stackoverflow, but as I am new to powershell and the json looks pretty complex for me.

I request to help me on parsing the above json format and make it to a csv data.

The output of CSV and the columns should be as below mentioned list.

Name 
_id 
AccountPartyRelationship.PartyIdValue    
AccountPartyRelationship.SourceSystem   
AccountPartyRelationship.PartyIdType
AccountPartyRelationship.RelationshipType
AccountPartyRelationship.PartyId
AlternateIdList.AlternateId.value
AlternateIdList.AlternateId.name    
SourceInfo.AccountClosedDate
SourceInfo.SystemCSIID
SourceInfo.SourceName
SourceInfo.AccountLastUpdateDate    
SourceInfo.SourceCSIID
SourceInfo.AccountOpenDate
SourceInfo.SystemName
CustomerAccount.Status
CustomerAccount.Address.AddressId
CustomerAccount.Address.AddressType
CustomerAccount.Address.FormatCode   
CustomerAccount.Address.FreeFormatAddress

Thanks, Mohan V.

Mohan.V
  • 141
  • 1
  • 1
  • 10
  • 2
    CSV files are _flat_, whereas the data you've presented here is _nested_ - can you show us what your desired CSV output file would look like, based on the input you've provided? – Mathias R. Jessen Aug 22 '22 at 14:23
  • 1
    See: [Convert nested JSON array into separate columns in CSV file](https://stackoverflow.com/a/46081131/1701026) – iRon Aug 22 '22 at 14:32
  • @MathiasR.Jessen thanks for the comment..I have added the expected output please check and let me know if you need any info. – Mohan.V Aug 22 '22 at 14:44
  • @iRon thanks for the suggestion. i have tried that and it results as each _id and their inner fields are added to new column. I would like to have them as new row instead of new column. – Mohan.V Aug 22 '22 at 15:04
  • Shouldn't it be `AccountPartyRelationshipList.AccountPartyRelationship` in the CSV? I mean that would be more consistent compared to `AlternateIdList.AlternateId`. Also, you just want a single column containing the unique paths? The JSON values are to be ignored? – zett42 Aug 22 '22 at 19:07

0 Answers0