Combining Nested Json using PowerShell

Question

I have the following Json script:

{
"merchant_info": {
    "email": "[email protected]",
    "first_name": "David",
    "last_name": "Larusso",
    "business_name": "Mitchell & Murray",
    "phone": {
        "country_code": "001",
        "national_number": "4085551234"
    },
    "address": {
        "line1": "1234 First Street",
        "city": "Anytown",
        "state": "CA",
        "postal_code": "98765",
        "country_code": "US"
    }
},
"billing_info": [{
        "email": "[email protected]",
        "first_name": "Stephanie",
        "last_name": "Meyers"
    }
],
"shipping_info": {
    "first_name": "Stephanie",
    "last_name": "Meyers",
    "address": {
        "line1": "1234 Main Street",
        "city": "Anytown",
        "state": "CA",
        "postal_code": "98765",
        "country_code": "US"
    }
},
"items": [{
        "name": "Zoom System wireless headphones",
        "quantity": 2,
        "unit_price": {
            "currency": "USD",
            "value": "120"
        },
        "tax": {
            "name": "Tax",
            "percent": 8
        }
    }, {
        "name": "Bluetooth speaker",
        "quantity": 1,
        "unit_price": {
            "currency": "USD",
            "value": "145"
        },
        "tax": {
            "name": "Tax",
            "percent": 8
        }
    }
],
"discount": {
    "percent": 1
},
"shipping_cost": {
    "amount": {
        "currency": "USD",
        "value": "10"
    }
},
"note": "Thank you for your business.",
"terms": "No refunds after 30 days."
}

And I want to use PowerShell to get the following Record and export it to CSV:

So far I created the following Script:

$JsonFile = "C:\Users\me\Documents\myfile.json"

$OutputFile = "C:\Users\me\Documents\newtext.csv"

Get-Content -Path $OutputFile
$json = ConvertFrom-Json (Get-Content $JsonFile -Raw)
$json.merchant_info | Select "first_name","last_name",@{Label = "phone"; Expression = {$_.phone.national_number}} | 
Export-Csv $OutputFile -NoTypeInformation

I am able to bring values from (Merchant_info, Shipping_info, item) separetely but how do I bring it all in combined like in my screen shot above.

TessellatingHeckler · Accepted Answer · 2018-07-28 08:55:18Z

2

but how do I bring it all in combined like in my screen shot above.

We can only guess; assuming this entire json block is one order, with one merchant and one customer, but multiple items, then each row is an item. So start with that as the input:

Create an output record (PSCustomObject) with the repeated data, and then the individual item data:

$json.items | ForEach-Object {

    [PSCustomObject]@{
        MerchantInfoFirstName   = $json.merchant_info.first_name
        MerchantInfoLastName    = $json.merchant_info.last_name
        MerchantInfoPhoneNumber = $json.merchant_info.phone.national_number
        ShippingInfoFirstName   = $json.shipping_info.first_name
        ShippingInfoLastName    = $json.shipping_info.last_name

        ItemName = $_.name
        ItemQuantity = $_.quantity

    }

} | Export-Csv ... etc.

edited Jul 28, 2018 at 8:55

user6811411

answered Jul 27, 2018 at 23:55

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

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

1 Comment

user6811411 Over a year ago

@GVal if Excel is the destination anyway you can use | Export-Excel -AutoSize .\NewText.xlsx -Show with DougFinke's famous ImportExcel Module to get this output

Collectives™ on Stack Overflow

Combining Nested Json using PowerShell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related