access the results of an sql query as json from javascript

[flosigime]

Hello,

I put this here, as a new conversation, i think it can be useful.

I have a query like this :

select json_group_array(json_object('table', nomtable, 'champ', nomchamp)) 
			from (SELECT m.tbl_name as 'nomtable', p.name AS 'nomchamp'
					FROM sqlite_master AS m 
					JOIN pragma_table_info(m.tbl_name) AS p 
					WHERE m.type='table' and m.tbl_name in('t_us', 't_fait')
					ORDER BY m.tbl_name, p.cid)

The result is a json array like this :

[{"table":"t_fait","champ":"id_fait"},{"table":"t_fait","champ":"numfait"},{"table":"t_fait","champ":"interpret_alter"},{"table":"t_fait","champ":"orient"},{"table":"t_fait","champ":"orient_calc"},{"table":"t_fait","champ":"descrip"},{"table":"t_us","champ":"note"},{"table":"t_us","champ":"num_seq"},{"table":"t_us","champ":"ordre_seq"},{"table":"t_us","champ":"geometry"}]

how can i get this result in a page, inside the html code ? can i use the text component ?

Thank you again

[lovasoa]

Hello!
Did you have a look at the json component?
https://sql.ophir.dev/documentation.sql?component=json#component

my_api.sql

SELECT 'json' AS component,
  json_group_array(json_object(
    'table', m.tbl_name,
    'champ',  p.name
  ) order by m.tbl_name, p.cid) as contents
FROM sqlite_master AS m 
JOIN pragma_table_info(m.tbl_name) AS p 
WHERE m.type='table' and m.tbl_name in('t_us', 't_fait')

It allows you to make your query results available as a small API.

Then, in JavaScript, you can use:

data.js

const res = await fetch("my_api.sql");
const my_data = await res.json();

[flosigime]

Yes, i tried it and it works, but i absolutely dont know how to use an api Thanks you for the JavaScript. I'll try this. Le dim. 24 mars 2024, 07:43, Ophir LOJKINE ***@***.***> a écrit :

…

Hello! Did you have a look at the json component? https://sql.ophir.dev/documentation.sql?component=json#component It allows you to make your query results available as a small API. Then, in JavaScript, you can use: const res = await fetch("my_file.sql");const my_data = await res.json(); — Reply to this email directly, view it on GitHub <#274 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOYEXY4LTKRBEED7L7MGXE3YZZYYZAVCNFSM6AAAAABFE6T3VKVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DQOJQHE4DI> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[flosigime]

you don't need to precise "as contents" in my_api.sql query ?

[lovasoa]

You do. I slightly simplified your query from above

SELECT 'json' AS component,
  json_group_array(json_object(
    'table', m.tbl_name,
    'champ',  p.name
  ) order by m.tbl_name, p.cid) as contents
FROM sqlite_master AS m 
JOIN pragma_table_info(m.tbl_name) AS p 
WHERE m.type='table' and m.tbl_name in('t_us', 't_fait')

[flosigime]

I didn't saw the 'as contents'...oups...

[flosigime]

maybe it will be a good example in the doc, for the json component, on how to create a json from a table :

SELECT
 'json' as component, 
 json_group_array(json_object('field1', m.myfield1, 'field2', m.myfield2 ) as contents
FROM my_table as m
WHERE m.myfield1 like 'my_value'

and get the result. If the table contains constructors and cars :

[
  {"constructor":"Mercedes","Car":"SLK"},
  {"constructor":"Ferrarri","Car":"F40"},
  {"constructor":"Honda","Car":"NSX"}
]

[lovasoa]

Do you find the existing example (in https://sql.ophir.dev/documentation.sql?component=json#component) unclear ?

[flosigime]

i think that it's better to have a lot of examples. And a second example, with a sql query, give a second point of view, an "how to when my data are in a database".