Convert a relation SQL table into JSON using R

Question

I'm attempting to convert a SQL table into JSON nested format using R. I can import the table into R easily. The problem is now getting all the parent-children relation in the JSON format. I already manage to have some kind of JSON output, but only list all parent individuals with respective children in the following form: ( i will just list the first 6 lines of the table)

[
  [
    {
      "name": ["a"],
      "children": ["b"]
    },
    {
      "name": ["b"],
      "children": ["c"]
    },
    {
      "name": ["c"],
      "children": ["d"]
    },
    {
      "name": ["b"],
      "children": ["e"]
    },
    {
      "name": ["e"],
      "children": ["f"]
    }
  ]
] 

with

library(RJSONIO)
orgTable=orgTable[,c("Manager","ID")]
makeList<-function(x){
    if(ncol(x)>2){
        listSplit = split(x[-1],x[1],drop=T)
        lapply(names(listSplit),function(y){list(name=y,children=makeList(listSplit[[y]]))})
    }
    else{
        lapply(seq(nrow(x[1])),function(y){list(name=x[,1][y],children=x[,2][y])})
    }
}

jsonOut = toJSON(list(makeList(orgTable[2:6,])),pretty=TRUE)
cat(jsonOut)

The SQL table is:

Parent     Children
a          b
b          c
c          d
b          e
e          f

What I would like to obtain would be something like:

   {
    "name": "a",
    "children": [
        {
        "name": "b",
        "children": [
            {
            "name": "c",
            "children": [
                {
                "name": "d"
                }
                ]
            },
            {
            "name": "e",
            "children": [
                {
                "name": "f"
                }
                ]
            }
        ]
    }
    ]
}

Can anyone help? If possible if I could add also info from a third column would be perfect.

The code I present comes from this post but was slightly tweaked for my needs. I'm still quite new at R so please bear with me.

Thanks in advance

Answer 1

I'll start with a simple recursive solution that only gets parent/children with no other attributes.

#get some help from igraph
library(igraph)

df <- read.table(
  textConnection(
'
Parent     Children
a          b
b          c
c          d
b          e
e          f
' )
  , header = TRUE
  , stringsAsFactors = FALSE
)

el_in <- get.adjlist(graph.data.frame(df),mode="in")
# fill in name/id instead of number
el_in <- lapply(
  el_in,
  function(x){
    names(el_in)[x]
  }
)

get_children <- function( adjlist, node ){
  names(Filter(function(x) x==node,unlist(el_in)))
}

recurse_tree <- function( adjlist, node = NULL ){
  #  start at root if undefined
  #    root will be the node with no in
  if(is.null(node)) node <- names(Filter(function(x)length(x)==0,adjlist))

  children <- get_children( adjlist, node )
  if(length(children)>0){
    list(
      name = node
      ,children = lapply(
        children
        ,function(x){
          recurse_tree( adjlist, x )
        }
      )
    )
  } else {
    list(
      name = node
    )
  }
}


jsonlite::toJSON(
  recurse_tree( el_in ),
  auto_unbox=T
)

Probably not the most efficient, but here is a solution using data.tree and igraph to build our hierarchy.

#get some help from the relatively new data.tree
#devtools::install_github("gluc/data.tree")
library(data.tree)
#get some help from igraph
library(igraph)

df <- read.table(
  textConnection(
'
Parent     Children
a          b
b          c
c          d
b          e
e          f
' )
  , header = TRUE
  , stringsAsFactors = FALSE
)

#this will be our paths for data.tree
build_path <- function(df){
  g <- graph.data.frame(df)
  #get an adjacency list of all in
  el_in <- get.adjlist( g, mode="in" )
  tree <- lapply(el_in,function(x){""})
  lapply(
    1:length(el_in)
    ,function(n){
      id <- names(el_in)[n]
      x <- el_in[[n]]
      if(length(x)>0){
        tree[[id]] <<- paste0(
          tree[[el_in[[id]]]],
          "/",
          id
        )
      } else {
        tree[[id]] <<- id
      }
    }
  )
  return(unlist(tree))
}


tree <- as.Node(data.frame(
  pathString = build_path(df),
  # have the ability to specify values
  #  if not then just set NA
  value = NA,
  stringsAsFactors = F
))

jsonlite::toJSON(
  as.list( tree, mode="explicit", unname = TRUE),
  auto_unbox = TRUE
)

# as a test let's build a random tree with igraph
tree_grf <- graph.tree(n=10,children=3)
plot(tree_grf)
tree <- as.Node(data.frame(
  pathString = build_path(
    get.data.frame(tree_grf,what="edges")
  ),
  # have the ability to specify values
  #  if not then just set NA
  value = NA,
  stringsAsFactors = F
))