Sqlite select as JSON

Anthony007 · March 12, 2022, 12:45pm

Hi All

i there a way to get a SQLlite select to return as JSON?

Thanks t

Still-learning · March 12, 2022, 12:51pm

May be other way will be there, however if you adjust your return response like this code, may be you could get it… pls ref here

gist.github.com

https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2

sqlite_to_json.sql

-- 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 data
CREATE TABLE users (
  id INTEGER PRIMARY KEY NOT NULL, 
  full_name TEXT NOT NULL, 
  email TEXT NOT NULL,

This file has been truncated. show original

Anthony007 · March 12, 2022, 12:55pm

Thanks

thats all very well if one had low level access to the SQLlite datebase and table query which one doesnt (as far as I can tell)???

the QUERY block is obviously not written this way

SELECT

json_group_array(

json_object(‘full_name’, full_name, ‘created’, created)

) AS json_result

FROM (SELECT * FROM users ORDER BY created);

UNLESS

I use

elementblock014
and do a JSON QUERY???

What do you think>

Still-learning · March 12, 2022, 1:05pm

why dont you try and test it?

Anthony007 · March 12, 2022, 1:16pm

just did. Nothing came back

Does this query make sense?

SELECT json_group_array(
json_object(
‘tank_house_no’, tank_house_no,
‘tank_street_name’, tank_street_name,
‘tank_unique_id’, tank_unique_id)
)
AS
json_result FROM (SELECT * FROM online_offline)

TimAi2 · March 12, 2022, 1:30pm

Anthony007 · March 12, 2022, 2:00pm

Thanks… will check it out.

but bfr i leave…

all in a days work when you dont know WHAT YOU ARE DOING

Anthony007 · March 12, 2022, 2:05pm

Nope… still no clue!

It cant be THAT difficult~~!!!

TimAi2 · March 12, 2022, 3:10pm

Unless you have a rooted phone and the correct tools, you won’t be able to compile a version of sqlite on your device that has json format output. Even then, this will only work on that device…

Anthony007 · March 13, 2022, 1:05am

Thanks…

I tried everything and eventually gave up on that one.

So the question remains.

Is there no way to return the data in JSON format in SQLlite which is crazy because one might have thought a RAW QUERY would have been just that! Obviously NOT!

t

Genesis_Records · July 6, 2023, 9:56am

Make a dictionary as the table names, then as values , you can select all from tables from index 2.
Thats how i did it