Using a nested REST API in combination with paging

Hi Jacqueline

We had something regarding this @Jan Stroeve may know about it.

In essence this is how they did it. They added the two rows along with the pagination option.

  <api:set attr="EnablePaging" value="true" />
  <api:set attr="pagetokenpath" value="/json/pagination/cursor" />
  <api:set attr="pagetokenparam" value="cursor" />

Do you have the field with the token as a field in the response, or is it in a header value?

Hi Jacqueline

That was also the issue for Jan.
What CData suggested was this.

Create two individual RSD files one that is doing the nesting call like this:

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="detailedInfo" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1" other:queryslicercolumn="nested_id">
    <attr name="ID"        xs:type="string"   readonly="false"                            other:xPath="/json/data/name"      />
    <!-- You can modify the name, type, and column size here. -->
    <attr name="nested_id" xs:type="string"   readonly="false" other:filter="{nested_id}" other:xPath="/json/data/nested_id" />
    <attr name="Name"      xs:type="string"   readonly="false"                            other:xPath="/json/data/name"      />
    <attr name="Date"      xs:type="datetime" readonly="false"                            other:xPath="/json/data/date"      />
    <attr name="Number"    xs:type="decimal"  readonly="false"                            other:xPath="/json/data/number"    />
  </api:info>

  <api:set attr="DataModel" value="DOCUMENT" />
  <api:set attr="URI" value="https://api.rest.com/v2/detailedInfo?nested={nested_id}" />
  <api:match pattern="*nested_id*" type="glob" value="[_query.criteria]">
    <api:else>
      <api:set attr="URI" value="https://api.rest.com/v2/detailedInfo?nested=001" />
    </api:else>
  </api:match>
  <api:set attr="JSONPath" value="$.data" />

  <api:set attr="EnablePaging"   value="TRUE"                           />
  <api:set attr="pagetokenpath"  value="/EnumerationResults/NextMarker" />
  <api:set attr="pagetokenparam" value="marker"                         />

  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:call op="jsonproviderGet">
      <api:push/>
    </api:call>
  </api:script>

</api:script>

And one that has a list of the nested values could look like this.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="values" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <attr name="id"     xs:type="string" readonly="false" other:xPath="/json/data/id"     />
    <attr name="name"   xs:type="string" readonly="false" other:xPath="/json/data/name"   />
    <attr name="status" xs:type="string" readonly="false" other:xPath="/json/data/status" />
  </api:info>

  <api:set attr="DataModel" value="DOCUMENT" />
  <api:set attr="URI" value="https://api.rest.com/v2/values" />
  <api:set attr="JSONPath" value="$.data" />

  <api:set attr="EnablePaging"   value="TRUE"                           />
  <api:set attr="pagetokenpath"  value="/EnumerationResults/NextMarker" />
  <api:set attr="pagetokenparam" value="marker"                         />

  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:call op="jsonproviderGet">
      <api:push/>
    </api:call>
  </api:script>

</api:script>

Then you have a REST data source that looks at a folder where these two RSD files are located and you create a Query table to do the nesting with. Like so.

SELECT ID, Nested_Id, Name, Date, Number FROM REST.detailedInfo
WHERE Nested_Id IN (SELECT ID FROM REST.values)

This should make it work despite having to do a nested call alongside this method.

Notice that I use a Query Slicer column and a nested field to make it able to be used for this.

Well they also provided some message about that.

If the customer absolutely needs the logic in one RSD file (i.e. so they can run a simple unfiltered SELECT *), then that will require manual logic using rows@next. In that case, please let me know and I can try to customize the RSD file accordingly.

So I think that may be more difficult actually.

Let me know if you get stuck.

Hi Jacqueline

We should maybe do a session where you show me how far you are and what issues you get. I may have some ideas and if not I can gather as much necessary data to ask for help with this.

When would it fit you?

I am available most days from 09-15 UTC +01:00

Hi Jacqueline

I have sent an invite.

Hi, I've had a similar use case when calling a nested API call in a different ETL-tool. The following rsd script first reads Azure AD groups via the Graph API and then retrieves the group members via a nested Graph API call. Hopefully this example will help you.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <api:info title="Members" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here.  -->
    <attr name="GroupId"           xs:type="string"  readonly="false" other:xPath="groupId"            />
    <attr name="GroupDisplayName"  xs:type="string"  readonly="false" other:xPath="groupDisplayName"   />
    <attr name="GroupDescription"  xs:type="string"  readonly="false" other:xPath="groupDescription"   />
    <attr name="UserId"            xs:type="string"  readonly="false" other:xPath="userId"             />
    <attr name="UserDisplayName"   xs:type="string"  readonly="false" other:xPath="userDisplayName"    />
    <attr name="UserPrincipalName" xs:type="string"  readonly="false" other:xPath="userPrincipalName"  />
    <!-- Attributes are used for passing to child API call -->
    <attr name="Id"                xs:type="string"  readonly="false" other:xPath="id"                 />
    <attr name="DisplayName"       xs:type="string"  readonly="false" other:xPath="displayName"        />
    <attr name="Description"       xs:type="string"  readonly="false" other:xPath="description"        />
  </api:info>

  <api:set attr="groupin.DataModel" value="DOCUMENT" />
  <api:set attr="groupin.JSONPath" value="/value" />
  <api:set attr="groupin.URITemplate" value="https://graph.microsoft.com/v1.0/groups?$select=id,displayName,description" />
  <api:set attr="groupin.ElementMapPath#" value="/value/id" />
  <api:set attr="groupin.ElementMapName#" value="group_id" />
  <api:set attr="groupin.EnablePaging" value="TRUE" />
  <api:set attr="memberin.DataModel" value="DOCUMENT" />
  <api:set attr="memberin.EnablePaging" value="TRUE" />
  <api:set attr="memberin.JSONPath" value="/value" />
  <api:set attr="memberin.URITemplate" value="https://graph.microsoft.com/v1.0/groups/{groupId}/members?$select=id,displayName,userPrincipalName" />

  <api:script method="GET">
    <api:set attr="groupin.URI" value="[groupin.URITemplate]" />
    <!-- Get the groups -->
    <api:call op="jsonproviderGet" in="groupin" out="groupout">
      <api:set attr="memberin.groupId" value="[groupout.id]" />
      <api:set attr="memberin.groupDisplayName" value="[groupout.displayName | allownull()]" />
      <api:set attr="memberin.groupDescription" value="[groupout.description | allownull()]" />
      <api:set attr="memberin.URI" value="[memberin.URITemplate | replace('{groupId}', [memberin.groupId])]" />
      <!-- Get the members of the group -->
      <api:call op="jsonproviderGet" in="memberin" out="memberout">
        <api:set attr="out.userId" value="[memberout.id]" />
        <api:set attr="out.userDisplayName" value="[memberout.displayName]" />
        <api:set attr="out.userPrincipalName" value="[memberout.userPrincipalName]" />
        <api:set attr="out.groupId" value="[memberin.groupId | allownull()]" />
        <api:set attr="out.groupDisplayName" value="[memberin.groupDisplayName | allownull()]" />
        <api:set attr="out.groupDescription" value="[memberin.groupDescription | allownull()]" />
        <api:push item="out"/>
      </api:call>
    </api:call>
  </api:script>
</api:script>

Hi @gijs,

Thanks for your reaction but unfortunately that does not solve my problem. 

I already got some connections working in the way you described, but the challenge for me lies in the combination of:

• nested calls
• paging
• incremental loading.

The option Thomas provided in using a query slicer does work for using nested call in combination with paging (after a bit of customizing) but not for the incremental loading at top level. And I need this for performance issues.

Hi @jacqueline.hofmeijer do you have an update for Thomas’ questions above? Thanks!