9 ms·
> Enhance the JSON SQL functions to support JSON5 extensions Wait, what's JSON5?? > Object keys may be unquoted identifiers. > Objects may have a single trai
by gred 3y ago
> Enhance the JSON SQL functions to support JSON5 extensions
Wait, what's JSON5??
> Object keys may be unquoted identifiers.
> Objects may have a single trailing comma.
> Arrays may have a single trailing comma.
> Strings may be single quoted.
> Strings may span multiple lines by escaping new line characters.
> Strings may include new character escapes.
> Numbers may be hexadecimal.
> Numbers may have a leading or trailing decimal point.
> Numbers may be "Infinity", "-Infinity", and "NaN".
> Numbers may begin with an explicit plus sign.
> Single (//...) and multi-line (/.../) comments are allowed.
> Additional white space characters are allowed.
Oh crap, the levees have broken!
- staz 3y agoall that and still no datetime support which is the most annoying thing missing in JSON imho
- stusmall 3y agoHuh, I'd never thought twice about that. What would native datetime support in JSON get you that a ISO 8601 string doesn't?
- masklinn 3y agoWell one thing it could get you (probably wouldn't, but could) is symbolic timezones, as ISO 8601 only supports offsets.
- dspillett 3y agoType validation for one. The reduced chance that a client has put some backwards format in there like MM-DD-YYYY (or DD-MM-YYYY for that matter) or just an invalid date completely. You might as well ask what native numeric or boolean support offer over just jamming stuff in a string in an agreed format. Some might argue that dates are a compound value so differ from atomic types like a number, but they are wrong IMO as a datetime can be treated as a simple numeric¹ with the compound display being just that – a display issue. Others will point out that JS doesn't have a native date/datetime/time type, but JSON is used for a lot more that persisting JS structures at this point. -- [1] caveat: this stops being true if you have a time portion with a timezone property
- masklinn 3y ago> You might as well ask what native numeric or boolean support offer over just jamming stuff in a string in an agreed format. A big difference is that a numeric or a boolean are quite limited datatypes with agreed upon semantics (mostly). > Others will point out that JS doesn't have a native date/datetime/time type It does, in fact. And it's absolute shit. > but JSON is used for a lot more that persisting JS structures at this point. So what I'm reading here is that you don't need types to be supported natively in order to serialize to JSON.
- dspillett 3y ago> It does, in fact. And it's absolute shit. Maybe it is "just us", maybe it is part of the general shitness you point out, maybe it is the lack of literal representation (even VB and relatives had one) other than an ISO8601 string, but dates don't feel native like simpler types, arrays, objects, fictions, … > So what I'm reading here is that you don't need types to be supported natively in order to serialize to JSON. Yes. But not absolutely needing something does not mean it isn't (or wouldn't be) exceptionally useful to have.
- regularfry 3y agoWhat it gets you is not having to deal with breakage when someone doesn't know the difference between ISO8601 and RFC3339 or, in fact, `date` strings. Shuffle all that mess off down the stack to where someone else has made one decision, once, rather than having to relitigate it every time.
- 8organicbits 3y ago> It is a conformant subset of the ISO 8601 extended format. Huh, TIL. https://www.rfc-editor.org/rfc/rfc3339 https://www.rfc-editor.org/rfc/rfc3339
- flatline 3y agoInconsistent datetime storage has been a consistent issue for me providing cross-provider and cross-application/framework support for SQLite.
- masklinn 3y agoJSON will never have datetime support, since Javascript does not have datetime literals (and that's a good thing given how horrible the Date object is). Probably more importantly, all of that and still not proper datetimes in sqlite. Also even more so no domains (for custom datatypes).
- Mister_Snuggles 3y ago> Probably more importantly, all of that and still not proper datetimes in sqlite. Home Assistant recently did a ton of changes to work around the issues caused by this. The short story is that they stopped storing timestamps as 'timestamp' datatypes and started storing them as unix times stored in numeric columns. Since timestamps turn into strings in SQLite, this was a huge improvement for storage space, performance, etc. The problem is that this change also affects databases which have a real datetime datatype. So PostgreSQL, which internally stores timestamps as unix times, is now being told to store a numeric value. To treat it as a timestamp you have to convert it while querying. Since I used PostgreSQL for my Home Assistant installation, this feels like a giant step backwards for me. I wish that they had used this change as an opportunity to refactor the database code a bit so that they could store timestamps as numeric for SQLite, but use a real timestamp datatype for MySQL and PostgreSQL. I'm sure that this isn't a simple thing to do though.
- tracker1 3y agoGenerally speaking... DateTime/TimeStamp fields between databases are in general treated differently either in practice or purpose much of the time. When migrating from one database to another, this is almost always an issue.
- chris-orgmenta 3y agoIn people's opinions: Would this feature be appropriate to implement, or beyond the scope of what the JSON project should aim for?
- pdimitar 3y agoI don't know what "appropriate" or even "beyond the scope" mean in this context but, having in mind that datetime data needing to be stored is a fact of life that's not going away then I'd say yes, it does belong in JSON. It also belongs in SQLite.
- Thaxll 3y agoYou can remove JSON, when is SQLite adding proper date type?
- thunderbong 3y agoIt's already available - https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- pdimitar 3y agoWrong, there's no datetime there. Only integer can be used for it in a more space-saving manner. But that means you have to convert and invoke calculation functions. It's error-prone.
- deleted 3y ago[deleted]
- NelsonMinar 3y agoIs JSON5 a thing people use? I see it's from 2012 but it's the first I've heard of it. It looks fairly sensible; I'd take it for trailing commas alone. And comments!
- yamtaddle 3y agoHow does it affect speed of parsing in JavaScript? I kinda thought the whole reason this deeply-mediocre format caught on in the first place was it was especially natural & fast to serialize/deserialize in JavaScript, on account of being a subset of that language. (XML also had the "fast" going for it thanks to browser APIs, but not so much the "natural")
- masklinn 3y ago> this deeply-mediocre format Most of the "popular" publicly available formats at the time were singularly worse, even ignoring commonly limited or inconvenient language support. SOAP? ASN.1? plists? CSV? uuencode? I'll still take JSON over all of them, especially when it comes to sending shit to the browser (plists might be workable with a library isolating you from it, but it is way too capable for server to browser communications, or even S2S for that matter not all languages expose a URL or an OSet type). > it was especially natural & fast to serialize/deserialize in JavaScript, on account of being a subset of that language. That is certainly a factor, specifically that you could parse it "natively", initially via eval, and relatively quickly[0] via built-in JSON support (for more safety as the eval methods needed a few tricks to avoid full RCE). But an other factor was almost certainly that it's simple to parse and the data model is a lower common denominator for pretty much every dynamically typed language. And you didn't need to waste time on schemas and codegen, which at the time was a breath of fresh air. > XML also had the "fast" going for it thanks to browser APIs, but not so much the "natural" XML never has "fast" going on in any situation, the browser XML APIs are horrible, and you had to implement whatever serialization format you wanted in javascript over that, so that was even slower (especially at a time when JS was mostly interpreted) [0] compared to the time it started being used: Crockford invented / extracted JSON in 2001, but services started using JSON with the rise of webapps / ajax in the mid aughts, and all of Firefox, Chrome, and Safari added native json support mid-2009
- nilsbunger 3y agoComments in JSON? Why isn’t this everywhere?
- cryptonector 3y agoBecause preserving them across processors (think jq) is hard or impossible.
- Salgat 3y agoIt's a shame because it's a rather trivial thing to filter out. Personally I wish they would have left out the "//" single-line commenting to avoid the whitespace dependency.
- cryptonector 3y agoFiltering them out is indeed trivial, but then they're not stable / preserved, and so why bother writing them? Ensuring that comments and their locations in the text are preserved by processors is quite difficult if not impossible. Reformatting a JSON text alone can "break" comments by not necessarily placing them where they belong. Any schema transformation means comments must be dropped.
- fiddlerwoaroof 3y agoYou can write a parser that generates an AST node for comments and then pretty prints that AST. So it’s not impossible, but it does require that your parser gives you the option to not just drop comments on the floor.
- cryptonector 3y agoAlright, here's a JSON5 text: { // start x: "y", // the following is foo foo: "hey", // the following is bar bar: "there" // end } When parsing it, should I attach each comment to the following name or the previous name? If I reformat to change indentation, should I change the indentation of the comments too? How would the comment that reads "the following is bar" be re-indented? What if there are duplicate names? JSON does allow duplicates. If I attach comments to preceding (or following) names, then while parsing I find a dup... what should I do with the preceding comment and the new name?
- cryptonector 3y ago> > Strings may be single quoted. Hmmm... It's super convenient that SQL uses single quotes for string literals while JSON uses double quotes. Changing that is going to cause pain. > > Strings may span multiple lines by escaping new line characters. I really can't recommend this. Yeah it's annoying to have to write \n, but still.
- masklinn 3y ago> Hmmm... It's super convenient that SQL uses single quotes for string literals while JSON uses double quotes. Changing that is going to cause pain. Surely you're not constructing queries by concatenating JSON to text? > I really can't recommend this. Yeah it's annoying to have to write \n, but still. I can only disagree, the ability to just put newlines in a string literal in languages like rust is refreshing.
- cryptonector 3y ago> Surely you're not constructing queries by concatenating JSON to text? Of course not, but I do have code that generates SQL and which uses JSON. (And no, the code in question is not subject to SQL injection.)
- masklinn 3y agoI fail to see how this is relevant. Your code feeds JSON to SQL, that remains supported. Or your code fetches SQL-generated value from SQL, in which case as literally stated before the JSON5 listing: > JSON text generated by [JSON] routines will always be strictly conforming to the canonical definition of JSON.
- throw0101a 3y ago> > Single (//...) and multi-line (/.../) comments are allowed. We can now have different parsers have pragmas that specify different behaviour depending on whether those pragmas are recognized or not. In case anyone was wondering, the history is that comments were considered an anti-feature by Douglas Crockford, the creator of JSON: > I removed comments from JSON because I saw people were using them to hold parsing directives, a practice which would have destroyed interoperability. I know that the lack of comments makes some people sad, but it shouldn't. > Suppose you are using JSON to keep configuration files, which you would like to annotate. Go ahead and insert all the comments you like. Then pipe it through JSMin before handing it to your JSON parser. * https://web.archive.org/web/20150105080225/https://plus.google.com/+DouglasCrockfordEsq/posts/RK8qyGVaGSr https://web.archive.org/web/20150105080225/https://plus.goog... * https://en.wikipedia.org/wiki/Douglas_Crockford https://en.wikipedia.org/wiki/Douglas_Crockford
- arp242 3y agoAnd for a data interchange format this is also 100% reasonable. The problem is that people have started using JSON for configuration files and the like, which IMHO has always been – and continues to be – the wrong tool for the job.
- hgsgm 3y agoHow does that relate? Your config parser can disregard commments.
- arp242 3y agoYes, but JSON was never intended for that use case – the feature set isn't geared towards it.
- mikepurvis 3y agoI think the point is that your config parser should be using yaml or toml, though in fairness neither of those existed in the early 2000s when JSON was being developed/discovered/adopted— the 800lb gorilla in that space at the time was XML. And XML remained dominant for a long time— for example, the original Google Maps from 2005 received its server responses as XML blobs, and API v2 even exposed the relevant parsing functionality as the GXml JavaScript class. By around 2007, it was all JSONp I think, and GXml was deprecated and removed in API v3 and v4 respectively.
- teddyh 3y agoDespite the name, “JSON5” is not an official successor to JSON.
- stjohnswarts 3y agoGood to know, I would still like to see it become defacto successor though.
- chris-orgmenta 3y ago"> Objects may have a single trailing comma." Any arguments against this one? My knee jerk reaction is 'yay'
- givemeethekeys 3y agoWhy would you put a trailing comma when there's nothing after it? A comma isn't a period :-/
- Taywee 3y agoWhen manually modifying JSON, it really helps avoid accidentally missing a comma when you add or reorder elements. JSON isn't English. The comma doesn't mean what it does in English, so I don't see why a period would be appropriate either.
- dangerlibrary 3y agoBecause it follows the general principle of making it easier to change [0]. Fewer edits, cleaner diffs. Useless from the perspective of a wire format, but nice for things like config files, which seem to be the use cases json5 is targeting. [0] Bullet pt 3: https://betterprogramming.pub/5-essential-takeaways-from-the-pragmatic-programmer-6bb3db986294 https://betterprogramming.pub/5-essential-takeaways-from-the...
- Tmpod 3y agoMakes copy-pasting and reordering lines easier, which is quite handy.
- everybodyknows 3y agoSimplifies JSON-generation code, typically saving a conditional.
- lordgrenville 3y agoJust noticed today that the Python formatter "black" does this, I really disagree. Of course I realise that by noticing its changes and arguing about them I am missing the whole point of using it.
- SQLite 3y agoThe important point to keep in mind is that SQLite will read JSON5, but it never writes it. The JSON that SQLite generates is canonical JSON that is fully compliant with the original JSON spec. It turns out that there is a lot of "JSON" data in the wild that is not pure and proper JSON, but instead includes some of the extensions of JSON5. The point of this enhancement is to enable SQLite to read and process most of that wild JSON. This feature was requested by multiple important users of SQLite.
- alberth 3y agoDr. Hipp Off topic: would you mind sharing any info on potential timing of begin-concurrent-pnu-wal2 branch being merged into main (or consideration of forking sqlite to have a "client/server" version)? https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2 https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-w... (Love what you have created. Thank you so much for all the years of amazing work)
- SQLite 3y agoThat branch has been renamed "bedrock" (after its principal user) and is up-to-date.
- sidewndr46 3y agoIsn't this just the YAML debacle all over again? Where virtually any sequence of text is "valid" (albeit meaningless) YAML?
- keithalewis 3y agoNo need to go Postel about it.
- atomize 3y agoI wished upon a shooting star for comments in JSON sometime in the 2010s. That took a while, but I'm glad it came through. -- EDIT: mainly back when we started using JSON to configure, well, lot of things.
- rcarmo 3y agoDoes it have "contains" and paths yet?