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

Sagar Pilkhwal · Accepted Answer · 2016-04-26T08:37:30.363

Reference Link

private JSONArray getResults()
{

String myPath = DB_PATH + DB_NAME;// Set path to your database 

String myTable = TABLE_NAME;//Set name of your table

//or you can use `context.getDatabasePath("my_db_test.db")`

SQLiteDatabase myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY); 

String searchQuery = "SELECT  * FROM " + myTable;
Cursor cursor = myDataBase.rawQuery(searchQuery, null );

JSONArray resultSet     = new JSONArray(); 

cursor.moveToFirst();
while (cursor.isAfterLast() == false) {

            int totalColumn = cursor.getColumnCount();
            JSONObject rowObject = new JSONObject();

            for( int i=0 ;  i< totalColumn ; i++ )
            {
                if( cursor.getColumnName(i) != null ) 
                { 
                    try 
                    { 
                        if( cursor.getString(i) != null )
                        {
                            Log.d("TAG_NAME", cursor.getString(i) );
                            rowObject.put(cursor.getColumnName(i) ,  cursor.getString(i) );
                        }
                        else
                        {
                            rowObject.put( cursor.getColumnName(i) ,  "" ); 
                        }
                    }
                    catch( Exception e )
                    {
                        Log.d("TAG_NAME", e.getMessage()  );
                    }
                } 
            } 
            resultSet.put(rowObject);
            cursor.moveToNext();
        } 
        cursor.close(); 
        Log.d("TAG_NAME", resultSet.toString() );
        return resultSet;  
}

This is from the article [Convert database cursor result to Json Array](http://tech.sarathdr.com/android-app/convert-database-cursor-result-to-json-array-android-app-development/). It is good to post the reference link when you copy paste. — Madan Sapkota, Jul 01 '15 at 09:38
Nice one buddy. - just one amend. to get your database path please use=> context.getDatabasePath("my_db_test.db") - do not hardcode as /data/data.... — Thiago, Apr 26 '16 at 03:55

score 13 · Answer 2 · 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 3 · answered Jul 09 '19 at 21:13

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 1 · Answer 4 · 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 5 · 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

Austine Iyke

488
2
9
16

score 1 · Answer 6 · 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

569
1
5
17

Convert SQLite to JSON

6 Answers6

Linked