API call to populate form fields returns error

[chris-bragg-sift]

Hi, all, may I say what an excellent project SQLpage is, I have been looking for a way of building a crud frontend for my database for a while and I think SQLPage is going to be great.
One question I have is API calls.... I have studied the sqlpage.exec() option and cannot get this to work with my setup. I am using PGSQL10 as the database backend, but however I structure it I cannot pass the JSON return results into the form. I assume I am doing something wrong.

-- This returns a JSON data structure, how do I pass this into a form.
select 'text' as component, sqlpage.exec('/www/lookup.php', 'xxxxx') as contents;

I modify as per the example.

select 'text' as component, value->>'status' from json_each( sqlpage.exec('/www/lookup.php', 'xxxxx') );

I get the following error.

error returned from database: function json_each(text) does not exist
function json_each(text) does not exist

Essentially can we use data returned from an api as JSON in SQLpage for any components.

Any help greatly appreciated, of course this process may not be possible.

[chris-bragg-sift]

Actually may have worked it out for myself.
So we get a value from a form field submitted run the exec call write it into a table in the database backend then query that table.#

Insert the record into a dedicated table first,

INSERT into audience.jsontest (data) values ( sqlpage.exec( 'curl','-u','xxxxxxxxxxxxxxxx:','https://api.companieshouse.gov.uk/company/xxxxxx' )::JSON );

query the new table to pull back required values.

select 'card' as component;
select data->>'company_number' as title, data->>'company_name' as description
from audience.jsontest where data->>'company_number'='xxxxxx'

Display in form field.

[lovasoa]

Hello and welcome to sqlpage !

All sqlpage pseudo-functions are executed inside sqlpage and their results are passed to the database as text. The error message function json_each(text) does not exist comes from the fact that sqlpage.exec(...) is a value of type text and json_each expects a value of type json. You can cast values with

select ... from json_each( sqlpage.exec(...)::json );

If you want to reuse a value without creating an intermediate table, you can use variables in sqlpage with set:

set target_url = 'http://example.com/api/company/' || $name;
set json_results = sqlpage.exec('curl', $target_url);

select 'card' as component;
select key as title, value as description
from json_each($json_results::json);

[chris-bragg-sift]

If you have a multi dimensional json array returned, accessing the individual values then becomes.

select 'card' as component;
select max($json_results::json->>'company_number') as title, max($json_results::json->'registered_office_address'->>'postal_code') as description;

[chris-bragg-sift]

I know this is stretching the original question ... how can I loop through an array of results and present in a table.

SET $json_result='
{
  "name": "employees",
  "items": [
    {
        "name": "JOE BLOGGS",
        "occupation": "Consultant"
    },
    {
        "name": "JOE DOE",
        "occupation": "Another Consultant"
    }
    ]
}'

How can I unnest an array within the json object, (below does not work) any suggestions greatly appreciated as ever.

select $json_result::json->>'name' as employees, $json_result::json->>'items'->>'name' as name, $json_result::json->>'items'->>'occupation' as occupation from json_each($json_result::json)

[chris-bragg-sift]

Worked it out anyway for anyone who is interested.

set json_result='{
  "name": "employees",
  "items": [
    {
        "name": "JOE BLOGGS",
        "occupation": "Consultant"
    },
    {
        "name": "JOE DOE",
        "occupation": "Another Consultant"
    }
    ]
}';

function json_array_elements() (would be good to know what native functions are supported, presumably this is down to the underlying lib being used ?

select
 max($json_result::json->>'name') as recordset,
 d.elem::json->>'occupation' AS occupation,
 d.elem::json->>'name' AS name
from
 json_each($json_result::json) t,
 json_array_elements($json_result::json->'items') d(elem)
group by 2,3;

[lovasoa]

Yes, you can find the list of json functions in the postgres documentation:

https://www.postgresql.org/docs/9.3/functions-json.html

[lovasoa]

Your query above (https://www.db-fiddle.com/f/nNnoFtHYyPzdJWMGqrDFid/0) can be simplified to

select
 $json_result::json->>'name' as recordset,
 item->>'occupation' AS occupation,
 item->>'name' AS name
from
 json_array_elements($json_result::json->'items') as items(item)

( https://www.db-fiddle.com/f/x7x7rSMHMK1LRVmYJiujLL/0 )