Processing json object received via sqlpage.fetch()

[rstocks]

Hi folks,

I'm sure I'm missing something really obvious, but this has me stumped...

I'm making an API request using sqlpage.fetch, and getting a fairly large and complex json object back. Part of that object (and the bit I actually care about!) is an array of data. I can dump that out to a code component, no problem.

How do I actually use it for something useful? For example, use the array as the source for a list? I don't want to insert that data into a table, I just need to display it.

Thanks in advance!

[lovasoa]

Hi!
I wrote an article about that https://sql-page.com/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide

Let me know if it's useful or if there are things I should change.

[rstocks]

Hi!

Thanks for the speedy reply!

Again, I think I must be missing something here :-)

That article covers off dealing with json coming from different database platforms, and the specific functions each platform makes available - but in this instance I'm not talking to a database. I don't have access to my test box at the moment, so I can't provide the exact code, but essentially:

set request = json_object(
    'method', 'GET',
    'url', 'https://example.com/api/give-me-data',
    'headers', json_object(
        'Accept', 'application/json'
    )
);
set api_results = sqlpage.fetch($request);

select 'code' as component;
select
    'API call results' as title,
    'json' as language,
    $api_results as contents;

The code block shows the data I just got back, so I know that part is working fine. Where I'm getting stuck is then using that result to (say) populate a list from an array in the result.

Thanks!

[lovasoa]

The various json functions documented in the article are not restricted to working with values coming from the database, and they work just as well with values coming from SQLPage.

Example

Here is an example using SQLite json functions to display an array from inside an API call result as a list using the default sqlpage list component :

set api_results =
    sqlpage.fetch('https://pokeapi.co/api/v2/pokemon?limit=10');

select 'list' as component, 'Pokémons' as title;
select 
    value->>'name' as title,
    value->>'url' as link
from json_each($api_results->'results');

(using https://pokeapi.co/ as an example api)

Results

https://editor.datapage.app/edit

[rstocks]

Awesome - I think it was some nuance of the syntax that I wasn't hitting. I'll give it a whirl when back at the dev box!

Thanks again!