Finding best strategies for parsing a large JSON set faster

[forthrin]

Let's say:

• You need to parse large set of JSON files
• You only need a small portion of the schema
• You might not want to refactor your extraction code
• You want it to go as fast as possible

Possible (combined) strategies:

• Prune the schema
• Flatten the schema
• Minify keys
• Minify values
• Use JSONL and Mmap (and smart ordering)
• Use SQL (though must sync if data updates)

1. What are the silver bullets, if any? Or how to easily pick the best dev-time / vs performance-gain for a given scheme/set?
2. Is there an interactive "pruning editor" for JSON trees that can batch converts whole sets, and can also show, eg.

• Estimated gain per strategy (eg. Cut "extraData": 5% CPU, 3% SIZE")
• Value variance (eg. "availableNow": 83% true, 17% false)

3. What would be the parse gain if JSON keys were unquoted? (Superfluous when parsed by non-JS / not embedded)

[wader]

If jq is too slow to parse the input(s) then you probably want to use some other tool for pruning/flattening. Which tool i guess depends on how complex the pruning/flattening criteria is. Maybe something simdjson based like fastgron could be used? maybe also worth see how gojq or jaq performs.

[forthrin]

Good pointers! Dumb test with 500 MB 12k lines JSONL. They do perform better/similar with a recipe (3-6s), with jaq still best.

• 2s - Ruby + Mmap + JSON.parse
• 5s - jaq -c . > /dev/null
• 7s - gojq -c . > /dev/null
• 20s - jq -c . > /dev/null
• 😖 - fastgron -s → libc++abi: simdjson::simdjson_error: CAPACITY: Document that big → Abort trap: 6

Did some R&D, eg. extract schema to YAML, prune in text editor, then batch prune from that, but text editing felt too "unsafe".

That's where the "pruning editor" came up, ie. nav tree, toggle branches, with info on variance and estimated gains. Thing is once manual pruning is done, the rest (flattening, minifying, etc.) can be automated. If no-one made this yet, maybe that's the calling?

PS! Seems the reason for quoting keys is that JSON parsing is LL(1), and lookback would hurt more. So to hockey sticks with that.

[forthrin]

Did some more tests, and it seems minifying keys (and even values) has an underwhelming effect on processing time. It simply seems to be the sheer number of records that make the biggest difference. And system level disk cache(!) So preliminary conclusion:

• Remove as many unnecessary values as possible
• Process as few records as possible (eg. by smart indexing/ordering of JSONL records)
• Minifying keys and flattening trees should theoretically help, but perhaps only if the data sets are really big

1. Do you think a "pruning editor" would be interesting? It would be like a jless with a delete button.

PS! TBH, I think the optimal solution would be:

• Use SQL as an index for the individual JSON files
• Update SQL when file.mtime > sql.mtime (which is rather seldom)
• Put all frequently accessed or searchable values in the SQL
• Leave more complex data structures in the files
• Look up files after narrowing down their number by other criteria

2. I recall there was a "NoSQL" fad ten years ago where you would just shove in copious amounts tree data and query with abandon. Does that offer any huge performance gains over JSON parsing the way things are today?

[wader]

Haven't had the need for a pruning tool myself. Maybe you want to look at postgres or some other database with json support? at least postgres has a binary representation type for json and i think it can be used in indexes.

If parsing time is the main issue maybe something JSON compatible like CBOR (supported by jaq), MsgPack etc could be useful?

[forthrin]

Thanks again. I'll see where this bring me. Had a go at MongoDB, and seems very suited for this type of thing. You obviously have to keep DB in sync with files. Maybe there's a built-in or supporting tool for that. If now, then quite easy to RMO.