SQLite Forum

Efficiency of updates versus replace when using the JSON1 extension functions.
Login
You are safe to assume that when you update something the entire record must be retrieved and a new entire record must be composed and that the entire record is re-written.

That is to say that if you have a record with 47 fields and you update one of them, then the entire record of 47 fields must be retrieved, the one you want to update is updated, then the entire record of 47 fields is re-composed into a record structure, and that record is re-written.

There are optimizations which may occur to prevent pages on which the content has not changed from being re-written, but this applies ONLY if the entire page after image is exactly the same as the entire page before image.  

There are also optimizations which may take place to minimize the "shuffling" of data in and out of overflow -- however you should assume that making a change to a part of a record will require I/O of the whole thing.

So updating something within a JSON text string means:  
 - retrieve the entire record  
 - parse the JSON string*  
 - fiddle with the JSON string*  
 - recompose the record  
 - write the updated record

The fiddle-faddling with the contents of the JSON string (the parts marked with the *) will occur no matter how you slice it -- it just depends whether you want that fiddling about to occur within the SQLite3 library or within your code.  In either case, if the content of a field changes the entire record must be retrieved and recomposed and rewritten, no matter the "method" by which you "change the field".

All I/O is by page so if data on a page is needed, then that page must be read (if it is not already in the connection page cache).  If a page "changes" (becomes after a change different than it was before the change) then the entire page will be written from the connection page cache to disk.  Note that transaction logging may require that the "pre-change image" and/or the "after change image" of a page may need multiple reads and writes if a page contains changed data.