How to flatten a deeply and irregularly nested list/dataframe/JSON in R

Question

I would like convert json file to dataframe (without any nested list inside a dataframe)

temp <- jsonlite::fromJSON(txt ="https://unstats.un.org/SDGAPI/v1/sdg/GeoArea/Tree")

Answer 1

Unnesting recursively:

library(tidyr)
library(purrr)
library(dplyr)

recursive_unnest <- function(.data) {
  # Exit condition: no more 'children' list-column
  if (!"children" %in% names(.data) || !is.list(.data[["children"]])) return(.data)
  x <- unnest(.data)
  # Minor clean-up to make unnest work: replace NULLs with empty data frames
  x <- mutate_if(x, is.list, 
                 ~ map_if(.x, ~ is.null(.x) || identical(.x, list()), ~ data.frame(geoAreaCode = NA)))
  Recall(x)
}
recursive_unnest(temp)

# Observations: 489
# Variables: 16
# $ geoAreaCode  <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,...
# $ geoAreaName  <chr> "World", "World", "World", "World", "World", "World", "World", "Worl...
# $ type         <chr> "Region", "Region", "Region", "Region", "Region", "Region", "Region"...
# $ geoAreaCode1 <int> 10, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2...
# $ geoAreaName1 <chr> "Antarctica", "Africa", "Africa", "Africa", "Africa", "Africa", "Afr...
# $ type1        <chr> "Country", "Region", "Region", "Region", "Region", "Region", "Region...
# $ geoAreaCode2 <int> NA, 15, 15, 15, 15, 15, 15, 15, 202, 202, 202, 202, 202, 202, 202, 2...
# $ geoAreaName2 <chr> NA, "Northern Africa", "Northern Africa", "Northern Africa", "Northe...
# $ type2        <chr> NA, "Region", "Region", "Region", "Region", "Region", "Region", "Reg...
# $ geoAreaCode3 <int> NA, 12, 818, 434, 504, 729, 788, 732, 14, 14, 14, 14, 14, 14, 14, 14...
# $ geoAreaName3 <chr> NA, "Algeria", "Egypt", "Libya", "Morocco", "Sudan", "Tunisia", "Wes...
# $ type3        <chr> NA, "Country", "Country", "Country", "Country", "Country", "Country"...
# $ geoAreaCode4 <int> NA, NA, NA, NA, NA, NA, NA, NA, 86, 108, 174, 262, 232, 231, 260, 40...
# $ geoAreaName4 <chr> NA, NA, NA, NA, NA, NA, NA, NA, "British Indian Ocean Territory", "B...
# $ type4        <chr> NA, NA, NA, NA, NA, NA, NA, NA, "Country", "Country", "Country", "Co...
# $ children     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, ...