Forum > Databases

[Solved] SQLite to JSON

(1/2) > >>

lainz:
Hi, I have a SQLite database, I use that for a local Rest API that provides the SQLite tables to web applications and android applications.

What I do is load the database into a bufdataset then convert the dataset to JSON.

There is a faster way? When there are a lot of items it takes more time.

Of course keep using the SQLite. Thanks.

Okoba:
Why don't you use the SQLite itself to generate the JSON? Write a query that makes the output string for you. It can make an object per record and in the end, you can sum them up.
Or using JSON1 extensions to do it easier:

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ----- When SQLite is compiled with the JSON1 extensions it provides builtin tools-- for manipulating JSON data stored in the database. -- This is a gist showing SQLite return query data as a JSON object.-- https://www.sqlite.org/json1.html -- An example table with some dataCREATE TABLE users (  id INTEGER PRIMARY KEY NOT NULL,   full_name TEXT NOT NULL,   email TEXT NOT NULL,   created DATE NOT NULL);INSERT INTO users VALUES (1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),(2, "Angus O'Vader", "angus.o@destroyers.com", "02-03-04"),(3, "Imperator Colin", "c@c.c", "01-01-01"); -- Get query data as a JSON object using the -- json_group_object() [1] and json_object() [2] functions.SELECT json_group_object(        email,         json_object('full_name', full_name, 'created', created)) AS json_resultFROM (SELECT * FROM users WHERE created > "02-01-01"); -- 1 rows returned-- json_result-- {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} -- Get query data as a JSON object using the -- json_group_array() function to maintain order.SELECT json_group_array(        json_object('full_name', full_name, 'created', created)) AS json_resultFROM (SELECT * FROM users ORDER BY created); -- 1 rows returned-- json_result-- [{"full_name":"Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}]  -- Links-- [1] https://www.sqlite.org/json1.html#jgroupobject-- [2] https://www.sqlite.org/json1.html#jobj Source.

lainz:
Thanks, maybe is the best solution, currently I've updated the process to use sqlquery instead of bufdataset, (previously we first query into the sqlquery and then copy to tbufdataset). Is already faster but it uses about 30% cpu of an i7 with 8 cores, and our customers doesn't have that machine :(

So maybe we need to update our dll and use the json extension.

Okoba:
Welcome.
As I said you do not need to update the dll, just write the record to object code in SQL, it may be ugly but it will work and faster.

lainz:
Many thanks, seems that our dll already had the JSON support  :)

Navigation

[0] Message Index

[#] Next page

Go to full version