[Solved] SQLite to JSON

[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  [Select][+][-]

1. -- When SQLite is compiled with the JSON1 extensions it provides builtin tools
2. -- for manipulating JSON data stored in the database.
3. -- This is a gist showing SQLite return query data as a JSON object.
4. -- https://www.sqlite.org/json1.html
5.  
6. -- An example table with some data
7. CREATE TABLE users (
8.   id INTEGER PRIMARY KEY NOT NULL,
9.   full_name TEXT NOT NULL,
10.   email TEXT NOT NULL,
11.   created DATE NOT NULL
12. );
13. INSERT INTO users
14. VALUES
15. (1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),
16. (2, "Angus O'Vader", "angus.o@destroyers.com", "02-03-04"),
17. (3, "Imperator Colin", "c@c.c", "01-01-01");
18.  
19. -- Get query data as a JSON object using the
20. -- json_group_object() [1] and json_object() [2] functions.
21. SELECT
22. json_group_object(
23.         email,
24.         json_object('full_name', full_name, 'created', created)
25. ) AS json_result
26. FROM (SELECT * FROM users WHERE created > "02-01-01");
27.  
28. -- 1 rows returned
29. -- json_result
30. -- {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}}
31.  
32. -- Get query data as a JSON object using the
33. -- json_group_array() function to maintain order.
34. SELECT
35. json_group_array(
36.         json_object('full_name', full_name, 'created', created)
37. ) AS json_result
38. FROM (SELECT * FROM users ORDER BY created);
39.  
40. -- 1 rows returned
41. -- json_result
42. -- [{"full_name":"Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}]
43.  
44. -- Links
45. -- [1] https://www.sqlite.org/json1.html#jgroupobject
46. -- [2] https://www.sqlite.org/json1.html#jobj
47.  

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 

[nasa]

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][+][-]

1. -- When SQLite is compiled with the JSON1 extensions it provides builtin tools
2. -- for manipulating JSON data stored in the database.
3. -- This is a gist showing SQLite return query data as a JSON object.
4. -- https://www.sqlite.org/json1.html
5.  
6. -- An example table with some data
7. CREATE TABLE users (
8.   id INTEGER PRIMARY KEY NOT NULL,
9.   full_name TEXT NOT NULL,
10.   email TEXT NOT NULL,
11.   created DATE NOT NULL
12. );
13. INSERT INTO users
14. VALUES
15. (1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),
16. (2, "Angus O'Vader", "angus.o@destroyers.com", "02-03-04"),
17. (3, "Imperator Colin", "c@c.c", "01-01-01");
18.  
19. -- Get query data as a JSON object using the
20. -- json_group_object() [1] and json_object() [2] functions.
21. SELECT
22. json_group_object(
23.         email,
24.         json_object('full_name', full_name, 'created', created)
25. ) AS json_result
26. FROM (SELECT * FROM users WHERE created > "02-01-01");
27.  
28. -- 1 rows returned
29. -- json_result
30. -- {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}}
31.  
32. -- Get query data as a JSON object using the
33. -- json_group_array() function to maintain order.
34. SELECT
35. json_group_array(
36.         json_object('full_name', full_name, 'created', created)
37. ) AS json_result
38. FROM (SELECT * FROM users ORDER BY created);
39.  
40. -- 1 rows returned
41. -- json_result
42. -- [{"full_name":"Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}]
43.  
44. -- Links
45. -- [1] https://www.sqlite.org/json1.html#jgroupobject
46. -- [2] https://www.sqlite.org/json1.html#jobj
47.  

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]

How can i solve this,
Thanks.

By reading the documents: https://www.sqlite.org/json1.html

The 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.