6 ms·
I see that Postgres also has a binary JSON format, and also is called JSONB (https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.o
by ks2048 3y ago
I see that Postgres also has a binary JSON format, and also is called JSONB (https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html), although I don't see any specification for the details. I supposed these are unrelated. Too bad people couldn't agree on a standard.
I realize in these cases, it's mainly for internal DB use, it would seem useful to have a widely use standard. (I'm aware there are many other attempts like BSON, etc).
- sgbeal 3y ago> I supposed these are unrelated. Correct. Same name, different implementations. > Too bad people couldn't agree on a standard. This is a case of making internal optimizations for performance and space, and standards always have fluff which is not relevant for certain use cases. Like Postgres's, this implementation is very specifically fine-tuned to the target's internals. That level of optimization wouldn't be possible with a one-size-fits-all standard (e.g. RFC-8259 JSON).
- OJFord 3y agoAgreed, the standardised part is JSON, it's not clear to me that the binary format, to be used internally by each of sqlite & postgres, would benefit from standardisation? As an ideal, sure, but what concrete benefit? Some kind of third-party tool/plugin that might hypothetically work with either, bypass the clients, and benefit from a single parser implementation for the internal representation of binary JSON columns?
- paulddraper 3y agoNot for databases but for messages, ala msgpack
- DemocracyFTW2 3y agoSure, maybe, but neither Postgres's nor SQLite's binary format are supposed to ever leak to the outside; it is strictly an implementation detail.
- paulddraper 3y ago> Not for databases
- orf 3y agoWhat’s the thought pattern here? Different implementations of different message formats should have a common format for representing… JSON? Why?
- paulddraper 3y agoUh no. Not multiple formats. Different messaging systems, libraries, etc should use a JSON-equivalent binary format. If you'd like you could say it's messagepack, but it's not because it's a superset of the JSON model, you can't round trip it
- orf 3y agoSo, BSON? https://en.m.wikipedia.org/wiki/BSON https://en.m.wikipedia.org/wiki/BSON But the argument here seems to be that we need “one format to rule them all”, which obviously never works.
- brazzy 3y agoSurely the thing that would benefit from standardization is the SQL-level stuff use JSON values, like indexing, and path expressions.
- o11c 3y agoFrom looking at it, the problem is that JSON fails to define "what is a string?" and "what is a number?". Possibly there are duplicate-key concerns as well. Postgres's JSONB inherits the definition of Postgres's string and numeric types. SQLite's JSONB punts the problem down the line to whoever uses the JSON. (this all is a reminder that JSON is a horrible format and you should never use it if you care about your data)
- Aqueous 3y agoJSON is one of the most succinct formats that you can use to express an arbitrary data shape of hashes, arrays, and primitives. It is highly useful and covers 99% of cases.
- o11c 3y agoand 1% of the time, it just does the wrong thing. It just silent clobber your data; it might crash your program due to creating an incoherent logic state. Who knows?
- theamk 3y agoThe rule is pretty simple: if you have fields which may potentially contain integers with outside of +-2*48 range, or floating points where you care about NaNs -- then do the safe thing and store them as strings. That one simple rule ensures your data won't be clobbered and you program won't crash. (And yes, your serialization/deserialization code will need to contain explicit string conversions. But even with that extra conversion, Javascript is way more interoperable with anything and future-proof than any alternative like protobuf or msgpack)
- swatcoder 3y ago> you should never use it if you care about your data Eh, warts abound all over technology and ubiquity is worth a lot. But because data is important, everybody who does use JSON should make sure they serialize their data in a way the format won’t break. I’ve heard it can be done.
- 3y ago
- fanf2 3y agoPostgres jsonb is described in https://github.com/postgres/postgres/blob/master/src/include/utils/jsonb.h https://github.com/postgres/postgres/blob/master/src/include...