Which is the best way to flatten nested lists derived from a relational database using tidyverse tools?

Question

I have a nested list which have received from a REST call. The response includes a nested set of lists from an underlying relational database. I want to flatten the list to simplify analysis. I have tried to follow the guidelines in the purrr tutorial but I can't get it to work.

My simplified input

hist1 <- list(field="type", from_string ="issue", to_string="bug")
hist2 <- list(field="status", from_string ="open", to_string="closed")
hist3 <- list(field="type", from_string ="bug", to_string="issue")
issue1 <- list(id="123", created = "2017-11-08", issue_history = list(hist1, hist2))
issue2 <- list(id="124", created = "2017-11-10", issue_history = list(hist1, hist3))
issue <- list(issue1, issue2)

I am looking for an flattened output:

id  created    type   from_string  to_string
123 2017-11-08 type   issue        bug
123 2017-11-08 status open         closed
123 2017-11-10 type   bug          issue

Which is the best way of building scable logic for this?

Best (for me):

• tools from tidyverse
• code that is simple to maintain
• do not have to scale for millions of issues, i.e. performance and memory are not critical elements

Answer 1

Another solution inspired by @Nate's answer:

map_df(issue, as_tibble) %>% 
    mutate(issue_history = map(issue_history, as_tibble)) %>% 
    unnest()

# A tibble: 4 x 5
#     id    created  field from_string to_string
#  <chr>      <chr>  <chr>       <chr>     <chr>
#1   123 2017-11-08   type       issue       bug
#2   123 2017-11-08 status        open    closed
#3   124 2017-11-10   type       issue       bug
#4   124 2017-11-10   type         bug     issue

Answer 2

Not sure if there is a more purrry way of doing this, but it works.

library(tidyverse)

map(issue, as.tibble) %>%
  map_df(~ rowwise(.) %>%
           mutate(issue_history = list(bind_rows(issue_history))) %>%
           unnest() )

# A tibble: 4 x 5
     id    created  field from_string to_string
  <chr>      <chr>  <chr>       <chr>     <chr>
1   123 2017-11-08   type       issue       bug
2   123 2017-11-08 status        open    closed
3   124 2017-11-10   type       issue       bug
4   124 2017-11-10   type         bug     issue