Powershell: l parsing nested JSON

Question

I've been working with PowerShell to retrieves the results of an API call to Brightpearl as a JSON string:

I want to retrieve just the section called results....

"results":[[1,1,207,4,3,"2014-09-18T14:15:50.000-04:00",4,"#1014",2],[2,1,207,1,3,"2014-09-29T13:20:52.000-04:00",4,"#1015",2],[3,1,207,1,3,"2014-09-29T13:25:39.000-04:00",4,"#1016",2]]}

Using ConvertFrom-Json($BpResults.Content), I get only the outer JSON....

PS C:> convertfrom-json $BPOrders.Content

response                                                         reference                                                       
--------                                                         ---------                                                       
@{metaData=; results=System.Object[]}                            @{orderTypeNames=; orderPaymentStatusNames=; orderStatusNames...

I think maybe some of the rest would be obtainable by digging into the System.Object[]...but can't figure out the syntax to access that. Ideally, I want to take the results to write out to .CSV file.

Any ideas?

"Original": {
    "response": {
        "metaData": {
            "resultsAvailable": 3,
            "resultsReturned": 3,
            "firstResult": 1,
            "lastResult": 3,
            "columns": [
                {
                    "name": "orderId",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "IDSET",
                    "required": false
                },
                {
                    "name": "orderTypeId",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "INTEGER",
                    "referenceData": [
                        "orderTypeNames"
                    ],
                    "required": false
                },
                {
                    "name": "contactId",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "INTEGER",
                    "required": false
                },
                {
                    "name": "orderStatusId",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "INTEGER",
                    "referenceData": [
                        "orderStatusNames"
                    ],
                    "required": false
                },
                {
                    "name": "orderStockStatusId",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "INTEGER",
                    "referenceData": [
                        "orderStockStatusNames"
                    ],
                    "required": false
                },
                {
                    "name": "createdOn",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "PERIOD",
                    "required": false
                },
                {
                    "name": "createdById",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "INTEGER",
                    "required": false
                },
                {
                    "name": "customerRef",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "STRING",
                    "required": false
                },
                {
                    "name": "orderPaymentStatusId",
                    "sortable": true,
                    "filterable": true,
                    "reportDataType": "INTEGER",
                    "referenceData": [
                        "orderPaymentStatusNames"
                    ],
                    "required": false
                }
            ],
            "sorting": [
                {
                    "filterable": {
                        "name": "orderId",
                        "sortable": true,
                        "filterable": true,
                        "reportDataType": "IDSET",
                        "required": false
                    },
                    "direction": "ASC"
                }
            ]
        },
        "results": [
            [
                1,
                1,
                207,
                4,
                3,
                "2014-09-18T14: 15: 50.000-04: 00",
                4,
                "#1014",
                2
            ],
            [
                2,
                1,
                207,
                1,
                3,
                "2014-09-29T13: 20: 52.000-04: 00",
                4,
                "#1015",
                2
            ],
            [
                3,
                1,
                207,
                1,
                3,
                "2014-09-29T13: 25: 39.000-04: 00",
                4,
                "#1016",
                2
            ]
        ]
    },
    "reference": {
        "orderTypeNames": {
            "1": "SALES_ORDER"
        },
        "orderPaymentStatusNames": {
            "2": "PARTIALLY_PAID"
        },
        "orderStatusNames": {
            "1": "Draft/Quote",
            "4": "Invoiced"
        },
        "orderStockStatusNames": {
            "3": "Allfulfilled"
        }
    }
}

Answer 1

ConvertFrom-Json $JSONObject

then you can address them like:

$JSONObject.response.results

Answer 2

ConvertFrom-Json returns an object that you can inspect with Select-Object.