Menu
how do I store a JSON Object in an SQLite database? What is the correct way?
one place is the blob type column. if i can convert the JSON object into byte array and use Fileoutputstream
the other idea is to store in a text column as a String
import org.json.JSONObject; JSONObject jsonObject; public void createJSONObject(Fields fields) { jsonObject = new JSONObject(); try { jsonObject.put("storedValue1", fields.storedValue1); jsonObject.put("storedValue2", fields.storedValue2); jsonObject.put("storedValue3", fields.storedValue3); jsonObject.put("storedValue4", fields.storedValue4); jsonObject.put("storedValue5", fields.storedValue5); jsonObject.put("storedValue6", fields.storedValue6); } catch (JSONException e) { e.printStackTrace(); } } 
968
SQLite stores JSON as ordinary text. Backwards compatibility constraints mean that SQLite is only able to store values that are NULL, integers, floating-point numbers, text, and BLOBs. It is not possible to add a sixth "JSON" type. SQLite does not (currently) support a binary encoding of JSON.
The SQLite json_insert() function allows us to insert a new value into a JSON document. We pass the original JSON as the first argument when we call the function, followed by a path that specifies where to insert the new value, followed by the value to insert. We can also insert multiple key/value pairs if required.
Basically, in document based db's, you store data in json files and then you can query on these json files. The Second model is the popular relational database structure. If you want to use relational database like MySql then i would suggest you to only use second model.
Click the Add button and select Column. On the Column element, specify values for the Index and Value attributes. Click the Add button in the sub-menu and select Add Same. Repeat the last two steps to add additional columns and elements from the JSON file.
Convert JSONObject into String and save as TEXT/ VARCHAR. While retrieving the same column convert the String into JSONObject.
For example
Write into DB
String stringToBeInserted = jsonObject.toString(); //and insert this string into DB Read from DB
String json = Read_column_value_logic_here JSONObject jsonObject = new JSONObject(json); An alternative could be to use the new JSON extension for SQLite. I've only just come across this myself: https://www.sqlite.org/json1.html This would allow you to perform a certain level of querying the stored JSON. If you used VARCHAR or TEXT to store a JSON string you would have no ability to query it. This is a great article showing its usage (in python) http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
