Openning JSON file, (NDJSON, new line delimited) — big file

I am trying since many hours to open a JSON file ("new line delimited") that has this as structure

{"Id":"5a864241ec2f2018","yearOfBirth":"1942","gender":"male","creationDate":"2009-01","numberOfNeutralVue":"0"}
{"Id":"8a75dacbe1c0d991","yearOfBirth":"1947","gender":"male","creationDate":"2004-01","numberOfNeutralVue":"0"}


(when I open at Blocknote txt) or if I use

Import["....json","CSV"]


While when I use Import["...","JSON"], I get the error message, specifically

Import::jsonexpendofinput: Unexpected character found while looking for the end of input.
Import::jsonhintposandchar: An error occurred near character '"', at line 2:3


I will be grateful for any help how to proceed! (Moreover some of my files are rather big, more than 1 Gb, I know it is a separate question, but if you can also give advice on how to deal with chunks of such file, I will appreciate a lot!)

• You could try something like stream = OpenRead@"~/test.ndjson"; ReadLine[stream] // DeveloperReadRawJSONString. You could replace the developer function with Import[#, "JSON"]& if you want to avoid undocumented functions. – Jason B. Jun 24 at 14:39
• Huh, thank you very much! At least something is finally happenning. Indeed I can see the lines from the file and the structure. But can you be so kind also to explain how I can collect those lines into chunks of lines (tables)? Sorry for my ignorance, but my experience so far is limited to working with mini csv and excel files.... – Kass Jun 24 at 15:04

Import[myfile.ndjson,"JSON"] doesn't work because the file as a whole is not valid JSON. But each line is valid JSON and so we can treat the file as a stream and read it one line at a time. The simplest way to do this would be

stream = OpenRead@"~/test.ndjson";
ClearAll[line];
result = {};
While[line =!= EndOfFile,
If[StringQ[line] && !FailureQ[res = ImportString[line, "RawJSON"]],
AppendTo[result, res]
]
];


The only real issue with this is the AppendTo, which can be a very inefficient way to grow a large list (so if you have many thousands of lines it could slow things down).

To get around this use the new "DynamicArray" data structure:

stream = OpenRead@"~/test.ndjson";
ClearAll[line];
ds = CreateDataStructure["DynamicArray"];
While[line =!= EndOfFile,

• I added the definition for ds. To stop the loop early you can adjust the line line =!= EndOfFile to something like (line =!= EndOfFile) || (++i < 10000) assuming you define i=0 beforehand. – Jason B. Jun 24 at 22:57
• If you don't have the new "DynamicArray" data structure available you can fall back to the old ways to do this: Either use Reap/Sow or something like result={result,res} in the loop and a result=Flatten[result] in the end. These should both be performing reasonably well. You can find more details about the latter approach in other questions on this site, e.g. here. – Albert Retey Jun 26 at 12:29