to_ndjson

where each row/column becomes its own object in an array.

---

• data.frame by-row & by-column
• list elements
• matrix by-row & by-column
• remove trailing '\n'

[sheffe]

I found this issue stub while researching jsonify to create ndjson for Google BigQuery inserts. If it's helpful to have another use-case from the wild in mind, here's the BigQuery edition. Most of this is from the documentation here.

(I have some example data but not a full reprex below. If followup seems useful to you I'll happily generate more detail.)

BigQuery efficiently ingests nested/repeated data when it's structured as a single JSON per newline-delimited row, without wrapping the row-object in an array. Each row in an R dataframe (with arbitrary list columns) becomes a single line wrapped in {...} instead of [{...}]. The example data from BigQuery docs looks like this:

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

Right now I'm making this work in an ugly way with data.table, jsonify, and substr, like this:

df_split <- split(setDT(df_raw, key = "unique_row_index"), by = "unique_row_index")
json_vec <- lapply(df_split, FUN = (function(x){ y <- jsonify::to_json(x, unbox = TRUE, by = "row")); substr(y, 2L, nchar(y) - 1L}))
fwrite(json_vec, quote = FALSE, sep = "\n")

IMHO the best scenario for the BigQuery problem would be an option in to_ndjson to input a dataframe and spit back a list or unescaped character vector of JSON elements.

FWIW jsonify has already saved me a ton of time on this application, so thank you! All yet-another-json-thingy self deprecation aside, it's a great package.

[dcooley]

Thanks for the use-case. It's useful to see how this would be used. I haven't used it myself, but are you aware of the bigrquery library?

[sheffe]

Oh yeah, bigrquery is great for using BigQuery from an R client once there's a DB up and running. The issues I'm confronting now are mostly around ingestion pipelines. It seems like many of BigQuery's optimizations rely on tables with nested and repeated field schemas that amount to pre-joining data (which was pretty unfamiliar to me, coming from Postgres land). BQ can ingest not-just-row structured data from ndjson, Parquet, and Avro files, but most of my data pipelines are in R and ndjson is the friendliest solution.

Beyond the BigQuery case I'm seeing a lot more ndjson applications in the wild on larger datasets. A few data providers I work with are pushing it heavily for large spatial cases. E.g. R hits the 2^31 character vector length limits pretty quickly on big geojson files, and I suspect even geojsonsf and other heavily-optimized packages couldn't really work around that. Newline-delimited also means you can use fast CLIs like split to batch-size files to taste without any headaches. I'm becoming a big fan of the nd-format vs regular (geo)json.

[dcooley]

Funny you should mention it, but I recently updated geojsonsf to handle larger character vectors in this issue

But I agree on the usefulness of ndjson and hope to implement this soon.

[dcooley]

First proof-of-concept

df <- data.frame( x = 1:5, y = letters[1:5] )
cat( jsonify:::rcpp_to_ndjson( df ) )
{"x":1,"y":"a"}
{"x":2,"y":"b"}
{"x":3,"y":"c"}
{"x":4,"y":"d"}
{"x":5,"y":"e"}

N <- 1e6
data <- data.frame(
  longitude = runif(N, -180, 180),
  latitude = runif(N, -180, 180),
  tooltip = sample(LETTERS, N, replace = TRUE)
  , stringsAsFactors = F
)

microbenchmark::microbenchmark(
  json = {
    res <- jsonify:::rcpp_to_json( data )
  },
  ndjson = {
    res <- jsonify:::rcpp_to_ndjson( data )
  },
  times = 5
)

# Unit: seconds
# expr      min       lq     mean   median       uq      max neval
# json 1.314854 1.318062 1.351673 1.336339 1.372355 1.416754     5
# ndjson 1.480732 1.481594 1.502879 1.483283 1.485288 1.583497     5

res <- jsonify:::rcpp_to_ndjson( data )
str( res )
# chr "{\"longitude\":18.95450374111533,\"latitude\":-76.69491718523205,\"tooltip\":\"X\"}\n{\"longitude\":138.2996058"| __truncated__

cat( substr( res, 1, 500 ))
# {"longitude":18.95450374111533,"latitude":-76.69491718523205,"tooltip":"X"}
# {"longitude":138.2996058370918,"latitude":96.73179904930294,"tooltip":"K"}
# {"longitude":150.90493150055409,"latitude":41.85694866813719,"tooltip":"B"}
# {"longitude":52.576808631420139,"latitude":120.26858397759497,"tooltip":"M"}
# {"longitude":72.25942776538432,"latitude":-58.31666300073266,"tooltip":"W"}
# {"longitude":-35.5781511683017,"latitude":-87.4365452863276,"tooltip":"N"}
# {"longitude":-138.20940038189293,"latitude":3

[dcooley]

data.frame by column

df <- data.frame(
  x = 1:5
  , y = letters[1:5]
)

cat( jsonify:::rcpp_to_ndjson(df, FALSE, -1L, TRUE, TRUE, "column") )
# {"x":[1,2,3,4,5]}
# {"y":["a","b","c","d","e"]}

[sheffe]

I'm testing this out now, with remotes::install_github("SymbolixAU/jsonify", ref = "issue29"). So far everything's working great! Even on some large deeply nested dataframes, dfs with geojson embedded as strings, mixed types, etc, speed and memory consumption performance are both really solid.

I'm using some live data from our BigQuery pipe to test right now -- I'll try to sanitize it or generate some comparable noise and post the results this weekend.

[dcooley]

@sheffe what do you think should be the result of to_ndjson() for nested lists:

lst <- list(
  x = 1:5
  , y = list(
    a = letters[1:5]
    , b = data.frame(i = 10:15, j = 20:25)
  )
)

lst

# $x
# [1] 1 2 3 4 5
# 
# $y
# $y$a
# [1] "a" "b" "c" "d" "e"
# 
# $y$b
#    i  j
# 1 10 20
# 2 11 21
# 3 12 22
# 4 13 23
# 5 14 24
# 6 15 25

should this return 2 lines for just x and y, or should the nested y be split into another two lines for a and b?