result in json and subsequent processing

[SyntaxEvg]

Hello, I would like to know if I can get json from my sample

1. get json, write it to a file
2. is it possible to use sqlpage to open this json, read the contents, and visualize it on graphs?
3. Can I save the result of an sql sample into a variable and then somehow analyze and build graphs?
   example:
   
   
   

[lovasoa]

Hello and welcome to SQLPage!
Can you give some details about what data you have, where, and what you are trying to do with it?

It looks like your data is in a Microsoft SQL Server database, and you want to run some analysis over it in SQL, and display the results as graphs. Is that so? If that's the case, then you don't really need json anywhere, do you?

You can just use the normal chart component and feed it data from your database using sql directly.

And if you need to reuse the same intermediate results to build multiple graphs, the most natural way to do it is to build a view in your database, and query that view in SQLPage, not to "save the result in a variable".

If you really need to query or produce JSON, you can use your database's built-in functions to do so, but I really don't think that's what you want here.

If you want an example of using SQLPage to produce graphs based on analytics data in a database view, you can check out this one (which uses SQLite, not SQL server, but the principles are the same) : https://github.com/lovasoa/SQLpage/tree/main/examples/splitwise

[SyntaxEvg]

thanks for the quick response, unfortunately I didn’t receive a response from you, I’ll try to give you the data
The first picture shows a query to the database and its output to the log
It turns out that the final array is formed like this:
{"year_group":2024,"month_group":3,"range_group":"20-40","WinCount":177,"WinVipStavkaCount":5,"LosCount":261,"LosVipStavkaCount":5,"TotalWinCount" :8683,"TotalWinVipStavkaCount":475,"TotalLosVipStavkaCount":452,"TotalLosCount":10445,"TotalAllCount":19128,"AverageRangeAll":2.2418036529680356,"AverageRangeWin":2.154350282485876," AverageRangeLos":2.3011111111111107},{"year_group" :2024,"month_group":1,"range_group":"40-60","WinCount":1151,"WinVipStavkaCount":76,"LosCount":1180,"LosVipStavkaCount":90,"TotalWinCount":8683," TotalWinVipStavkaCount":475,"TotalLosVipStavkaCount":452,"TotalLosCount":10445,"TotalAllCount":19128,"AverageRangeAll":2.0155855855855873,"AverageRangeWin":1.9750564726324935,"AverageRangeLo s":2.0551186440677998}...
My task is to save this data to a json file? how to log information, and the second task is to provide a visual response as a graphic, look at the dynamics of changes, by parameters
year_group
month_group
range_group
WinCount
Unfortunately, I don’t know how this can be done using sqlPage
Can you help me ?

[lovasoa]

For the json file, do you need to download it to your application user's computer, or to save it to the server where SQLPage is running?

• if you need to generate the json on the server and let the user download it immediately, you can use the json component.
• if you need to save it to the server, then you can use the sqlpage.exec function
• For the graph, you can use the graph component, you just need to change your query to return the data in the format expected by the component

If you encounter issues while writing your queries, please paste your SQL, with a screenshot of the error you are getting here !

[SyntaxEvg]

For the json file, do you need to download it to your application user's computer, or to save it to the server where SQLPage is running?

• if you need to generate the json on the server and let the user download it immediately, you can use the json component.
• if you need to save it to the server, then you can use the sqlpage.exec function
• For the graph, you can use the graph component, you just need to change your query to return the data in the format expected by the component

If you encounter issues while writing your queries, please paste your SQL, with a screenshot of the error you are getting here !

When executing this item from the standard example, did you receive the following error?
"if you need to save it to the server, then you can use the sqlpage.exec function"

[SyntaxEvg]

Forgive me, but I found your product to be very complex, I cannot use it to perform simple business tasks, such as
will contact the server, from the received response, receive a json array, save this json into a file with the json extension, then add a button to open the file and load it into the chart, please confirm that your product can do this, thank you!

[lovasoa]

I'm sorry to hear you are finding SQLPage complex ! Its goal is to make web application creation easier, not more difficult. If you have any suggestion about how to improve the documentation, they are welcome. Even better, if you want to improve the official website, it is open-source too, so you can contribute.

SQLPage is not a commercial product, it is an open source tool driven by a community of volunteers. It is provided as-is, without any guarantees or engagement. If you need personalized support, and tailored help, I'd be happy to setup a contract for that ! Feel free to contact me on contact@ophir.dev.

Saving the results of a query to a json file and then using that to build a graph is unusual and a little opposite to the philosophy of simplicity behind SQLPage. Is there a reason you want to do that rather than just build the graph directly from your SQL query without the intermediary json export ?

If that is really what you want, however, it is definitely possible to achieve with SQLPage. You would use

• SQL server builtin json capability to generate the json,
• then sqlpage.exec to run a command to save the json to a file,
• then once again SQL server builtin json capability together with sqlpage.read_file_as_text to parse the json back,
• and then the SQLPage chart component to build the graph.

But once again, this is very unnecessarily complicated if you just want to show your query results as a graph. You can show a graph just by adding select 'chart' as component and a few AS to your existing query.

If you need paid personalized support, feel free to send an email to contact@ophir.dev