5 ms·
Whenever I know I'm dealing with a large array of objects (and still want them to be user readable/editable) I typically choose to represent them in CSV format,
by pbohun 5y ago
Whenever I know I'm dealing with a large array of objects (and still want them to be user readable/editable) I typically choose to represent them in CSV format, sometimes with a special separation character.
Is there an advantage besides convenience where one would use JSON? JSON requires far more bytes per row to represent large arrays of objects.
- eatonphil 5y agoWell CSV only has one datatype: string. JSON has a few more.
- kroltan 5y agoSome people will curse me, but I have used JSON-over-CSV a few times, it's a good compromise if your data is indeed mostly tabular. 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", true
- saghm 5y agoThis is super interesting. I'd imagine there's a pretty good reason that you picked this over something like an array of arrays of values or something like { "row1": [...], "row2": [...] }, but the only reason I can think of is if there was some of external constraint that made CSV necessary. Was it that simple, or was there something else?
- kroltan 5y agoEmbedded system with limited bandwidth, the data was mostly simple values that did not really need escaping 99% of the time (mostly no unicode or control characters in strings, but still _possible_, and we needed the streamability of array-of-structs instead of struct-of-arrays. 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.
- deleted 5y ago[deleted]
- DougBTX 5y agoI think this would be fine, as long as the CSV layer was still parsable using the RFC 4180, then you could still use a normal CSV parser to parse the CSV layer and a normal JSON parser to parse the JSON layer. My worry with your example is that it is neither format, so it will need custom serialisation and deserialisation logic as it is essentially a bran new format. https://datatracker.ietf.org/doc/html/rfc4180 https://datatracker.ietf.org/doc/html/rfc4180 If you’re looking for line-oriented JSON, another option would be ndjson: http://ndjson.org/ http://ndjson.org/
- kroltan 5y agoYeah you're right, I'm not respecting the quote-quoting rule in this example, though IIRC I did in at least one of the actual uses. 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.
- laumars 5y agoYou’d be better off using jsonlines than creating a bespoke format that is neither CSV nor JSON. https://jsonlines.org/examples/ https://jsonlines.org/examples/
- kroltan 5y agoTrue, I guess that could be a viable alternative!
- tedunangst 5y agoMaybe you want to include metadata without adhoc parsing rules like skip the first five lines.
- cowsandmilk 5y ago> sometimes with a special separation character. My experience is that only leads to problems. Have never seen a good justification for not using RFC 4180 for csv files.
- rattray 5y agoJSON supports nested data and other data types, and your data will be read the same by any system (csv it might depend on the parser). MessagePack might be worth a look as a more efficient json if you're okay with a non-human-readable file.
- 0xedd 5y agoNo. CSV plays nicely in big data. JSON doesn't. 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.
- laumars 5y agoIt’s not as cute and dry as you claim. 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/ 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.
- stingraycharles 5y agoOne popular alternative (useful if your data has nested dicts or arrays) is to use newline separated json. This avoids having to encode or parse the whole dataset as a single object, but rather line-by-line, which works nicely with large datasets.
- philwelch 5y agoThis helps but repeating the keys on each line is still a pain.
- masklinn 5y agoArrays are valid json values.
- philwelch 5y agoBut that would be a very irregular way to use JSON.
- masklinn 5y agoNot even remotely.
- philwelch 5y agoIt’s not irregular to represent a list of typed objects in JSON as an array of arrays in which the first array is the array of keys in the same order as the corresponding values? Is there a JSON serialization library that actually uses this pattern? I’m not asking to be argumentative, but I genuinely like the idea and would be pleasantly surprised to see it.
- mattewong 5y agoI never really understood why anyone thinks this is a good idea. Popular, maybe. Smart? Not so sure. 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.
- klabb3 5y agoMy take, based on principles and reason (but not enough experience so take with a grain of salt) is that a json document is a message. If it doesn't fit in a message (whatever that means for your use case), you send multiple messages over a stream, or append them to a log file. Encoding wise, a newline character is a conventional and simple way to separate human readable messages. I believe this is called JSONL. Golangs json encoder does this by default. 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.