I am trying to consume external API in Oracle Apex. API returns JSON like this:
{
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
"key5": "value5",
...
}
When creating REST Data Source auto data discovery made like 300 columns. Obviously there are only two. What is proper JSON selector to extract information in proper way?
The reason why a REST Data Source returns a column for each attribute is that your JSON is not an array with each member having two attributes. In this case, the JSON would look like this:
[
{"key": "key1", "value": "value1" },
{"key": "key2", "value": "value2" },
:
]
Instead it’s an object, with many attributes. For a REST Data Source, this JSON represents a single line, with as many columns as there as “key” attributes. APEX REST Data Sources use the JSON_TABLE SQL function to parse the JSON, and with that function you’d also only be able to extract a single row with multiple attributes.
To get a result with only two columns you’d need to parse the JSON yourself using custom code – here is an example …
select key,
value
from xmltable(
'/json//*'
passing apex_json.to_xmltype(
apex_web_service.make_rest_request('https://...api-url', 'GET' ) )
columns
key varchar2(255) path 'name()',
value varchar2(255) path 'text()' )
/
2