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 minimal reproducible example rules (pictures are unwelcome). — JosefZ
– JosefZ, Commented Jul 31, 2020 at 21:39
Does this answer your question? Flatten a nested JSON with array and filter to CSV — JosefZ
– JosefZ, Commented Jul 31, 2020 at 21:42

AdminOfThings · Accepted Answer · 2020-07-31 21:47:06Z

0

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.

answered Jul 31, 2020 at 21:47

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vids Over a year ago

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.

Collectives™ on Stack Overflow

Powershell export nested Json to CSV as row

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related