4
$\begingroup$

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!)

$\endgroup$
  • 1
    $\begingroup$ You could try something like stream = OpenRead@"~/test.ndjson"; ReadLine[stream] // Developer`ReadRawJSONString. You could replace the developer function with Import[#, "JSON"]& if you want to avoid undocumented functions. $\endgroup$ – Jason B. Jun 24 at 14:39
  • $\begingroup$ 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.... $\endgroup$ – Kass Jun 24 at 15:04
4
$\begingroup$

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,
    line = ReadLine[stream];
    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,
    line = ReadLine[stream];
    If[StringQ[line] && !FailureQ[res = Developer`ReadRawJSONString[line]],
        ds["Append", res]
    ]
 ];
result = Normal[ds]
| improve this answer | |
$\endgroup$
  • $\begingroup$ Thank you very much! The second does not work for me (maybe because ds is not defined and my attempts to define it fail). The first works but only with a small file. Is there any way to stop streaming the lines after say 10 000 lines and then restart a separate stream? Thanks a lot, really! $\endgroup$ – Kass Jun 24 at 22:46
  • $\begingroup$ 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. $\endgroup$ – Jason B. Jun 24 at 22:57
  • $\begingroup$ Thank you! I realised the issue is that "create data structure" is available only Mathematica 12, while mine is 11. Working out for chunks of line now, will let you know! $\endgroup$ – Kass Jun 25 at 10:37
  • $\begingroup$ 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. $\endgroup$ – Albert Retey Jun 26 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.