[malkia]

I don't know the rationale, but I would assume exact preservation of data maybe the case.

Does json (yaml/others) have well defined handling of ints/floats? Especially ints with more than 52bits set?

[nycdotnet]

This sounds right. This JSONB seems to be oriented not around semantic parsing, just structural parsing. https://sqlite.org/draft/jsonb.html

Note too that JSON doesn’t really have ints or floats - the number type in JSON doesn’t specify a max size. Most implementations of course do rely on the native number types of their platform, so this implementation choice for SQLite allows them to keep a simpler implementation that sidesteps a lot of complexity that would come from deeper parsing.

See the number section on json.org https://www.json.org/json-en.html

[ncruces]

The rational was to design a format that can serve as a (flattened) parse tree for the JSON.

JSON handling functions in SQLite took in textual JSON and mostly spat out JSON text. So their structure was: (1) parse JSON, (2) massage in memory representation, (3) serialize JSON.

If you can come up with a format that can serve as the in memory representation, and persist that to disk, your functions can skip (1) and (3), and focus on (2).

Still, many times you'll need JSON text at the boundary, so making (1) and (3) fast are needed to.

Parsing and formatting numbers can also be an unnecessary expense, if you do that more often than you actually need it.