How to store json array elements to sqlite db

Question

I would to parse the following json and add it to sqlite database.

{
  "group_id": "12",
  "group_name": "ABC Group",
  "employees": [
    {
      "domain_id": "123",
      "from": "abc@gmail.com",
      "to": "def@gmail.com"
    },
    {
      "domain_id": "123",
      "from": "abc@gmail.com",
      "to": "def@gmail.com"
    }
  ]
}

The problem is that I'm not sure how the table design has to be for adding all of those details especially json array values. What is an efficient table design for such a json

http://stackoverflow.com/questions/16603621/how-to-store-json-object-in-sqlite-database — sasikumar, Mar 03 '16 at 11:17
Why don't you save it as a normal string and parse it when you fetch it from the DB. — Rohit5k2, Mar 03 '16 at 11:18
I save it as a string. I want to know the efficient table design for the strings that I get by parsing the json — , Mar 03 '16 at 11:19
For that we need to know what all you want to save. Little broad to answer and also opinion based. — Rohit5k2, Mar 03 '16 at 11:22
There is a thing called Jackson Parser. You can use object mappers to map your json to a class object. You need to design a class according to your database schema. — Ameya Kulkarni, Mar 03 '16 at 11:27

score 0 · Answer 1 · answered Mar 03 '16 at 11:32

There are a couple of scenarios:

You have few database writes and many reads (with possible filtering, sorting, grouping). In this case, it's better to parse the JSON into POJO (using the GSON library for example) and to save them as table rows in a relational way (table groups, table employees);
You have many database writes and few reads. In this case, you can save the JSON string as plain text and parse them when needed (you can get away with a single table).

score 0 · Accepted Answer · answered Mar 03 '16 at 11:34

From what I understand, create 2 tables namely - Group and Employee.

In your Group table just create 2 columns - id and name.

In this make id the Primary Key for this table.

In your Employee table create 4 columns - domain_id, from, to and group_id.

The group_id field in the Employee table will act as a Foreign Key to the id field of the Group table.

How to store json array elements to sqlite db

2 Answers2