json to data frame

Hi,

I used the post request node to retreive data via an API. The result is an json object which I want to convert to a table. I tried the json to table node, but it only converts the json object to columns not to a table.

The json object looks like this:

{
  "report": {
    "data": [
      {
        "name": "December 1, 2016",
        "year": 2016,
        "month": 12,
        "day": 1,
        "hour": 0,
        "counts": [
          "500"
        ]
      },
      {
        "name": "December 2, 2016",
        "year": 2016,
        "month": 12,
        "day": 2,
        "hour": 0,
        "counts": [
          "200"
        ]
      },

    ],
    "reportSuite": {
      "id": "",
      "name": ""
    },
    "period": "2016-12-01T00:00:00-0800/2016-12-02T00:00:00-0800",
    "elements": null,
    "metrics": [
      {
        "id": "visits",
        "name": "Visits",
        "type": "number",
        "decimals": 0
      }
    ],
    "page": 1,
    "totalPages": 1
  }
}

 

and the table should look like this:

name year month day hour count
December 1, 2016 2016 12 1 0 500
December 2, 2016 2016 12 2 0 200

Do you have an idea how to solve this elegantly? Different API requests may result in different json object, hence, more rows or mor columns.

 

Thanks

Andra

Hi Andra,

It appears that you are only interested in the content of the data array?

In that case, you will need to use a JSON Path node first to extract those entries before using JSON to Table

Hope that helps!

Cheers,

Roland

Hi Roland,

thanks for your answer! I used the JSON Path node to extract the data array, and it works now. However, I need several nodes for this process to work. JSON Path/JSON to Table/Column Filter/Transpose/Json to Table.

It's ok for me for now, but if you know a shorter way for the transformation, from json to dataframe, I would appreciate it!

 

Thank you

Andra