I have JSON including multiple nested records. I want to add records with comma separated and store it in a CSV file.

JSON Body

{
    "projectVitals": {
        "productName": "Enterprise",
        "name": "WhiteSource Bolt",
        "token": "61a48eab05356f149828c0e",
        "creationDate": "2022-10-17 09:08:46",
        "lastUpdatedDate": "2023-01-25 06:37:32"
    },
    "libraries": [
{
            "keyUuid": "a89b-40759d783dc3",
            "keyId": 145110423,
            "type": "NUGET_PACKAGE_MODULE",
            "languages": "Nuget",
            "references": {
                "url": "https://api.nuget.org/packages/system.text.encodings.web.5.0.1.nupkg",
                "homePage": "https://github.com/dotnet/runtime",
                "genericPackageIndex": "https://api.nuget.org/packages/System.Text.Encodings.Web/5.0.1"
            },
            "matchType": "SHA1",
            "sha1": "05cd84c678cddd1de0c",
            "name": "system.text.encodings.web.5.0.1.nupkg",
            "artifactId": "system.text.encodings.web.5.0.1.nupkg",
            "version": "5.0.1",
            "groupId": "System.Text.Encodings.Web",
            "licenses": [
                {
                    "name": "MIT",
                    "url": "http://www.opensource.org/licenses/MIT",
                    "profileInfo": {
                        "copyrightRiskScore": "THREE",
                        "patentRiskScore": "ONE",
                        "copyleft": "NO",
                        "royaltyFree": "YES"
                    },
                    "references": [
                        {
                            "referenceType": "NuGet package (details available in nuget gallery)",
                            "reference": "https://index.whitesourcesoftware.com/gri/app/reader/resource/content/asString/33131621-c9e5-4c87-ac1d-b988bbef1e0a"
                        }
                    ]
                }
            ],
            "vulnerabilities": []
        },
        {
            "keyUuid": "936f-5daddbcc37b2",
            "keyId": 69037902,
            "type": "DOT_NET_AS_GENERIC_RESOURCE",
            "languages": ".NET",
            "references": {
                "url": "https://api.nuget.org/packages/system.runtime.interopservices.runtimeinformation.4.3.0.nupkg",
                "genericPackageIndex": ""
            },
            "matchType": "SHA1",
            "sha1": "32d3122a48aa379904",
            "name": "System.Runtime.InteropServices.RuntimeInformation-4.6.24705.01.dll",
            "artifactId": "System.Runtime.InteropServices.RuntimeInformation-4.6.24705.01.dll",
            "version": "4.6.24705.01",
            "groupId": "System.Runtime.InteropServices.RuntimeInformation",
            "licenses": [
                {
                    "name": "Microsoft .NET Library",
                    "url": "http://microsoft.com/web/webpi/eula/aspnetcomponent_rtw_enu.htm",
                    "riskLevel": "unknown",
                    "references": [
                        {
                            "referenceType": "Details available in GitHub repository",
                            "reference": "https://dot.net/"
                        },
                        {
                            "referenceType": "Details available in GitHub repository",
                            "reference": "https://dotnet.microsoft.com/"
                        }
                    ]
                },
                {
                    "name": "MIT",
                    "url": "http://www.opensource.org/licenses/MIT",
                    "profileInfo": {
                        "copyrightRiskScore": "THREE",
                        "patentRiskScore": "ONE",
                        "copyleft": "NO",
                        "royaltyFree": "YES"
                    },
                    "references": [
                        {
                            "referenceType": "Details available in GitHub repository",
                            "reference": "https://dot.net/"
                        }
                    ]
                }
            ],
            "vulnerabilities": []
        }
            ]
}

Powershell Script

$pathToInputJsonFile = "C:\Users\abc\Downloads\test.json"
$pathToOutputCSVFile = "C:\Users\abc\Downloads\License3.csv"
$jsonFileContent = Get-Content -Raw -Path $pathToInputJsonFile | Out-String | ConvertFrom-Json
$libraries = $jsonFileContent.libraries
foreach($obj in $libraries)
{   
    $LibraryName = $obj.name
    $LibraryVersion = $obj.version 
    $LibraryType = $obj.type
    $LibraryLanguage = $obj.languages
    $LibraryURL = $obj.references.url
    $LicenseName = $obj.licenses.name
    $LicenseURL = $obj.licenses.url

    [PSCustomObject]@{
       LibraryName = $LibraryName
       LibraryVersion = $LibraryVersion 
       LibraryType = $LibraryType
       LibraryLanguage = $LibraryLanguage
       LibraryURL = $LibraryURL
       LicenseName = $LicenseName
       LicenseURL = $LicenseURL
       } | Export-Csv $pathToOutputCSVFile -notype -Append 
} 

Actual Result

Expected Result

CodePudding user response：

Use the -join operator to join 1 or more strings together with a given separator:

[PSCustomObject]@{
    # ...
    LicenseName = $LicenseName -join ', '
    LicenseURL = $LicenseURL -join ', '
}