rjsondataframetidyversejsonlite

Read JSON file with an array into R dataframe


I need to turn the following json file into an R dataframe.

[
  {
    "assessment": "AWS - Application Development",
    "sessions": [
      {
        "percentile": 0.0739025,
        "date_completed": "2021-10-20T20:58:33+00:00"
      },
      {
        "percentile": 0.0745573,
        "date_completed": "2021-07-14T09:58:17+00:00"
      }
    ]
  },
  {
    "assessment": "AWS - Big Data",
    "sessions": [
      {
        "percentile": 0.075069,
        "date_completed": "2021-07-14T08:58:24+00:00"
      },
      {
        "percentile": 0.426306,
        "date_completed": "2021-10-20T19:46:42+00:00"
      }
    ]
  }
]

The dataframe should have a row for each record of sessions, like so:

|assessment                   |percentile|date_completed           | 
|-----------------------------|----------|-------------------------|
|AWS - Application Development|0.0739025 |2021-10-20T20:58:33+00:00|
|AWS - Application Development|0.0745573 |2021-07-14T09:58:17+00:00|
|AWS - Big Data               |0.075069  |2021-07-14T08:58:24+00:00|
|AWS - Big Data               |0.426306  |2021-10-20T19:46:42+00:00|

How can I do this? If possible, I'd like to use the jsonlite an tidyverse libraries.


Solution

  • Does this work for you:

    txt <- '[
      {
        "assessment": "AWS - Application Development",
        "sessions": [
          {
            "percentile": 0.0739025,
            "date_completed": "2021-10-20T20:58:33+00:00"
          },
          {
            "percentile": 0.0745573,
            "date_completed": "2021-07-14T09:58:17+00:00"
          }
        ]
      },
      {
        "assessment": "AWS - Big Data",
        "sessions": [
          {
            "percentile": 0.075069,
            "date_completed": "2021-07-14T08:58:24+00:00"
          },
          {
            "percentile": 0.426306,
            "date_completed": "2021-10-20T19:46:42+00:00"
          }
        ]
      }
    ]'
    
    
    library('jsonlite')
    unnest(fromJSON(txt))
    
    # A tibble: 4 × 3
      assessment                    percentile date_completed           
      <chr>                              <dbl> <chr>                    
    1 AWS - Application Development     0.0739 2021-10-20T20:58:33+00:00
    2 AWS - Application Development     0.0746 2021-07-14T09:58:17+00:00
    3 AWS - Big Data                    0.0751 2021-07-14T08:58:24+00:00
    4 AWS - Big Data                    0.426  2021-10-20T19:46:42+00:00