Extract data from API response JSON Array in ODBC

nayan · March 27, 2024, 9:37pm

Problem

If you are using ODBC JSON driver to read data from API in Apps like Power BI, Informatica, Crystal Reports, Python etc and you have changing schema in response based on success or failure then this article will explain how to use few techniques to extract data from nested array.

For example API call sends two different responses like below… If we like to extract properties from the errors array (e.g. jsonPath, message) then check the solution described later in this topic

Success Response

{
   "status": "SUCCESS",
   id:1,
   fname:"bob"
}

Error Response

{
   "status": "ERROR",
   id:1,
   "errors": [
       {
           "jsonPath": "$.abcd",
           "message": "Invalid value entered ",
           "messageType": "ERROR"
       }
   ]
}

Solution

Use JSON_VALUE and JSON_ARRAY_FIRST function
Use META clause to avoid META data request (we can manually supply meta)
Notice we included errors column in the select list becuase its used in Function. You have to defined same column in META too.

SELECT status
,id
,fname
,errors --THIS must be present in output (if used in calculated columns below)

--below 3 cols will be null if success response
,JSON_VALUE(json_array_first(errors),'$.jsonPath') as err_jsonPath
,JSON_VALUE(json_array_first(errors),'$.message') as err_message
,JSON_VALUE(json_array_first(errors),'$.messageType') as err_messageType
FROM $ 
WITH 
(
Filter='',
DATA='
{
   "status": "ERROR",
   id:1,
   fname:"bob",
   "errors": [
       {
           "jsonPath": "$.abcd",
           "message": "Invalid value entered ",
           "messageType": "ERROR"
       }
   ]
}'
,
META='status:string(20);id:int;fname:string(20);errors:string(2000);err_jsonPath:string(1000);err_message:string(1000);err_messageType:string(100)'
)