6 ms·
JSONB – Request for evaluation and comment
- ks2048 3y agoI 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...
- Sytten 3y agoThis is very exciting! The pace of sqlite development is suprisingly fast for a project that mature. The only real type that I miss from SQLite is a native datetime. Currently each library uses a different parsing strategy and it's a mess. I fixed the diesel (rust) implementation recently and boy was that not fun.
- Aeolun 3y agoDoesn’t it use ISO format everywhere? I thought that was pretty well standardized at this point.
- giraffe_lady 3y agoIt's pretty standardized in the sense that like, that's what most sqlite users choose to do for dates. But it doesn't have a datetime type, and has native functions for generating both iso strings and unix epoch numbers. It really is kind of up to and on the user to decide and stick to one approach. So you definitely do see differences between programming languages based on how their sqlite libraries chose to do it. Or between libraries in the same language, if there are multiple good choices.
- em500 3y agoSQLite has no date/time types, only strings and numbers, and functions that can interpret certain strings or numbers as date/times. That design choice actually made a lot of sense in the past, because unlike most other databases SQLite didn't have rigid typed columns. Now you could roll your own rigid column types using CHECK constraints, but then you would just use the date/time functions to enforce date/times column values, so there was no need for separate date/time datatypes. But recently they've implementedn STRICT tables with rigid column types, so maybe they'll add date/time datatypes at some point as well.
- euroderf 3y agoAs I understand the docs... You can use "DATE" and "DATETIME", and they default to NUMERIC affinity, and therefore a string that can't be parsed as a number will be parsed as TEXT, which defaults to ISO-8601.
- busymom0 3y agoFor those using SQLite, what's the biggest database you have used it for?
- sbstp 3y agoExpensify has blogged[0] about having terabyte sized SQLite databases. [0] https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...
- tantalor 3y agoHere's a tip. If storage size and performance is your concern then don't use JSON.
- paulddraper 3y agoThis tip would be better with a prescription as well
- tantalor 3y agoUse a serialization format designed to optimize storage or performance. There are many of those. Comparing JSONB to JSON is missing the point. You have to compare it to MessagePack and Thrift and Protobuf.
- cr125rider 3y agoMongo’s BSON seems to work well in their space. Is that applicable at all here?
- maxbond 3y agoIt's worth noting that BSON is not equivalent to JSON. All BSON objects are documents. JSON objects can be documents, lists, strings, numbers, null, or booleans. Eg, `null` is valid JSON, as is `"foo"`, but neither can be expressed in BSON. So if your criterion is 1:1 compatibility with JSON, BSON doesn't get you there. (BSON is awesome, though.)
- tonyg 3y agoThis is actually very nice! It exposes all of the awkward areas of JSON without trying to paper over any of them, while still achieving its goals. In particular it doesn't try to define any kind of an equivalence over JSON objects outside trivial syntactic equivalence.