SQLite Insert Multiple rows from JSON object ios

Question

I am getting json data over http using NSURLSession class

    NSString *url = @"http://10.0.0.25/Website/database.php";

NSURLSessionTask *task = [[NSURLSession sharedSession] dataTaskWithURL:[NSURL URLWithString:url] completionHandler:^(NSData * _Nullable data, NSURLResponse * _Nullable response, NSError * _Nullable error) {
    dispatch_async(dispatch_get_main_queue(), ^{ 
        NSString *str = [[NSString alloc]initWithData:data encoding:NSUTF8StringEncoding];
        NSLog(@"String Output: %@",str);


    });
}];
[task resume];

i am getting the following sample output below:

{ "client_id":"12", "finger_print_code":"", "national_id":"28811982" }, { "client_id":"32", "finger_print_code":"", "national_id":"293239323" }

i would like to get the above data from JSON string or object into my SQLite Database. Someone suggested FMDB, but i am not quite familiar with it.

  query = [NSString stringWithFormat:@"insert into clientInfo values(null, %d, %d, '%@')", [importedID intValue], [nationalID intValue], fingerPrintCode];

This is how to insert multiple data, but i would like to learn how to insert data when its from JSON, and insert it at once or at the same time. Here is one of examples but it did not prove to be helpful.

score 1 · Accepted Answer · answered Oct 29 '19 at 09:35

Assuming that sample data is actually a JSON array

[{ "client_id":"12", "finger_print_code":"", "national_id":"28811982" },
 { "client_id":"32", "finger_print_code":"", "national_id":"293239323" }]

and the version of sqlite you're using has the JSON1 extension enabled, you can do something like

INSERT INTO clientInfo(client_id, finger_print_code, national_id)
SELECT json_extract(j.value, '$.client_id')
     , json_extract(j.value, '$.finger_print_code')
     , json_extract(j.value, '$.national_id')
FROM json_each(?) AS j;

where you bind the string holding the JSON array to the parameter in the prepared statement created from the above query. Adjust column names as needed (Since you didn't give a table definition I guessed).

@bots_developer_fix It's just the alias used for the rows returned by `json_each()`. See the linked documentation for details. Or a basic sql tutorial if you've never seen `AS` or don't know what an alias is. — Shawn, Oct 29 '19 at 12:31

score 0 · Answer 2 · answered Oct 30 '19 at 07:21

Thank you for your answer. This is how i implemented it in Objective-c incase one might want to know how.In this scenario i used a custom database class to communicate with my SQLite Database.

        NSString *queryInsert;
    queryInsert = [NSString stringWithFormat:@"INSERT INTO clientInfo(clientID, nationalID, fingerPrintCode) SELECT json_extract(j.value, '$.client_id') , json_extract(j.value, '$.national_id') , json_extract(j.value, '$.finger_print_code')  FROM json_each('%@') AS j", my_json_array];
    [self.dbManager executeQuery:queryInsert];

Where my_json_array is exactly that the jsonArray of data i get from my server. I didnt know much about JSON1 extension, thank you for your assistance and for sharing that knowledge.

SQLite Insert Multiple rows from JSON object ios

2 Answers2