autodetect json fields and use jsonlite to parse them into lists

[zachmayer]

I've encountered data dumps from postgresql with JSON columns in them. It'd be really cool if fread could auto-detect this and use jsonlite to parse those columns into lists.

Right now I'm doing dt[,col := lapply(col, jsonlite::fromJSON)], but it'd be cool if I could skip this step.

[zachmayer]

The use case looks something like this:

library(data.table)
set.seed(1)
N <- 10
dt_list <- data.table(
  id=1:N,
  a=round(runif(N), 4),
  b=round(runif(N), 4),
  c=sample(letters, N, replace=TRUE),
  other=lapply(1:N, function(x) sample(letters, sample(5:10)))
)

dt_json <- copy(dt_list)
dt_json <- dt_json[,other := sapply(other, jsonlite::toJSON)]

Where dt_json looks like this:

> dt_json
    id      a      b c                                     other
 1:  1 0.2655 0.2060 y             ["u","c","r","j","s","n","p"]
 2:  2 0.3721 0.1766 f         ["s","l","u","k","f","b","x","g"]
 3:  3 0.5729 0.6870 q         ["i","q","g","l","y","b","r","x"]
 4:  4 0.9082 0.3841 d ["k","t","x","j","p","i","g","o","d","m"]
 5:  5 0.2017 0.7698 g                     ["w","t","y","k","j"]
 6:  6 0.8984 0.4977 k     ["q","f","d","k","u","m","t","n","g"]
 7:  7 0.9447 0.7176 a             ["q","y","l","x","d","p","j"]
 8:  8 0.6608 0.9919 j         ["a","q","w","n","m","l","t","j"]
 9:  9 0.6291 0.3800 w     ["e","s","c","t","n","l","g","i","j"]
10: 10 0.0618 0.7774 i                 ["x","l","s","u","j","b"]

(other is a character vector)
And dt_list looks like this:

> dt_list
    id      a      b c        other
 1:  1 0.2655 0.2060 y u,c,r,j,s,n,
 2:  2 0.3721 0.1766 f s,l,u,k,f,b,
 3:  3 0.5729 0.6870 q i,q,g,l,y,b,
 4:  4 0.9082 0.3841 d k,t,x,j,p,i,
 5:  5 0.2017 0.7698 g    w,t,y,k,j
 6:  6 0.8984 0.4977 k q,f,d,k,u,m,
 7:  7 0.9447 0.7176 a q,y,l,x,d,p,
 8:  8 0.6608 0.9919 j a,q,w,n,m,l,
 9:  9 0.6291 0.3800 w e,s,c,t,n,l,
10: 10 0.0618 0.7774 i  x,l,s,u,j,b

(other is a list of character vectors)

The idea would be fread would generate dt_list rather than dt_json, e.g.:

f <- tempfile()
write.table(dt_json, f, row.names=FALSE, quote=FALSE)
dt_new <- fread(f)
unlink(f)
identical(dt_list, dt_new) #Should be TRUE

Right now I have to manually run fromJSON:

dt_new[, other := sapply(other, jsonlite::fromJSON)]
identical(dt_list, dt_new) #Is TRUE

Since json objects are pretty analogous to R lists, and data.tables support list columns, I think this would be a really nice feature to have. Even better, when one day you add a fwrite (#580) function to data.table, it can automatically serialize list columns to json strings.

[dselivanov]

@zachmayer , recently I investigated fread internals - see this PR. So from my experience, it will be hard to detect json and parse it, because all parsing is in plain C. And it will be hard to write json parser in C which maps json objects into R lists.
@arunsrinivasan, please, correct me if i'm wrong.

[mattdowle]

Seems related to fread(..., sep2 ). If it's just a case of a starting [, ending ] and optionally some quoting inside the sep2 separated vector, then shouldn't be too bad. I don't know JSON at all.
Anyway, wanted to get the 'sep2' string into this one, for search later.

[zachmayer]

@mattdowle To really do it right, you'd want to use jsonlite::fromJSON, which is already really fast and handles all the edge cases correctly. sep2 would get us part of the way there, though, and would be immensely useful.

That said, dt_new[, other := sapply(other, jsonlite::fromJSON)] seems a little... inefficient to me =D. We already parse 64 bit integers using the bit64 package, so maybe this could be another optional dependency for certain datatypes?

[jangorecki]

@zachmayer I doubt we use bit64 for parsing int64, AFAIU we just return object of such class, but parsing is made by Matt in C. Core fread operates in plain C, so no way to call R API of jsonlite package.

[zachmayer]

There are JSON parsers in C:
https://github.com/DaveGamble/cJSON
https://zserge.com/jsmn.html
https://stackoverflow.com/questions/6673936/parsing-json-using-c

Maybe that's too heavy a dependency to add, but it certainly would be useful to have json support in fread and fwrite.

[MichaelChirico]

It should be possible to include headers from jsonlite:

https://github.com/jeroen/jsonlite/blob/master/src/parse.c

Though I'm not sure what that would mean for pydatatable

[MichaelChirico]

I would say wontfix -- JSON is a completely different data structure, don't think it makes sense to for data.table to reinvent the wheel there (user contributions welcome of course).

For example this simdjson parser came up recently & IIUC is blazing fast -- and of course Dirk already built an R wrapper for it 😄 IMO it makes more sense to add contributions to that package to potentially improve the R API of leveraging simdjson, than to spend time teaching fread to parse arbitrarily-nested data structures.

[jangorecki]

Agree, what is also an important factor, jsonlite is very lightweight package, same like data.table, so adding it as an extra dependency in users code is not that big problem.