Comma Separated JSON | Hacker News Reader

Comma Separated JSON

kirit.com

So literally just JSON without the "[" and "]," delimiting rows, and another "[" at the beginning and "]" at the end of the file?

There are already streaming JSON parsers/serializers, why not just use (or write) one of those instead of inventing yet another file format?

There's also http://ndjson.org/ and http://jsonlines.org/

To add to this, APIs like Twitter's streaming API, have been sending a JSON blob per line for forever. No need to break up the line any further IMHO.

It's useful for situations where CSV output is a requirement. Actually, this seems like a great candidate for a CSV standard since JSON is so widely implemented.

But JSON won't match the quoting rules of any existing CSV parser, so it won't actually work in a situation where CSV is a requirement.

It's not just JSON without [ and ], it's a bunch of JSONs represented as a table. The first line sets the property's key and every other line are the values. It is a representation that needs a parser to be read but that's way faster for a row based database to generate it than to output full json. Plus it's smaller than minified json.

You can represent a table using JSON arrays, and I doubt CJS is "way faster" or smaller than gzipped JSON.

There is nothing inherently preventing you from writing an almost identically performing streaming JSON parser or serializer.

This is likely content coming down from an HTTP API so likely already gzipped. Since you can gzip both CSJ and JSON I think that's not really relevant.

With this proposed format you only need to parse the object definition once and you also get the ordering fixed. From then on you need only validate each field in each line as being legal and you can build your object quite fast.

This format saves probably half the network bandwidth, half or more in memory requirements, and more than half the cpu complexity in parsing and getting your final objects instantiated. That's pretty good.

Remember the goal isn't to replace JSON. It's to replace CSV. JSON and CSV have different goals. This is simply a JSON structure built to meet the same goals as CSV. It seems to do that quite nicely and can use existing JSON parsers internally to do it. Pretty nifty.

The spec doesn't specify what the first line contains.

It only says "Semantically a CSJ file is an array of JSON objects which share a common set of keys." You used your knowledge of how these representations usually work to infer that the first line contains a list of keys for the remaining lines.

Some obvious questions for a table spec include: 1) must those keys be strings? 2) Are duplicate keys allowed? 3) Can different rows have a different number of columns? Or must the writer generate null fields?

FWIW, I've used JSON Lines for my own work. It's very easy to read a line and (in Python), json.loads(line). How should I parse this "JSON without [ and ]"?

There's not a built-in function for it. Either I have to write my own JSON tokenizer, or I have to terminate the string with '[' and ']' and parse that. Both are much more complicated than using JSON Lines? You'll notice they only gave output generation timing numbers, not input parsing.

If a JSON-based table format is important, why not layer those same table semantics on top of JSON Lines, rather than make a format which is harder to read?

The format for the keys is the same as a javascript object. They are unique strings or numbers. It's already in the Ecmascript standard for objects.

That doesn't mean it's as easy to parse as plain JSON or JSON Lines.

In JS, non-streaming:

    JSON.parse(blob)

    blob.split("\n").map(line => JSON.parse(line))

respectively.

Could you help me understand your response?

I find it hard to match your statement to the JSON spec(s). Neither RFC 7159 nor ECMA 404 use the term "key". The standard for objects requires a string for the 'name' of an object member, which is certainly what you refer to. However, JSON does not allow a number.

I tried looking at the ECMAScript spec. In http://www.ecma-international.org/publications/files/ECMA-ST... :

> An Object is logically a collection of properties. ... Properties are identified using key values . A property key value is either an ECMAScript String value or a Symbol value .

That 'Symbol' has nothing to do with numbers, so only strings make sense for an external representation.

Perhaps you mean to include the integer from the Array object? The spec says "An Array object is an exotic object that gives special treatment to array index property key ... A String property name P is an array index if and only if ToString(ToUint32(P)) is equal to P and ToUint32(P) is not equal to 2^32-1."

Is that limited range of integers what you mean when you saying that numbers are allowed as keys?

Regarding uniqueness, the CSJ spec says nothing about uniqueness, so how do you know the keys must be unique? It doesn't inherit that from JSON. RFC 7159 says that in an object "The names within an object SHOULD be unique."; uniqueness is not required. It then adds "When the names within an object are not unique, the behavior of software that receives such an object is unpredictable."

Therefore, I don't see how you drew the conclusion you did.

> rather than make a format which is harder to read

Do you really believe that this is harder to read than JSON lines? We use both, and I've never heard a user complain that CSJ is hard to read, but the JSON lines has a lot of clutter.

    {"age": 45, "name": "Kirit Sælensminde", "job": "Minister Without  Portfolio"}
    {"job": null, "age": 5, "name": "Freyja Sælensminde"}

That seems fine for a low number of columns, but gets much harder as the column count goes up. Especially so as you lose control of the key order in the line.

The difference between this format and JSON lines is 1) the leading and trailing '['/']' for each line, 2) the convention that each line encodes an array and all arrays have the same length, and 3) the convention that the first line contains string names for the keys for the values on the remaining lines.

To clarify, JSON Lines says "Each Line is a Valid JSON Value", "The most common values will be objects or arrays, but any JSON value is permitted." It does not need to encode an object, so there needn't be "a lot of clutter".

Here's a sketch of a table parser in Python built on top of JSON Lines using conventions 2) and 3):

    import json

    def read_table(infile):
        line = infile.readline()
        keys = json.loads(line)
        for line in infile:
            yield dict(zip(keys, json.loads(line)))

The equivalent for a CSJ parser replaces the two "(line)" occurrences with "('[' + line + ']')".

As such, no, it's not hard to write a CSJ parser, but the performance will be slower.

I tested it with 3 columns (string, int, int) and 1456021 lines (including the header). Best of 4 for the JSON Lines + convention = 11.3 seconds. Best of 4 for CSJ = 11.8 seconds, which was slower than any of the JSON Lines parser times.

Perhaps the 8% slowdown isn't important After all, the uncompressed file size is smaller.

                 #bytes   gzip size
    JSON Lines  34316877   6561939
    CSJ         31404835   6576623
      savings:     ~10%     ~0.2%

So if the uncompressed savings is important, then use CSJ. But that's a small niche. Anyone concerned about space will use a custom format, while those who need to embed JSON objects or lists aren't going to be worried about 2 extra characters per line.

Thanks, really interesting to see some numbers.

When I parse this on the server I use a composable parser that doesn't need to do the string manipulation you have to in Python. I have no reason to believe that the parsing speed would be different at all with my code. I'd like to know though, so I'll probably do some tests -- that is if I ever get time :)

There is one other benefit (which is actually quite important for what we're using it for, but YMMV) and that is that it's very nearly CSV and for many simple files it is the same as CSV. This makes it very familiar looking to users of the system and means that for the most part these files can be easily previewed using a spreadsheet.

Your "composable parser" is conceptually similar to my "Either I have to write my own JSON tokenizer" alternative, though you already have such an alternative.

You are right that a well-optimized implementation will not be slower than a JSON Lines implementation on top of a stock JSON parser. The difficulty is in convincing people to build and deploy a high-performance CSJ parser. Why not convince them to use a high-performance CSV reader, conforming to whichever variant of CSV you wish to prefer, which presumably could include UTF-8?

Your point about similarity with CSV is a good one, but it fails in exactly the same ways as existing CSV portability fails. That is, consider the following CSJ:

    "question","answer"
    "Was it Nicholson, Duvall, or Lloyd who said \"Here's Johnny!\"?","Nicholson"

A trivial CSV parser which splits on "," will fail because it doesn't understand the quoting rules.

A CSV parser that does understand quoting rules, like the one in Python, will almost certainly default to Excel's quoting rules, which is different than what JSON does:

    >>> import csv
    >>> r = csv.reader(open("tmp.csv"))
    question|answer
    >>> print("|".join(next(r)))
    Was it Nicholson, Duvall, or Lloyd who said \Here's Johnny!\"?"|Nicholson

It can be fixed for Python by changing the convention:

    >>> r=csv.reader(open("tmp.csv"), escapechar='\\')
    >>> print("|".join(next(r)))
    question|answer
    >>> print("|".join(next(r)))
    Was it Nicholson, Duvall, or Lloyd who said "Here's Johnny!"?|Nicholson

but someone previewing it in a spreadsheet may see what appears to be garbage characters.

It's even worse for CSJ files which embed JSON objects, because the commas in those objects are not subject to quoting rules.

And there's the question of how the spreadsheet handles non-ASCII characters.

Here's an example using the tiny CSJ file from the spec page:

    "name", "age", "job"
    "Kirit S\u00e6lensminde", 45, "Minister Without  Portfolio"
    "Freyja S\u00e6lensminde", 5, null

Using Python's default CSV reader I get:

    >>> print("|".join(next(r)))
    name| "age"| "job"
    >>> print("|".join(next(r)))
    Kirit S\u00e6lensminde| 45| "Minister Without  Portfolio"
    >>> print("|".join(next(r)))
    Freyja S\u00e6lensminde| 5| null

You can immediately see that the space after the comma caused a portability problem!

I'll regenerate the CSJ file so it doesn't have a space:

    "name","age","job"
    "Kirit S\u00e6lensminde",45,"Minister Without  Portfolio"
    "Freyja S\u00e6lensminde",5,null

Even then, the Unicode escapes cause a problem:

    >>> print("|".join(next(r)))
    name|age|job
    >>> print("|".join(next(r)))
    Kirit S\u00e6lensminde|45|Minister Without  Portfolio
    >>> print("|".join(next(r)))
    Freyja S\u00e6lensminde|5|null

Thus, almost the only way you get perfect fidelity is with the CSV subset that is already portable. That is, "for the most part", CSV is portable enough.

(BTW, if imperfect fidelity is okay, you can open the JSON-Lines-for-table as a CSV file. The only problem will be the '[' as the first character in the first column, and the ']' as the last character in the last column.)

So a sed script?

I appreciate the problem this is trying to solve, but it could be more easily remedied by a JSON stream processor than reinventing the wheel of data marshalling (yet again). JSON is so simplistic in structure, it's trivial to know when you've met the conditions of a completed segment of structure. There's no reason why a stream parser couldn't handle this well.

How about twice the bandwidth, twice the memory, and at least twice the CPU complexity in parson full JSON objects than the proposed CSJ format?

This is actually a very elegant way of formalizing CSV. For most apps, if you were to simply output "CSV files" using this encoding, really no one consuming them would know any different -- except you could actually point them at a spec being used.

I've been in arguments with customers before where they complained we were quoting strings, and CSV files shouldn't have quotes, and it would have been nice to say "We're using CSJ, please just parse that way".

I think the name is perhaps misleading though, as it sounds more like it's a variant of JSON than a formalization of CSV. Perhaps "JSON flavored CSV" would be more appropriate?

I mean, you could always point them to RFC 4180.

Ah, well, look at that. Thankfully I haven't had to deal with CSV files for several years, though I wish I knew about that RFC at the time. Thanks.

Okay talk about ironic. Yesterday in our ops meeting, it came up that some tool another department had built was exporting csv "with extra quotes that were causing problems" (I've had no involvement in any of this). My ears perked up remembering this comment and I was able to point out this RFC, so hopefully it will save them some time in arguing about csv formatting. Thanks!

"Comma-Separated JSON" implies objects, "object-notation" is integral to the definition.

What about "Comma-Separated JSON Values"? CSJV!

So you save some space on top level keys which are stored once in the header...but you waste space by having to flatten data structures into a table to start with, with key values being repeated over lines, the more deeply nested, the worse it gets.

Stick with JSON document per line.

is there necessarily a need to repeat key values over lines?

why not have all possible object definitions in the header and data as a single huge array?

You're describing the type-length-value format, used in some places in digital video or in Open Sound Control.

Basically you have a TYPE (fixed width, eg. 16 bits), a LENGTH (again fixed) and a VALUE which is simply a stream of bytes that goes on for LENGTH bytes and is interpreted based on what TYPE it is. You can nest your fields and get very creative in quite a compact way.

One could represent a JSON document with TLV by adding an associative map between the field names and their type numbers, then stream away.

I always loved the simplicity of TLV. Though nowadays, I'm not sure this has much of an advantage over gzipped JSON.

Off-topic: Howdy, Mr. Thénot! Crazy reading the comments and seeing your username here. Hope life is goin' well for ya with the HealthNinja stuff -- haven't seen you since I left Wellcentive (around the same time as mbonnette, if that gives you an idea of who I am).

Because to parse it legally you'd have to store the entire file in memory. That's not actually streaming. The CSJ format puts the object schema and order up front and just applies it to each line afterwards. Stream away...

There are some good arguments against this format but your points were invalid. Key data in many different types of data is much larger than the actual values. E.g ("average_cpu_cylces_per_min": 60)

Gzip or some other compression scheme takes care of that pretty well. It's not rare to do JSON over gzipped HTTP requests.

You gzip csv and csj as well so this is orthogonal to the point.

Is it? I think his point is that key repition may not be a relevant factor when a gzip can be presumed.

ElasticSearch bulk API deals with streaming by using line breaks of JSON docs instead of an array. If you are interested in using this Comma Separated JSON, maybe you want to try this simpler approach before (though the comma separated version seems to be lighter, if I want really to reduce weight I'd consider protobuf, probably).

Reference: https://www.elastic.co/guide/en/elasticsearch/reference/curr...

Or: just use \n and also be able to read individual rows. Many programs in the wild do this.

I really wish ASCII delimited text didn't die off.

https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

https://news.ycombinator.com/item?id=7474600

But, it suffered from not having designated keys on common keyboards, therefore it wasn't obviously necessary for plain text editors to support. Meanwhile, everyone uses plain text editors for everything --even if it means jumping through hoops full of exceptions and corner cases to pretend they can handle more than plain text.

Newline-delimited JSON? I've used it informally, but I've never had the nerve to suggest it as an actual documented interchange format. It makes a lot of sense for things which CSV is often used for - dumping out big tables of data into files for shipping over FTP or similar - or even for logfiles with structured data entries. The tricky thing is having to restrict the JSON on each line to not contain any newline characters. Even though all legitimate JSON can be minified into a form which has the same semantics but contains no newlines, "JSON with no newlines in it" isn't a well defined standard.

"JSON with no commas in it" isn't a thing either. That's not what's being discussed here.

I've been using my custom JSV Format for years:

https://github.com/ServiceStack/ServiceStack.Text/wiki/JSV-F...

Which is just JSON with CSV-escaping which is ends up being faster, more concise and more human-friendly since quotes are only necessary when using special delimiter chars. It's just not as interoperable or ubiquitous as JSON so only an option when you control both ends.

If you are using a proper CSV library you don't have any of the problems this format is trying to solve. The CSV format does not have any issues mentioned, it is either a buggy CSV library or some lazzy developer who thinks CSV is just fields separated with comma writing 2 line of code instead of using a proper CSV library.

Which CSV library is able to infer that a value is a number instead of a string?

CSV for sure requires a convention to be shared between the producer and the consumer of the data. Once you have that I don't think you will need to deal with this problem.

To be honest, what I didn't like is the tone of the article. It is just that this statement is wrong: "But the problem is that CSV isn't really a well defined file format with a well defined syntax." There is RFC 4180.

I would call this "JSON encoded CSV" since it appears to be a strict subset of CSV, not JSON.

A cell can embed JSON objects and arrays, so it isn't a strict subset.

I thought MIME types had to go through an application process or start with x-

I think I'd rather stick to the format supported by Go and the 'jq' command, where each line is actually JSON.

But if it became popular, this format seems easy to support.

JSON enhanced CSV might be a more suitable name. After all it is valid CSV and invalid JSON.

Have needed something like this a few times, would be great if it was standardized.

inb4: https://xkcd.com/927/

the reason json is popular is because it's widely used and is easily parsed (without having to call any extra libraries).

standardizing this would mean accepting upon a complete working alternate that'd work everywhere just akin to json (plus the added perks), which will take a lot of (dis)agreement, especially given that there are already a dozen other standards trying to replace json.

no one uses csv by choice, they use it because they have to work with tools (excel) that can only produce/consume csv

given you don't have that constraint there are much better options (json lines, avro, parquet, protobufs...)

I use TSV often when I want to easily dump data from a C program to gnuplot or similar. It's more of an ad hoc file format then a serious one though.

So this is something that looks quite a lot like CSV, in some cases produces identical output to CSV, but which isn't compatible with CSV.

To be fair, most CSV isn't compatible with CSV.

Especially with the CSV in RFC 4180, which among other things requires a CRLF.

doesn't look very JSONy without any curly braces.

Reply on news.ycombinator.com