Saving JSON in local db

Question

I have implemented code to store the JSON data returned to be stored in local DB (SQFlite). I want to store only those data where the is_deleted parameter is 0. I am not sure how to implement that. Any solution will be of great help.

This is the JSON :

"subject": [
        {
            "univ_spec_sub_id": "53",
            "univ_year_sem_id": "18",
            "sem_id": "2",
            "university_id": "5",
            "master_course_id": "36",
            "subject_name": "Programming and Problem Solving",
            "subject_desc": "99",
            "info": "Programming and Problem Solving using Python",
            "no_units": "6",
            "is_deleted": "0",
            
        },
        {
            "univ_spec_sub_id": "59",
            "univ_year_sem_id": "18",
            "sem_id": "2",
            "university_id": "5",
            "master_course_id": "37",
            "subject_name": "Basic Electrical Engineering",
            "subject_desc": "99",
            "info": "",
            "no_units": "100",
            "is_deleted": "0",
            
        },
        {
            "univ_spec_sub_id": "61",
            "univ_year_sem_id": "18",
            "sem_id": "2",
            "university_id": "5",
            "master_course_id": "38",
            "subject_name": "Engineering Mathematics II",
            "subject_desc": "99",
            "info": "",
            "no_units": "6",
            "is_deleted": "0",
            
        },
        {
            "univ_spec_sub_id": "65",
            "univ_year_sem_id": "18",
            "sem_id": "2",
            "university_id": "5",
            "master_course_id": "39",
            "subject_name": "Engineering Graphics",
            "subject_desc": "99",
            "info": "",
            "no_units": "6",
            "is_deleted": "1",
        }
    ],

I have implemented the following code to store the JSON returned into local DB.

Future<Semdata> semdata(String url, {Map body} ) {
    return http.post(url,
          body:body).then((http.Response response){
      if (response.statusCode < 200 || response.statusCode > 400 || json == null) {
      throw new Exception("Error while fetching data");
    }
    //JsonDecoder().convert(response.body);
    var extractdata = json.decode(response.body);
    List subdata = extractdata["subject"];
    Map<String, dynamic> decodedData = json.decode(response.body);

    
    for(Map<String, dynamic> subjectMap in decodedData['subject']){
      print(subjectMap["subject"]["is_deleted"]);
      if(subjectMap["subject"]["is_deleted"]== "0"){
        db.savesubject(subjectMap);
      }
      // db.savesubject(subjectMap);
      
    }
    return Semdata.fromJson(json.decode(response.body));
    
    });
          
  }

Answer 1

(rewriting my response since I think I got it all wrong, I guess I got confused by the multiple same calls to json.decode(response.body)).

Here I'm assuming you have not started the database yet...

One solution is create a table with all the subjects field. Here I'm assuming univ_spec_sub_id is the primary key (unique).

var db =
    await openDatabase(path, version: 1, onCreate: (db, version) async {
  await db.execute('''
CREATE TABLE Subject (
    univ_spec_sub_id TEXT PRIMARY KEY,
    univ_year_sem_id TEXT,
    sem_id TEXT,
    university_id TEXT,
    master_course_id TEXT,
    subject_name TEXT,
    subject_desc TEXT,
    info TEXT,
    no_units TEXT,
    is_deleted TEXT);
  ''');
});

When receiving the data, you should decode it, keep items with 'is_deleted' == '0' and add item to the database in a transaction

// Decode the body
var responseData = jsonDecode(response.body) as Map;

// Only keep data with 'is_deleted' == '0' in a list
var subjects = (responseData['subject'] as List)
    .map((raw) => (raw as Map)?.cast<String, dynamic>())
    .where((map) => map['is_deleted'] == '0');

// Save subjects in a transaction
if (subjects.isNotEmpty) {
  await db.transaction((txn) async {
    for (var subjects in subjects) {
      await txn.insert('Subject', subjects);
    }
  });
}