I have a table with name "vehicles" and column "status" with BLOB type,
inside in column I have this values:
{"status": "available", "condition": "new"}
Can some help understand how I can replace values into that column to this one
{"status": "available", "condition": "new", "color": 'red'}
sometimes I need to insert new values and sometimes remove some values, can somebody please help me write the query to that?
Thank you
That is JSON, which is a textual format, so you should be storing it as text, not blobs. Doing so means you can use the sqlite JSON1 extension's functions to make manipulating the values easy.
Examples:
sqlite> CREATE TABLE vehicles(id INTEGER PRIMARY KEY, status);
sqlite> INSERT INTO vehicles(status) VALUES ('{"status": "available", "condition": "new"}');
sqlite> UPDATE vehicles SET status = json_insert(status, '$.color', 'red') WHERE id = 1;
sqlite> SELECT * FROM vehicles;
id status
---------- ------------------------------------------------------
1 {"status":"available","condition":"new","color":"red"}
sqlite> UPDATE vehicles SET status = json_remove(status, '$.condition') WHERE id = 1;
sqlite> SELECT * FROM vehicles;
id status
---------- ------------------------------------
1 {"status":"available","color":"red"}
