- April 2, 2021
- Posted by: Sibi Elamparithi
- Category: APEX
In most cases, REST API’s output is in the form of JSON. Oracle APEX’s REST Data Source utility has inbuilt functionalities to read this JSON and parse the values as separate columns. But in case if your JSON is a multidimensional array, the parsing needs to be done manually for getting custom column values.
In this example, I will be using a free weather api source to display climate conditions as a report in APEX.
To begin with, I am creating a REST Data Source with default means.
Sample API – http://api.openweathermap.org/data/2.5/weather
In the above, REST Data Source Discovery Panel, we could see the columns which are parsed by default, but the actual data needed is the weather max and min temperature. Hence, we need to do the parsing manually to get the desired output.
For now, let us create the REST call with default columns discovered and will modify it in the next step. Go on and click Create REST Data Service.
Once created, select the REST service to enter the Edit mode. In the Data Profile section, click on Edit Data Profile. In the Rediscovery pane, click on Rediscover Data Profile. A popup opens up. In the Response Body tab, you will be able to see the complete output JSON from the API call. In my case it looks like below,
{“coord”:{“lon”:80.2785,”lat”:13.0878},
“weather”:[{“id”:802,”main”:”Clouds”,”description”:”scattered clouds”,”icon”:”03d”}],
“base”:”stations”,
“main”:{“temp”:310.15,”feels_like”:314.51,”temp_min”:312.15,”temp_max”:312.15,”pressure”:1006,”humidity”:32},
“visibility”:6000,
“wind”:{“speed”:3.09,”deg”:300},
“clouds”:{“all”:48},
“dt”:1617342993,
“sys”:{“type”:1,”id”:9218,”country”:”IN”,”sunrise”:1617323659,”sunset”:1617367820},
“timezone”:19800,”id”:1264527,”name”:”Chennai”,”cod”:200}
In the response above, we need temp_min, temp_max, wind_speed as the column data which we need to parse manually.
Click on Edit Data Profile -> Click Add Column. In the Add Column Dialog box, enter the details a below.
The selector info is the key to get the required column. In this case the temp_min is under the parent key main. So hence my selector is main.temp_min. Click on Create.
Similarly add columns for Max Temperature and Wind Speed.
After adding the required columns, click on Apply Changes. Now let us use the created columns in our APEX page.
Create an Interactive Report and select REST API as the source and choose the Weather Report as the source API which has been created above.
Save the Page and run to see the desired results.
Please feel free to reach Cittabase for more information. Thanks for checking my blog …Have a nice day !!!!