How to store JSON object in SQLite database

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

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/

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

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)

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