Powershell export nested Json to CSV as row

Question

The Json contains the following structure, I would like to convert the values to rows and then insert the rows into a CSV file using Powershell:

the expected My CSV output would be like the one hereunder. I would like the GUID to be repeated for each URL and other Image attributes. Many thanks for looking into the request and for your help.

CSV Output sample

CSV outout example

enter code here
"_embedded": {
    "assets": [{
        "guid": "49EDBE70-2B28-3AD7-B993-7F68972BA1",
        "images": [{
            "URL": "https://www.rc.com/eqent_images/2020183/thumb/12014855_1.jpg",
            "imageSize": "thumb",
            "imageType": "OTHER",
            "angleNo": 1,
            "photoGuid": "aeb75f64-9005-472-a85a-f857a7b8e27"
        }, {
            "URL": "https://www.rc.com/eqt_images/2020183/largest/12014855_1.jpg",
            "imageSize": "largest",
            "imageType": "OTHER",
            "angleNo": 1,
            "photoGuid": "aeb75f64-9005-47f2-a85a-f857a7b8e27"
        }],
        "_links": {
            "self": {
                "href": "http://www.rc.com/asset-images/guid/49EDBE70-2B9-3AD7-B993-7F670472BA10?imageSize=thumbnail&imageSize=largest&angleNo=1{&imageType}",
                "templated": true
            }
        }
    }, {
        "guid": "D7C5B69C-083C-7598-2762-B57D64B4D82",
        "images": [{
            "URL": "https://www.rc.com/equipment_images/2090183/thumb/12050336_1.jpg",
            "imageSize": "thumb",
            "imageType": "OTHER",
            "angleNo": 1,
            "photoGuid": "11e196a8-02d3-42ae-9620-1a992516a50"
        }, {
            "URL": "https://www.rc.com/eqnt_images/2020183/largest/12050336_1.jpg",
            "imageSize": "largest",
            "imageType": "OTHER",
            "angleNo": 1,
            "photoGuid": "11e196a8-02d3-42ae-9620-1a7f251a950"
        }],
        "_links": {
            "self": {
                "href": "http://www.rc.com/asset-images/guid/D7C8969C-083C-7598-2762-B57D6994D82E?imageSize=thumbnail&imageSize=largest&angleNo=1{&imageType}",
                "templated": true
            }
        }
    }]
},
"_links": {
    "self": {
        "href": "http://www.rc.com/assetdata-imagescall/search/byGUIDSString?imageSize=thumbnail&imageSize=largest&angleNo=1&page=0&size=1500"
    }
},
"page": {
    "size": 1500,
    "totalElements": 2,
    "totalPages": 1,
    "number": 0
}

}

Please read and follow [mcve] rules (pictures are unwelcome). — JosefZ, Jul 31 '20 at 21:39
Does this answer your question? [Flatten a nested JSON with array and filter to CSV](https://stackoverflow.com/questions/62340113/flatten-a-nested-json-with-array-and-filter-to-csv) — JosefZ, Jul 31 '20 at 21:42

score 0 · Accepted Answer · answered Jul 31 '20 at 21:47

Provided you have a correctly formatted JSON called j.json, you could potentially do the following:

# Read JSON as a PowerShell Object
$obj = Get-Content j.json | ConvertFrom-Json

# Loop through each asset as it contains the GUIDs and Images
$output = $obj._embedded.assets | Foreach-Object {
    # Keep track of Guid as we loop through images
    $guid = $_.Guid
    # Loop through the images to find each URL
    # Use calculated property to create GUID property
    $_.Images | Select-Object @{n='GUID';e={$guid}},URL
}

# Convert to CSV on console only. Not needed if outputting to file
$output | ConvertTo-Csv -NoType

# Output to CSV file
$output | Export-Csv -Path output.csv -NoType

See Select-Object for information on calculated properties.

Perfect answer, this works like a charm! This is exactly what I needed. Thanks for the fast response. Just a note : I received the error "ConvertFrom-Json : Invalid object passed in, ':' or '}' expected. (1): {" may be the Json is not in correct format. I modified it '$obj = Get-Content $sourceFilePath -Raw | ConvertFrom-Json' everything else is same. — Vids, Jul 31 '20 at 22:41

Powershell export nested Json to CSV as row

1 Answers1