3 ms·
There’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.
by oefrha 3y ago
There’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.
- lifthrasiir 3y agoWhich occurs with JSON as well (SQLite doesn't have a dedicated JSON nor JSONB type). The only actual cost would be the conversion between JSONB and JSON.
- oefrha 3y agoNo, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.
- lifthrasiir 3y agoThat's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.
- oefrha 3y agoThere’s no point using json_* functions if you’re always reading/writing the full blob.
- lifthrasiir 3y agoOf course (and I never said that), but if you need to store a JSON value in the DB and use a JSONB-encoded BLOB as an optimization, you eventually read it back to a textual JSON. It's just like having a UNIX timestamp in your DB and converting back to a parsed date and time for application uses, except that applications may handle a UNIX timestamp directly and can't handle JSONB at all.
- akira2501 3y agoYes, but when you use the BLOB with jsonb functions, your application demands go from: Read JSON from TEXT column. Parse JSON into Internal Binary Format. Run json_*() function on this format, which will Serialize Internal Binary Format to JSON as output. To: Read JSONB from BLOB column. Run json_*() function on Internal Binary Format, which will serialize the Internal Binary Format to JSON as output. Because: The json_* and jsonb_* all accept _either_ JSON or JSONB as their input. The difference is jsonb_* functions also produces it as output. So even in the above case, if your function output is just being used to feed back into another table as a BLOB, then you can use the jsonb_* version of the function and skip the serialization step entirely.
- lifthrasiir 3y agoOh, you are right! I later looked at the draft documentation and realized that `json(b)_` only determines the output type. That said, it is still true that you need one more function call `json(...)` to retrieve a textual form of JSON which you do often need instead of JSONB.
- Someone 3y ago> Run json_*() function on Internal Binary Format, which will serialize the Internal Binary Format to JSON as output I don’t think that’s always correct (it is within this thread, which stated (way up) “if you’re always reading/writing the full blob”, but I think this discussion is more general by now). Firstly, it need not convert the full jsonb blob (example: jsonb_extract(field,'$.a.b.c.d.e')) Secondly, it need not convert to text at all, for example when calling a jsonb_* function that returns jsonb. (Technically, whether any implementation is that smart is an implementation detail, but the article claims this is “a rewrite of the SQLite JSON functions that, depending on usage patterns, could be several times faster than the original JSON functions” so it can’t be completely dumb.
- nonethewiser 3y agoIt doesnt validate that its valid json?
- ElectricalUnion 3y agoNo, it doesn't. By default in sqlite3, you can store whatever you like in whatever field you want. The "built-in validation mechanism" is invoking json_valid(X) [1] call within the CHECK [2] condition on a column. [1] https://www.sqlite.org/json1.html#jvalid https://www.sqlite.org/json1.html#jvalid [2] also assumes you didn't disable CHECKs with PRAGMA ignore_check_constraints https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints https://www.sqlite.org/pragma.html#pragma_ignore_check_const...