Forum > Databases

[Solved] SQLite to JSON

<< < (2/2)

nasa:

--- Quote from: Okoba 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  [+][-]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.

--- End quote ---

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:

--- Quote from: nasa on April 06, 2022, 11:04:47 pm ---How can i solve this,
Thanks.

--- End quote ---
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.

--- End quote ---

Navigation

[0] Message Index

[*] Previous page

Go to full version