Recent

Author Topic: [Solved] SQLite to JSON  (Read 1991 times)

lainz

  • Hero Member
  • *****
  • Posts: 3966
[Solved] SQLite to JSON
« on: November 03, 2020, 02:24:18 pm »
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.
« Last Edit: November 04, 2020, 01:57:48 am by lainz »

Okoba

  • Sr. Member
  • ****
  • Posts: 304
Re: SQLite to JSON
« Reply #1 on: November 03, 2020, 03:38:20 pm »
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.
« Last Edit: November 03, 2020, 03:43:53 pm by OkobaPatino »

lainz

  • Hero Member
  • *****
  • Posts: 3966
Re: SQLite to JSON
« Reply #2 on: November 03, 2020, 06:00:35 pm »
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

  • Sr. Member
  • ****
  • Posts: 304
Re: SQLite to JSON
« Reply #3 on: November 03, 2020, 06:18:16 pm »
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

  • Hero Member
  • *****
  • Posts: 3966
Re: SQLite to JSON
« Reply #4 on: November 04, 2020, 01:57:37 am »
Many thanks, seems that our dll already had the JSON support  :)

nasa

  • New Member
  • *
  • Posts: 21
Re: SQLite to JSON
« Reply #5 on: April 06, 2022, 11:04:47 pm »
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

  • Hero Member
  • *****
  • Posts: 1261
Re: SQLite to JSON
« Reply #6 on: April 07, 2022, 10:00:15 am »
How can i solve this,
Thanks.
By reading the documents: https://www.sqlite.org/json1.html
Quote
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.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018