[Solved] SQLite to JSON
lazarus / 16-Databases

----------------------------------------------------------------------
lainz · 2020-11-03 13:24

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 · 2020-11-03 14:38

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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i -- 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 · 2020-11-03 17:00

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 · 2020-11-03 17:18

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 · 2020-11-04 00:57

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

----------------------------------------------------------------------
nasa · 2022-04-06 21:04

Quote from: Okoba on November 03, 2020, 03:38:20 pmWhy 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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i -- 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.

Hi for everyone!!

I´m having a problem using the json_group_array on SQLite select, i
din´t test on Postgres yet
I'm not able to read the content of the resulting field
Using TField.asString  fails, using TDataset.savetoStream also fail,
and TField.getData() fails too.
The string read is incomplete and can't be converted to TJSONArray
object
But if i dont use json_group_array and only use json_object() i need
to iterate over the resulting records and build the array
concatenating the rows, and it works when converted to TJSONArray.
How can i solve this, i really don't want to iterate the records, it´s
way more fast only read the field value and convert it to TJSONArray.
Why cannot read from TField.asString or TField.AsWideString  all this
fails.
Thanks.

----------------------------------------------------------------------
Zvoni · 2022-04-07 08:00

Quote from: nasa on April 06, 2022, 11:04:47 pmHow can i solve this,
Thanks.
By reading the documents: https://www.sqlite.org/json1.html
QuoteThe JSON functions and operators are built into SQLite by
default, as of SQLite version 3.38.0 (2022-02-22). They can be omitted
by adding the -DSQLITE_OMIT_JSON compile-time option. Prior to version
3.38.0, the JSON functions were an extension that would only be
included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option
was included. In other words, the JSON functions went from being opt-
in with SQLite version 3.37.2 and earlier to opt-out with SQLite
version 3.38.0 and later.