How to store JSON object in SQLite database

Question

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();
    }
}

[JSON is *a textual representation of data*](http://json.org). 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()`](http://www.json.org/javadoc/org/json/JSONObject.html#toString()). — user2246674, May 17 '13 at 07: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 — Ankitkumar Makwana, May 17 '13 at 07:39

score 109 · Accepted Answer · answered May 17 '13 at 07:27

109

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);

answered May 17 '13 at 07:27

Pankaj Kumar

81,967
29
167
186

2

Are you aware of any performance issues by inserting it like that? – WJA Apr 01 '15 at 13:15
@JohnAndrews no. If you found, please update here :) – Pankaj Kumar Apr 01 '15 at 13:21
Will do. I am using that method to store base64 converted strings of images... so will see what happens – WJA Apr 01 '15 at 13:23
2

@JohnAndrews Sure. waiting for your valuable response. But better way to store image is using blob. Read http://stackoverflow.com/questions/9357668/how-to-store-image-in-sqlite-database. – Pankaj Kumar Apr 02 '15 at 05:27
Thanks for that! How does a blob differ from a base64 encoded imagedata string? Or is the same? – WJA Apr 04 '15 at 08:14
I should be faster than base64 incoding. Here no need of string coversion or encoding/ decoding. As you can see into link which I posted in my previous comment – Pankaj Kumar Apr 06 '15 at 06:04
6

SQLite has a new JSON extension. See oisin's comment. – coleifer Nov 19 '15 at 14:16
1

@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. – Pankaj Kumar Nov 20 '15 at 06:03
2

If you bother reading the docs, there is no SQLite json data type. Just a collection of functions. – coleifer Nov 20 '15 at 16:15
SQLite JSON data type: https://nelsonslog.wordpress.com/2015/09/22/json1-a-sqlite-extension/ – CAD bloke Jun 25 '17 at 09:49
My assumption: this will store data of JSONobject's each element value to Sqlite database respective column name. isn't it? is this true? – Qadir Hussain Jul 07 '17 at 12:21
Is is possible to convert a json object into a string and store that data into sqlite database ? If it is possible then can we use that string when we have no internet connection in the app for offline mode. @PankajKumar – Shehram Tahir Jul 22 '17 at 19:41
@ShehramTahir Yes you can. Depending your requirement you can cached json response when it was last working... and that can be used in either error cases or can show offline data. – Pankaj Kumar Jul 23 '17 at 07:49
If the json is storing some urls of multiple images. Then in online mode it fetches data from internet against those urls of images. But how can we show those urls data in offline mode ? @PankajKumar – Shehram Tahir Jul 23 '17 at 10:25
what if the JSON had 300 key/value pairs, does this affect it? – Seamus.Reeve Apr 17 '19 at 17:30
@Seamus.Reeve definitely the data size will play a role in performance. Even parsing JSON to POJO or POJO to JSON will take more time than smaller data size. – Pankaj Kumar Apr 18 '19 at 04:23
How can you do that in JavaScript? – Benjamin Heinke Jan 05 '20 at 03:21

Oisin · Answer 2 · 2015-11-18T15:44:11.387

49

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/

edited Nov 18 '15 at 15:44

answered Nov 18 '15 at 15:30

Oisin

1,483
12
15

1

And here is a possible way to do it Android using NDK: https://www.sqlite.org/android/doc/trunk/www/index.wiki – dev.bmax Mar 03 '16 at 11:44
9

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. – ypnos Jan 21 '20 at 09:47

score 5 · Answer 3 · answered May 17 '13 at 07:26

5

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

answered May 17 '13 at 07:26

Pradeep

2,530
26
37

Daniel F · Answer 4 · 2019-08-07T19:30:48.563

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.

https://www.sqlite.org/json1.html (store and query JSON documents)
https://www.sqlite.org/fts3.html (perform full-text searches)

score -13 · Answer 5 · answered Jan 06 '15 at 15:49

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

How to store JSON object in SQLite database

5 Answers5

Linked