112

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(); } }
Improve this question
2
  • 5
    JSON is a textual representation of data. So save it in a TEXT column / type affinity. Encoding it as a BLOB just makes more work/pain - don't do that! Of course, get the actual JSON from the JSONObject (which is not JSON). That can be done with jsonObject.toString(). Commented May 17, 2013 at 7:23
  • if Sqlite is not compulsory used then my suggestion is that write File.txt file and save whole Response in file and Read it when need Commented May 17, 2013 at 7:39

5 Answers 5

Reset to default
115

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

18 Comments

Are you aware of any performance issues by inserting it like that?
@JohnAndrews Sure. waiting for your valuable response. But better way to store image is using blob. Read stackoverflow.com/questions/9357668/….
SQLite has a new JSON extension. See oisin's comment.
@coleifer Still this is not supported in Android. By Reading oisin's answer and attached link, JSON is a type which can be used to store Json data into Sqlite, but in Android still this is not clear that which data type will support 'JSON' type of SQLite.
If you bother reading the docs, there is no SQLite json data type. Just a collection of functions.
|
54

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/

Improve this answer

2 Comments

And here is a possible way to do it Android using NDK: sqlite.org/android/doc/trunk/www/index.wiki
From the first page you linked: "The json1 extension (currently) 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." So yes, you are supposed to store JSON in a TEXT column, even if you would like to use the JSON extension functionality.
5

There is no data types for that.. You need to store it as VARCHAR or TEXT only.. jsonObject.toString();

Improve this answer

Comments

1

https://github.com/requery/sqlite-android allows you to query JSON fields (and arrays in them, I've tried it and am using it). Before that I was just storing JSON strings into a TEXT column. It supports FTS3, FTS4, & JSON1

As of July 2019, it still gets version bumps every now and then, so it isn't a dead project.

Improve this answer

Comments

-14

https://github.com/app-z/Json-to-SQLite

At first generate Plain Old Java Objects from JSON http://www.jsonschema2pojo.org/

Main method

void createDb(String dbName, String tableName, List dataList, Field[] fields){ ...

Fields name will create dynamically

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.