How to make a Second API call based on the first API Response (URL Join)?

ODBC PowerPack
, , ,
1

Sometimes, you may need to retrieve a list of data in the initial API call and then dynamically make a second API call based on the response. This involves passing specific data, such as IDs, from the first API response to dynamically construct and execute the second API call.

ZappySys Join Query

By using a join query, you can perform the first API call and smoothly send the obtained data to the second API call, whether it’s placed in the header or embedded in the body.

In simple terms, this method is called URL JOIN, which is used in the context of APIs. The purpose of this method is to efficiently transfer information from a main or “parent” URL to a secondary or “child” API URL using a single query.

Join Query Example

SELECT 
    c.custid,c.name,c.city
    ,o.orderid,o.orderdate,o.total
    /* ,i.itemid,i.qty,i.shipped */
FROM $
WITH(
    --1st level (root)--
	 src='https://zappysys.com/downloads/files/test/join/customers.json'
    ,NextUrlAttributeOrExpr='$.nextpage'
	,filter='$.customers[*]'
    ,alias='c'
    ,WaitTimeMs='500'
    
    --2nd level--
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] )
    ,join1_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.json'
    ,join1_filter='$.orders[*]'
    ,join1_alias='o'
    ,join1_NextUrlAttributeOrExpr='$.nextpage'    
    ,join1_WaitTimeMs='500'

    /*
    --3rd level-- 
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$])
    ,join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.json'
    ,join2_filter='$.items[*]'
    ,join2_alias='i'
    ,join2_NextUrlAttributeOrExpr='$.nextpage'    
    ,join2_WaitTimeMs='500'

    --maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx
    -- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx
    */
)

Now, let’s break it down:

  1. URL JOIN Pattern:
    This is a technique for combining or joining different parts of web addresses (URLs). It enables the passing of information from a main URL to a secondary URL in one go.

  2. Data Passing:
    Information from the main URL can be sent to the secondary URL either through the URL itself or in the body of the request (useful for POST requests).

  3. Placeholder Syntax:
    A specific way of indicating where the data from the main URL should be inserted into the secondary URL. The format for this indication is [$parent_alias.parent_col_name$].

  4. Alias Attribute:
    Each level or stage of this data transfer (presumably organized hierarchically) must be identified by an ALIAS attribute.

  5. Prefixing JOIN:
    At levels other than the main or root level, a specific prefix (JOINxxxxx) needs to be added before each attribute name.

  6. Function Usage:
    Functions can be applied when passing data from the main URL to the secondary URL. For instance, an example is given: [$a.firstname,FUN_TRIM$].

  7. Information Source:
    For more details about this method, a link to the API JOIN Pattern is provided: API JOIN Pattern.

  8. Placeholder Functions:
    A resource is shared for users to explore a variety of functions that can be used within these placeholders: Placeholder Functions.

  9. Example with BASE64DEC Function:
    A practical example is provided, demonstrating the use of the BASE64DEC function in a secondary URL: BASE64DEC Example.

You can explore additional SQL query examples in the driver-specific help file, such as the one provided for the JSON driver.

URL JOIN Custom Metadata for Child URL and final output

This example shows how to perform URL JOIN and supply custom META for joined URL (child) and final output (SELECT list). Using this pattern you can control datatype parsing in child levels. Notice that in first META we included CUST_ID and CUST_CITY. This is aliased columns from SELECT list. You must use exact name from final output. Any columns (aliased or non-aliased) you intend to use in Final SELECT include in META clause to control exact Metadata. If you skip Output META and only use JOIN1_META (for child) then datatypes for final result will be detected automatically and sometimes length is set to 16MB max to support long string. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

SELECT 
 _pid,q._rid
, r.custid as CUST_ID, r.city as CUST_CITY
, q.VAL1, q.VAL2, q.VAL3, q.DESCR 
FROM $
ORDER BY _pid,q._rid
WITH (

 SRC='https://zappysys.com/downloads/files/test/join/customers.json'
,filter= '$.customers[*]'    
,ALIAS='r'

,JOIN1_DATA='{"ORDERS":{"ROW":[
{"VAL1":"HINTROW","VAL2":"000100","VAL3":"000100","DESCR":"Some notes1"},
{"VAL1":"5023","VAL2":"000200","VAL3":"000200","DESCR":"Some notes2"},
{"VAL1":"0000","VAL2":"000300","VAL3":"000300","DESCR":"Some notes3"}]}}'
,JOIN1_FILTER='$.ORDERS.ROW[*]'
,JOIN1_IncludeParentColumns='False'
,JOIN1_ALIAS='q'

--metadata for final output (include column, type, length for output of SELECT). 
--This meta does not perform type casting
,Meta='[
  {Name: "_rid",Type: "Int32"},
  {Name: "_pid",Type: "Int32"},
 
  {Name: "CUST_ID",Type: "String",Length: 100},
  {Name: "CUST_CITY",Type: "String",Length: 100},
  
  {Name: "VAL1",Type: "String",Length: 100},
  {Name: "VAL2",Type: "Int32"},
  {Name: "VAL3",Type: "String"},
  {Name: "DESCR",Type: "String",Length: 254}
  ]'
  
 --child level1 metadata (length not needed). Datatype used below may perform some type casting ---
,JOIN1_meta='[
  {Name: "VAL1",Type: "String"},
  {Name: "VAL2",Type: "Int32"},
  {Name: "VAL3",Type: "String"},
  {Name: "DESCR",Type: "String"}
  ]'
)

URL JOIN Download files to local disk

This example shows how to download files to local disk. It first gets records by calling first URL and then pass it to seconds URL. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

select a.url,b.data,file_write_binary(base64_to_bytes(b.data) ,'c:\temp\' || name  ) as bytes_written 
from $
WITH ( 
  SRC='https://zappysys.com/downloads/files/test/imagelist.json',
  FILTER='$.list[*]',
  ALIAS='a',
  
  JOIN1_SRC='[$a.url$]',
  JOIN1_ALIAS='b',
  JOIN1_SaveContentAsBinary='true'  
)

URL JOIN Download binary files as Base64 and Upload to different URL

This example shows how to download binary files from one URL and Upload data to another URL as Base64 using URL JOIN techniques. When SaveContentAsBinary Property is supplied it will convert Binary Response to Base64 for easy string operations. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

select a.url,c.* 
from $
WITH ( 
  --Step1: Get List of URLs to download
  SRC='https://zappysys.com/downloads/files/test/imagelist.json',
  FILTER='$.list[*]',
  ALIAS='a',
  
  --Step2: Download files as base64 data for each URL
  JOIN1_SRC='[$a.url$]',  --//SRC can be local or URL c:\image.png or http://some/url/file.pdf
  JOIN1_SaveContentAsBinary='true',  --//This property returns data as base base64 string rather byte array so its easy to pass in next step
  JOIN1_ALIAS='b',
  
  --Step3: Upload File - POST File Base64 data to target URL
  JOIN2_SRC='https://zappysys.com/downloads/files/test/callapi.aspx',
  JOIN2_METHOD='POST',
  JOIN2_BODY='{ name: "[$a.name$]", base64:"[$b.data$]"}',
  JOIN2_ALIAS='c'  
)

URL JOIN Read unstructured data and pass to API call

This example shows how to query unstructured data and parse using Regex, XPath or JSONPath)

Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/
Examples of JsonPath: https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet/
Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/

Also try following Query Examples to extract Raw data.

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
)

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='<title>([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns
,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template
,RawOutputExtractMode='Regex' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
)

SELECT * FROM $
WITH(
Src='http://httpbin.org/get'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='$.origin||$.url' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns
--,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template
,RawOutputExtractMode='Json' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
)

SELECT c.title,o.url,o.data 
FROM $
WITH(
	 Src='http://google.com'
	,EnableRawOutputModeSingleRow='True'
	,RawOutputFilterExpr='<title>([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns
	,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template. Comment this line to generate default columns e.g. col1, col2...If you comment this then use c.col1 and c.col2 instread of c.title and c.content in Select and join1_xxxxx attributes
	,RawOutputExtractMode='Regex' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
    ,Alias='c'
    ,join1_RequestMethod='POST'
    ,join1_RequestData='my data [$c.content$]'
    ,join1_src='https://httpbin.org/post?id=[$c.title$]'
    ,join1_RequestContentTypeCode = 'TextPlain'
    ,join1_alias='o'
    ,join1_EnableRawOutputModeSingleRow='False'
)

URL JOIN Query API with Status Check Loop (Wait until value found)

This example shows how to use status check feature to keep waiting until desired value is not found in response. This pattern is very useful for Job style API where you have to wait until API returns Success or Failure status of Job status. You can adjust iteration wait time, max wait time for timeout and regex pattern to search for Success or Failure. If Success value found it returns data. If Failure value found or timeout occurs it will throw error.

Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/
Examples of JsonPath: https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet/
Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/

SELECT * FROM $ WITH 
(
SRC='https://zappysys.com/downloads/files/test/cust-1.json', 
EnableStatusCheck='True', 
StatucCheckMaxWaitSeconds=7,
StatucCheckIterationWaitSeconds=3,
--StatusCheckForFailedValue = 'True',
--StatusFailedValue = 'Failed|Cancelled',
--StatusFieldFilterExpr='$.response.status', --Use XPath, JsonPath or Regex to narrow down to single property / content under which you like to search 
--StatusFieldFilterType='Json', --or use Xml, Regex, None
StatusSuccessValue='C1' --Wait until C1 or C2 or C3 found in response (Use Regular expression syntax) 
)


--- See real world example of status check pattern here (Use of XML Driver)... https://zappysys.com/blog/import-bing-ads-data-sql-server-performance-reports/ 

SELECT c.* FROM $
WITH(
	--///////////////////////////////////
	--Step-1: submit report request for XML format
	--///////////////////////////////////
	 alias='a' 
	,Src='https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc'
	,Filter='$.s:Envelope.s:Body.SubmitGenerateReportResponse'
	,RequestData='@C:\Requests\BingAds\sql_body1.xml'  --Get Body from File for clean Look (Read blog post above to see actual body)
	,IsMultiPart='True'
	,RequestContentTypeCode='TextXml'
	,Header='SOAPAction: "SubmitGenerateReport"'
	,RequestMethod = 'POST'
	,EnableBodyPlaceholderForMultiPart = 'True'
	,Meta = '[{ "Name": "ReportRequestId","Type": "Int64"}]'

    --///////////////////////////////////
    --///Step-2: keep checking status until report is ready - returns final url when done
    --///////////////////////////////////
	,join1_alias = 'b'
	,join1_Filter = '$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus'
	,join1_RequestData = '@C:\Requests\BingAds\sql_body2.xml'
	,join1_IsMultiPart = 'True'
	,join1_Src = 'https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc'
	,join1_RequestContentTypeCode = 'TextXml'
	,join1_Header = 'SOAPAction: "PollGenerateReport"'
	,join1_RequestMethod = 'POST'
	,join1_EnableBodyPlaceholderForMultiPart = 'True'
	,join1_EnableStatusCheck = 'True', 
	,join1_StatucCheckMaxWaitSeconds = 300, --wait max 5 mins ?
	,join1_StatucCheckIterationWaitSeconds = 5, --check every 5 sec ?
	,join1_StatusSuccessValue = 'Success', --look for success word in response
      --, join1_Meta = '[{ "Name": "ReportDownloadUrl","Type": "String",Length: "500"},{ "Name": "Status","Type": "String",Length: "20"}]'

   --///////////////////////////////////
   --Step-3: Download report(Zip file) and Parse rows
   --///////////////////////////////////
	,join2_alias = 'c'
	,join2_DataConnectionType = 'Default'
	,join2_Src = '[$b.ReportDownloadUrl$]'
	,join2_Filter = '$.Report.Table.Row[*]'
	,join2_ElementsToTreatAsArray = 'Row'
	,join2_RequestMethod = 'GET'
	,join2_IsMultiPart = 'False'
	,join2_FileCompressionType = 'Zip'
)