Nested json extract from powershell

Question

I need to show id_products as a result, the remaining data I have a photo id and url pictures. Below is the code which I extract data from

$files_ro = "products.csv"
$Ident = Import-Csv -Path $files_ro -Header id_product | select-object -skip 1
foreach ($idka in $ident)
{
 $idp = $idka.id_product
 $request_n = "http://api.url/"+ $idp +"" 
 foreach($d1 in $request_n)
         {
     Invoke-WebRequest $d1 |     
     ConvertFrom-Json | 
     Select-Object -Expand data |
     Select -expand extended_info |
     select -expand images |
     Select id,url
 } 
}

files - product.csv

"id_product"
"21221"
"23526"
"23525"
"24074"
"21302"
"24372"
"21272"
"21783"
"27268"
"21776"

json

{
 data: {
  id: 21221,
   extended_info: {
     images: [
                {
                    id: 34380,
                    url: photos1.jpg
                },
                {
                    id: 34381,
                    url: photos2.jpg
                },
                {
                    id: 34382,
                    url: photos3.jpg
                }
         ],
         }
     }
 }

I would like it to look like this:

id_product,id(images), url
21221,34380,photos1.jpg
21221,34381,photos2.jpg
21221,34382,photos3.jpg

You can help me somehow ?

Martin Brandl · Accepted Answer · 2019-05-24 07:47:56Z

2

Your provided JSON is not valid. However, I would use a PSCustomObject to create the desired result:

$json = @'
{
    "data": {
        "id": 21221,
        "extended_info": {
            "images": [{
                    "id": 34380,
                    "url": "photos1.jpg"
                }, {
                    "id": 34381,
                    "url": "photos2.jpg"
                }, {
                    "id": 34382,
                    "url": "photos3.jpg"
                }
            ]
        }
    }
}
'@ | ConvertFrom-Json

$json.data.extended_info.images | ForEach-Object {
    [PSCustomObject]@{
        id_product = $json.data.id
        "id(images)" = $_.id
        url = $_.url
    }
}

Output:

id_product id(images) url
---------- ---------- ---
     21221      34380 photos1.jpg
     21221      34381 photos2.jpg
     21221      34382 photos3.jpg

To convert the result to CSV, just add | ConvertTo-Csv -NoTypeInformation after the last curly bracket to get the following output:

"id_product","id(images)","url"
"21221","34380","photos1.jpg"
"21221","34381","photos2.jpg"
"21221","34382","photos3.jpg"

answered May 24, 2019 at 7:47

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

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

2 Comments

mario Over a year ago

Only I have to download only for those who are in produkct.csv so I have to use the mechanism to get the information for which product I need to download data. as for json (I had to arrange for the query) I could change something. As I'm just getting information about the picture, I have no problem

mario Over a year ago

I tried to do it for a specific product but something does not work when downloading from api. I have corrected the code does not work ``` $request = 'api.url/21221' Invoke-WebRequest $request | ConvertFrom-Json | $request.data.extended_info.images | ForEach-Object { [PSCustomObject]@{ id_product = $request.data.id_product id_image = $_.id url = $_.url } } ```

user6811411 · Accepted Answer · 2019-05-24 12:43:44Z

As your product.csv already has the same header it isn't necessary to supply one and then skip the first line.
Your single URL $request_n also doesn't need to be iterated with a foreach
I suggest to store the result from the webrequest and convertedfrom-json into var $Json and proceed with Martin Brandls good answer.

## Q:\Test\2019\05\24\SO_56287843.ps1
$files_ro = "products.csv"
$Ident = Import-Csv -Path $files_ro

$Data = foreach ($idka in $ident){
    $request_n = "http://api.url/{0}" -f $idka.id_product 
    $Json = Invoke-WebRequest $request_n | ConvertFrom-Json 

    # inserted code from Martin Brandl's good answer
    $Json.data.extended_info.images | ForEach-Object {
        [PSCustomObject]@{
            id_product   = $json.data.id
            "id(images)" = $_.id
            url          = $_.url
        }
    }
}

$Data 
$Data | Export-Csv ProductImages.csv -NoTypeInformation 
#$Data | Out-Gridview

Collectives™ on Stack Overflow

Nested json extract from powershell

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related