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.
Does json (yaml/others) have well defined handling of ints/floats? Especially ints with more than 52bits set?
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
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.