Is there an advantage besides convenience where one would use JSON? JSON requires far more bytes per row to represent large arrays of objects.
Tabular data allows you to easily optimize performance and costs. For example, Google decided it is a good idea to export some of its billing data columns as JSON. As a consequence, filtering by a low cardinality value means parsing a large amount of data because it's in a JSON column. Something that would otherwise be cheap in a classic columnar compression. Where the value is stored once along the number of subsequent rows with the same value. BigQuery bills according to amount of data parsed.
The above anecdote doesn't cost much, fortunately, because the billing data isn't large. But, in terms of a data pipeline development costs, I don't use it in the context of big data.
CSV only plays nicely with big data where your CSV encoder and decoder both agree on how to read and write CSV data. The problem is CSV is not formal standard, sure there are documents on how CSV should work they’ve never been enshrined as a standard. This means CSV parsers often differ (like with how some JSON parsers break spec with supporting comments except where JSON differs it’s nondestructive to data integrity but where CSV differs is hugely destructive).
Common issues that break CSV:
* headings or no headings
* multi line comments not being handled the same
* delimiters differing
* differing support for quotation marks
* how to escape characters, particularly control characters such as quotation marks, new lines and delimiters
* parsing of numbers (a lot of popular CSV editors actual mangle numbers a lot)
* parsing of non-numeric data that superficially appears as numbers (like credit card data, dates, basically anything where zero padding needs to be preserved).
But the worse offence with most CSV parsers is that they’ll often silently fail (or silently do the wrong thing) thus garbling your data in ways that can be almost impossible to spot on really large data sets.
You can see a practical example this problem just saving a CSV file in Excel :)
JSON might have its warts but it is a better format for where you care about preserving the integrity of the data between two independent systems. However if you’re reliant on tabulated data then I might recommend jsonlines https://jsonlines.org/examples/ (ndjson is a very similar spec too). While they have not been formally standardised they do at least extend JSON in a complimentary way and they also solve your complaints about JSON but without creating the same problems as CSV (well, aside from the headings problem).
I appreciate if you’re working with large enterprise databases then you’re hands are likely tied as to which file format you can use. But if you’re writing your own routines then jsonlines is definitely a better format for data integrity than CSV.
I didn't know CSV injection was a thing until it got flagged in a pen test. Then you look around and realize it's a widespread problem and most serializers don't even have an option to escape them.
* CSV only supports one data type: string. Thus formulas are just strings processed as code by some applications based on the content of that string
* CSV doesn’t support character escaping. Everything is supposed to be read unescaped. Even new lines are literal new lines. There no support for C-style escaping. If you need to have control characters then you wrap your string in quotation marks (and the fact that quotation marks are option leads to another class of bugs). If you need quotation marks inside your quotation marks then you double up the punctuation marks (ie to print “ inside “” then your string would look like
“Bob said “”hello”””
The character will be rendered/visible, but that's better than letter excel execute some arbitrary code.
Instead of just modifying your parser, you change your entire input format to be incompatible with what most of the rest of the world uses (standard JSON).
I get it, it requires slightly more technical skill / effort / etc to implement a proper streaming JSON parser (which BTW will perform WAY better in terms of speed and memory) instead of just writing "my_data.split('\n')". But, there are pre-existing libraries that already do that, and a one-time tiny bit of extra work, to open up a capability that you can reuse anywhere in your stack, seems to be a much better option than a lifetime of incompatibility with standard JSON.
It follows that json is not a database, and the fact that it's tempting to use it as such is no fault of json.
I'm not ready to say json is better than csv, but I dislike that csv has a rigid table structure but no types. It feels like combining sweat pants with a tie. I also dislike that csv has different conventions around quoting and such. I don't feel comfortable editing csvs by hand. It's possible I'm wrong and I just never learnt it correctly, but experience tells me to trust that feeling.
MessagePack might be worth a look as a more efficient json if you're okay with a non-human-readable file.
My experience is that only leads to problems. Have never seen a good justification for not using RFC 4180 for csv files.
To be clear, I mean encoding values as JSON, but the overall object structure in CSV, so something like:
id,name,cool
1,"John Doe",false
2,"Daisy \"Obliterator of Worlds\" Wilson", truehttps://datatracker.ietf.org/doc/html/rfc4180
If you’re looking for line-oriented JSON, another option would be ndjson: http://ndjson.org/
ndjson has the disadvantage of repeating column names for every record, which, granted, is basically fine if you are using compression, but sometimes for whatever reason you can't.
Why not some binary format, then? Well, it's easier to debug and share the data with non-technical people (just open in excel or equivalent) without having to run a conversion step.