How to read a newline delimited JSON file from R?

Question

I have a newline delimited (i.e., each JSON object is confined to 1 line in the file):

{"name": "json1"}
{"name": "json2"}
{"name": "json3"}

In Python I can easily read it as follows (I must use the encoding encoding='cp850' to read my real data):

import json

objs = []
with open("testfile.json", encoding='cp850') as f:
    for line in f:
        objs.append(json.loads(line))

How can I do a similar trick in R?

At the end I want to get a data.frame:

library("jsonlite")
library("data.table")

d <- fromJSON("testfile.json", flatten=FALSE)
df <- as.data.frame(d)

I cannot show `d` because `fromJSON` fails with the following error: `Error: lexical error: invalid char in json text. testfile.json (right here) ------^` — Fluxy, Jan 26 '20 at 19:40
I would read with `read_json` and see if it works (`d <- read_json('testfile.json')` — akrun, Jan 26 '20 at 19:41
_I have a newline delimited (i.e., each JSON object is confined to 1 line in the file)_ Why? Do you have any way of changing that? — AMC, Jan 26 '20 at 20:01

score 4 · Accepted Answer · answered Jan 26 '20 at 20:28

4

We can use stream_in from jsonlite

library(jsonlite)
out <- stream_in(file('testfile.json'))
out
#    name
#1 json1
#2 json2
#3 json3

str(out)
#'data.frame':  3 obs. of  1 variable:
#$ name: chr  "json1" "json2" "json3"

answered Jan 26 '20 at 20:28

akrun

874,273
37
540
662

Hi @akrun : the stream_in() works on unboxed json. Is there a way to make it work on boxed single line json? – Lazarus Thurston Feb 27 '21 at 02:27
@LazarusThurston can you please post as a new question – akrun Feb 27 '21 at 19:36
Sure. Taking it out as a new question. – Lazarus Thurston Feb 28 '21 at 10:52
It's working for now :P. Will postpone posting the question for now. – Lazarus Thurston Feb 28 '21 at 11:04

niko · Answer 2 · 2020-01-26T20:14:06.723

You can read & process the data to a proper format and then parse the JSON

jsonlite::fromJSON(sprintf('[%s]', paste(readLines('text.json', warn = FALSE), 
                                         collapse = ',')))

#    name
# 1 json1
# 2 json2
# 3 json3

(you can use one of the many alternatives as JSON package e.g.

jsonlite a more R-like package as it will mainly work with data frames
RJSONIO a more Python-ic package working mainly with lists

or yet another one)

score 1 · Answer 3 · answered Jul 10 '20 at 23:01

The fastest way to read in newline json is probably stream_in from ndjson. It uses underlying C++ libraries (although I think it is still single threaded). Still, I find it much faster than (the still very good) jsonlite library. As a bonus, the json is flattened by default.

library(ndjson)
out<- ndjson::stream_in(path='./testfile.json')

How to read a newline delimited JSON file from R?

3 Answers3

Linked