[radarsat1]

I find it really odd that the decision was made to store ints and floats as text in JSONB. It seems to defeat a lot of use cases for it as far as I can tell. There are few solutions for storing and retrieving/querying unstructured numerical data.

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

[nick_]

IIRC Sqlite stores all data types as text. It seems like a very weird decision to me.

[ncruces]

This is wrong. Integers (in SQLite columns) are stored as varints and floats as IEEE 754 doubles. Numbers in JSONB are stored as text.