Read JSON file with an array into R dataframe-CodePudding

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.

CodePudding user response：

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