JSONB – Request for evaluation and comment | Hacker News Reader

JSONB – Request for evaluation and comment

sqlite.org

84 pointspolyrand58 comments

I see that Postgres also has a binary JSON format, and also is called JSONB (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).

> 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).

Agreed, 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?

Not for databases but for messages, ala msgpack

Sure, maybe, but neither Postgres's nor SQLite's binary format are supposed to ever leak to the outside; it is strictly an implementation detail.

> Not for databases

What’s the thought pattern here? Different implementations of different message formats should have a common format for representing… JSON? Why?

Uh 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

So, 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.

Surely the thing that would benefit from standardization is the SQL-level stuff use JSON values, like indexing, and path expressions.

From 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)

> 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.

What's the alternative to json?

There's tons of alternatives. BSON, CBOR, and protobuf are three arbitrary ones.

XML (:

On the database?

There's actually quite a lot of XML/XSLT support in various proprietary databases (eg RDF databases), and there's Postgres support too:

https://www.postgresql.org/docs/current/functions-xml.html

Iirc XML support in Postgres predates JSON support.

Woah! Ok I didn't know that. How's the performance?

Very XML'ish

MessagePack - https://msgpack.org

Most binary JSON variants including MessagePack deviate from the spirit of JSON in one fundamental way: they are not simple. The JSON file format can accurately be described with a few simple syntax diagram that fit comfortably on a printed page and the spec can be implmented with very little code. A binary file format comes with the theoretical further simplicity advantage that you can leave out conversion of numbers, detection of string length, (un)escaping and dealing with white space/pretty printing. Instead of taking that as gains and be done with it, such formats start introducing (space) microoptimizations, such as many different number formats, variable length numbers for sizes, a wealth of additional data formats such as dates, etc.

I think there a two good variants of binary JSON equivalents: one where you have a minimal implementation that makes both reading and writing with the lowest amount of code possible, potentially feeding a SAX-like interface or constructing an in memory representation (DOM-style).

The other one would be to facilitate lookups in large documents such as json_extract without parsing the whole document. That would require some clever layout of the data, potentially even as in-file hash tables or similar. I assume that is what SQLite has done.

TBF&H, when you say additional data formats such as dates, dates (including time) are the one format that would be great to have standardized in JSON, due to their inherent complexity. A format for binary data is also missing, but that is downright trivial to do ad-hoc (string literals + encoding hint) when compared to datetimes.

I don't know, it is trivially simple to choose either Unix timestamp (which is relatively close to what MessagePack is doing) or JSs format [1] if you like a bit more readability and time zone deltas. Or any of the zillion more complex formats such as ISO 8601, etc. In my opinion that would be part of the schema and not the file format.

[1] https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...

I wrote what I think may be the simplest possible serialization format, though I don't expect it to be a standard anytime soon

https://github.com/civboot/zoa

that is one of the more complex serialization formats I have seen.. custom backslash-escapes, 13 operators, "variable compiler", string concatenation, user-defined types (somehow only in binary protocol), two types of maps, special "file path" type...

I could say many things about it, but "simplest possible" is not one of them.

I misspoke, I should have said protocol. I'm referring to the bytecode not the text format. I'm probably throwing away the text format, for the reasons you outlined

As I said, I mispoke. Here's how I think a JSON equivalent format should be structured: https://github.com/civboot/zoa/blob/main/notes/dynamic.md

"Simple" in that it's trivial to decode with a bytestream and a jump table, and it can be written down very briefly.

You inspired me to re-examine it. I wrote up the new design here:

https://github.com/civboot/civlua/blob/main/zoa/README.md

It's still in it's initial stages, but I'd love feedback :D

> this all is a reminder that JSON is a horrible format and you should never use it if you care about your data

Works on my machine.

JSON 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.

and 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?

The 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)

> JSON fails to define "what is a string?" and "what is a number?"

Can you elaborate on this? It seems pretty clear from the spec to me: stuff starting with " is a string, stuff starting with - or a digit is a number.

"What is a number?"

Min value? Max value? Precision (and in what base)?

Simple, the RFC even calls that out: assume 64 bit float as specified by IEEE and as basically supported everywhere. Why should JSON do anything else than the rest of the world does?

64 bit IEEE 754 floating point, as per the spec.

Anything else is non-standard.

Datadog APIs use 64 bit ints.

PostgreSQL also doesn't follow your "standard".

The "standard" is called JSON, and it's not mine, it's made by the IETF.

Don't know what a "Datadog" is, and I don't care about Postgres idiosyncrasies.

Okay, let's quote the RFC then.

> A number is represented in base 10 using decimal digits. It contains an integer component that may be prefixed with an optional minus sign, which may be followed by a fraction part and/or an exponent part.

> This specification allows implementations to set limits on the range and precision of numbers accepted. Since software that implements IEEE 754 binary64 (double precision) numbers [IEEE754] is generally available and widely used, good interoperability can be achieved by implementations that expect no more precision or range than these provide, in the sense that implementations will approximate JSON numbers within the expected precision.

That sure doesn't sound like numbers are 64 bit floats, according to the spec.

> That sure doesn't sound like numbers are 64 bit floats, according to the spec.

It can't be 64 bit floats, because they are represented as decimal numbers, not binary. You will be surprised to also hear that most base conversion algorithms used are not exact, and so different implementations might give different results in the LSB(s) for some numbers for performance reasons. Here practicality prevails, because making the standard stricter would make its implementation much harder in many environments. And for many applications it isn't a problem. I am sure btw that most file formats that use decimal representations for floating point numbers have this property, otherwise exact base conversion would be more prominently accessible in more programming languages.

I have some hope that this will change in the future, as exact base conversion (lossless round trip) will gain momentum now that it landed in standard C++. Once most common programming languages have this feature, there isn't a big reason to not modify the standard anymore to enforce lossless round-trip.

> I don't care

I got that.

The stuff between the "" is important: it must be unicode.

Except not really. Under what circumstances are surrogates allowed? Under what circumstances are controls allowed?

>8.1. Character Encoding

> JSON text exchanged between systems that are not part of a closed ecosystem MUST be encoded using UTF-8 [1].

I don't know what you mean by controls (you mean unicode control characters, for which the spec explicitly states that they are to be escaped?), but since JSON should really only be exchanged as UTF-8, surrogate pairs play no role.

[1] https://www.rfc-editor.org/rfc/rfc8259

Postgres jsonb is described in https://github.com/postgres/postgres/blob/master/src/include...

This 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.

Doesn’t it use ISO format everywhere? I thought that was pretty well standardized at this point.

It'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.

SQLite 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.

As 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.

This 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.

Here's a tip. If storage size and performance is your concern then don't use JSON.

This tip would be better with a prescription as well

Use 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.

Mongo’s BSON seems to work well in their space. Is that applicable at all here?

It'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.)

For those using SQLite, what's the biggest database you have used it for?

Expensify has blogged[0] about having terabyte sized SQLite databases.

[0] https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

Reply on news.ycombinator.com