Read a jsonl (json lines) file

Question

Reading a json file could be possible with this commands:

library(jsonlite)
json_text <- readLines("tect.json", warn = FALSE, encoding = "UTF-8")

Parse the JSON data

json_data <- fromJSON(txt = paste(json_text, collapse = ""))

in a format of data inside the tect.json file like this:

[
  {
    "id": 1,
    "name": "Apple",
    "color": "red",
    "price": 0.99
  },
  {
    "id": 2,
    "name": "Banana",
    "color": "yellow",
    "price": 0.5
  },
  {
    "id": 3,
    "name": "Orange",
    "color": "orange",
    "price": 0.75
  }
]

However if the tech.json has this format how is it possible to read it as a json file and convert it to a dataframe?

{"id": 1, "name": "Apple", "color": "red", "price": 0.99 }
{"id": 2, "name": "Banana", "color": "yellow", "price": 0.5 }
{"id": 3, "name": "Orange", "color": "orange", "price": 0.75 }

If I try the option at the start to read this file I receive an error like this:

Error: parse error: trailing garbage
          nge":"

How is it possible to read this file?

Answer 1

Your second example is not valid json. It is jsonl:

JSONL is a text-based format that uses the .jsonl file extension and is essentially the same as JSON format except that newline characters are used to delimit JSON data. It also goes by the name JSON Lines.

To deal with files like this you can read in each line as json, and then rbind() the results. A base R approach (requires at least R 4.2 for the _ placeholder):

library(jsonlite)
json_text <- readLines("tech.json", warn = FALSE, encoding = "UTF-8")

lapply(json_text, fromJSON) |>
    do.call(rbind, args = _) |>
    data.frame()

#   id   name  color price
# 1  1  Apple    red  0.99
# 2  2 Banana yellow   0.5
# 3  3 Orange orange  0.75

Alternatively, here's a dplyr approach:

library(dplyr)
lapply(json_text, fromJSON) |>
    bind_rows()

# # A tibble: 3 x 4
#      id name   color  price
#   <int> <chr>  <chr>  <dbl>
# 1     1 Apple  red     0.99
# 2     2 Banana yellow  0.5 
# 3     3 Orange orange  0.75

And for the sake of completeness here's the data.table way to do the same:

library(data.table)
lapply(json_text, fromJSON) |>
    rbindlist()
#       id   name  color price
#    <int> <char> <char> <num>
# 1:     1  Apple    red  0.99
# 2:     2 Banana yellow  0.50
# 3:     3 Orange orange  0.75

Answer 2

You can turn the file into valid json like so:

file_content <- 
  readLines("tech.json")

paste("[", paste(file_content, collapse = ", \n") , "]") |> 
  writeLines("tech2.json")

And then read it and bind it into a data.frame:

library(jsonlite)
library(dplyr)

read_json("tech2.json") |> 
  bind_rows()

Answer 3

You could also do:

jsonlite::fromJSON(sprintf("[%s]", toString(readLines("tech.json"))))

  id   name  color price
1  1  Apple    red  0.99
2  2 Banana yellow  0.50
3  3 Orange orange  0.75