How to store JSON data into database table in flutter using SQFLite or Moor Library

Question

How do I insert the list of String in database in flutter app, I tried saving it in string datatype by encoding the json array into the string, but then the skills array requires explicit decoding every time, like below whenever I need the object back from database.

List<User> userResponse = await tempDatabase.allItems;
      jsonData = Result.fromJson({
        "name": userResponse[0].name,
        "skills": jsonDecode(userResponse[0].skills)
      });

This is my json response

{
  "result": [
    {
      "name":"Sidhant Rajora",
      "skills": [
        "C++",
        "Java",
        "Python",
        "React"
      ]
    },
    {
      "name":"Adity Rajora",
      "skills": [
        "C++",
        "Java",
        "Python"
      ]
    }
  ]
}

I have this kind of JSON response and the model PODO created by it is like

class UsersJson {
  List<Result> result;

  UsersJson({this.result});

  UsersJson.fromJson(Map<String, dynamic> json) {
    if (json['result'] != null) {
      result = new List<Result>();
      json['result'].forEach((v) {
        result.add(new Result.fromJson(v));
      });
    }
  }

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    if (this.result != null) {
      data['result'] = this.result.map((v) => v.toJson()).toList();
    }
    return data;
  }
}

class Result {
  String name;
  int id;
  List<String> skills;

  Result({this.name, this.skills});

  Result.fromJson(Map<String, dynamic> json) {
    name = json['name'];
    skills = json['skills'].cast<String>();
  }

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data['name'] = this.name;
    data['skills'] = this.skills;
    return data;
  }
}

and now I am not sure about the approach should i take to insert the model into database and get it back from database. I have tried using the SQFLite library as well as Moor Library(https://moor.simonbinder.eu)

@SidhantRajora: May I know which NoSQL database you are using for Flutter? — Raghunandan R, Nov 11 '19 at 17:02
as far as I tried to solve the problem out, I found that sembast worked for the case that I had to deal, moor is a completely different game. — Sidhant Rajora, Jul 23 '21 at 17:44

score 2 · Answer 1 · answered Aug 23 '19 at 11:45

2

In moor you can use TypeConverter. Convert any type into a supported moor column type. For example you can stringify json and store it in a TextColumn. See the docs for how to. But with deeply nested json data it might get a bit tricky to encode and decode.

answered Aug 23 '19 at 11:45

Newton Munene

145
3
12

I have got the concept `TypeConverter` and followed the tutorial but I am not able to store and retrieve the data i.e I am not able to write the queries for it. Can you please help ? – nimi0112 Jan 22 '20 at 04:11

score 1 · Answer 2 · answered Jul 29 '19 at 19:02

1

I have a similar problem I'm dealing with while learning Flutter, SQLite, Moor et al. and the only solution I can see working for me is joining two separate tables, one table contains the names and ids and the other one contains two kinds of ids, one unique identifier for each skill, and one "grouping" id that links a bunch of them to the first table. So for your case it should be something like this:

answered Jul 29 '19 at 19:02

Reda Lazri

131
3

I am well aware of joins and foreign keys, but the data that i am going to store, is in json format, and have complex structure, with nested objects and arrays, and in that case table creation and record insertion and fetch will be harder. – Sidhant Rajora Aug 03 '19 at 05:53

How to store JSON data into database table in flutter using SQFLite or Moor Library

2 Answers2