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