Parse complex json and convert into CSV in Powershell

89 Views Asked by At

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.

0

There are 0 best solutions below