Convert SQLite to JSON

Question

Is there a way to convert sqlite to json? All other questions are parsing json and saving to sqlite. I can't seem to find any reference on this, please help me.

I have a sqlite db inside the app and i need it to be converted to json, upgrade db version, parse earlier converted json and add another table. Any suggestions on how should I do this?

Thanks in advance.

you can get an ArrayList from your Db and with a library like Gson or Jackson convert it to JSON. — Arash GM, Sep 08 '14 at 10:52
I'm not sure I understand why this is the first time someone has asked this question, because don't you have to do this in order to sync a MySQL database to an Android SQLite database?? — Lou Morda, Oct 27 '14 at 01:41
Best answer AFAIK is https://stackoverflow.com/a/67186486/5134302 — amka66, Nov 03 '22 at 13:55

score 22 · Answer 1 · answered Nov 09 '18 at 18:13

Quick and easy way if you don't feel like coding it:

Download DB Browser for SQLite: https://sqlitebrowser.org/ (yes, it's free)
Open the SQLite DB
Go to File > Export > Table(s) to JSON
Voila

Beware, for some reason it does not correctly convert NULL values. It converts this to an empty string... Besides that it works like a charm as far as I now.

score 3 · Answer 2 · edited Mar 13 '23 at 04:56

sqlite, already have json1 extension, you could use:

// https://www.sqlite.org/json1.html
// https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2
var _sql = "SELECT json_group_array( json_object('id', id, 'name', name)    ) AS json_result FROM (SELECT * FROM ipfs ORDER BY id); ";

sql.js does not support json1 extension,

my working example convert raw record to json

function json1_extension(_records){
  var _json = [];
  var _columns = _records[0].columns
  var _values = _records[0].values

  for (var i = 0; i < _values.length; i++) {
    //console.log(_values[i]);
    var _row_json = {};
    var _row = _values[i];
    for (var k = 0; k < _row.length; k++) {
      _row_json[_columns[k]] = _row[k]
    }
    //console.log('_row_json...',_row_json);
    _json.push(_row_json)
  }
  return _json
}

score 2 · Answer 3 · answered Oct 17 '16 at 15:59

static JSONObject cursorToJson(Cursor c) {
    JSONObject retVal = new JSONObject();
    for(int i=0; i<c.getColumnCount(); i++) {
        String cName = c.getColumnName(i);
        try {
            switch (c.getType(i)) {
                case Cursor.FIELD_TYPE_INTEGER:
                    retVal.put(cName, c.getInt(i));
                    break;
                case Cursor.FIELD_TYPE_FLOAT:
                    retVal.put(cName, c.getFloat(i));
                    break;
                case Cursor.FIELD_TYPE_STRING:
                    retVal.put(cName, c.getString(i));
                    break;
                case Cursor.FIELD_TYPE_BLOB:
                    retVal.put(cName, DataUtils.bytesToHexString(c.getBlob(i)));
                    break;
            }
        }
        catch(Exception ex) {
            Log.e(TAG, "Exception converting cursor column to json field: " + cName);
        }
    }
    return retVal;
}

score 1 · Answer 4 · answered Jul 31 '19 at 09:27

1

To convert SQLite to JSON, you could use this python tool: https://github.com/Austyns/sqlite-to-json-python

you could also use this online tool https://data-converters.web.app/ if you don't want to code

answered Jul 31 '19 at 09:27

Austyns

712
2
13
21

score 1 · Answer 5 · answered Jan 10 '20 at 18:38

1

This npm package does the job. It can convert your sqlite file to json easy. It also provides functions to manipulate the output.

https://www.npmjs.com/package/sqlite-json

answered Jan 10 '20 at 18:38

Mano Haran

634
1
6
19

Convert SQLite to JSON

5 Answers5

Linked