How to Loop through multiple pages of results when querying a REST WebAPI using apoc.load.json Params

Neo4j Graph Platform Procedures & APOC
1

Often times if you query a JSON WebAPI, if it has too much data it will break the return into multiple pages, and provide the URL for the next page as one of the returned values..

For example, the returned json will have data within data [0...100]
There will also be a top-level "links" value that will have properties: links.next, links.last, and links.first. If the links.next is not null, then there are additional pages of data for this query, and the links.next will be string representing a URL that has dynamically included some page number details in the query language.

How could I build a cypher query that will perform something like: "WHILE the returnvalue.links.next contains something, then perform this query again, using the value as the url"?

It looks like apoc.periodic.rock_n_roll_while may be the ticket, but does someone have an example using this?

2

i dont have an example with apoc.periodic.rock_n_roll_while, but do something similar for example when querying the Zendesk API which returns multiple pages of output and you have to continue to get each page. See Zendesk to Neo4j Integration. Better control over you reporting needs | by Dana Canzano | Neo4j Developer Blog | Medium and specifically the usage of

 WITH next_page,
 CASE WHEN next_page is null then 0 ELSE 1 END AS count
 FOREACH(_ in CASE WHEN count = 0 THEN [] ELSE [1] END |
     MERGE (import:Import {id:1})
        SET import.page = next_page
3

I think that looks like exactly what I am looking for. I'll give it a shot. Thanks!

4

Ok - you got me on the right track.

My use case had one more extra complication - that is I was performing this multi-tenant. UNWIND wasn't quite behaving as I needed, so I aggregated multiple tenants into a property array on the helper node: (import:Auvikimport) .urllist
Then if the "nextpage" value was blank, I checked to see if there was anything left in the .urllist and set that to the 'nextpage', then removed the value from the .urllist property.

Thanks for the great assist Dana! Here's my cypher code for reference:

// SECTION set each tenant to latest update
MATCH (at:Auviktenant)--(ad:Auvikdevice)
WITH at,max(ad.modified) as latestmod where latestmod >=at.lastupdate or not exists(at.lastupdate)
SET at.lastupdate=latestmod;

// SECTION SET URL property for each :Auviktenant to query devices
WITH "2019-01-01T23:00:00.000Z" as lastupdate
MATCH (at:Auviktenant)
MERGE (import:Auvikimport {id: 1}) SET import.urllist=[] REMOVE import.url
WITH import,at,lastupdate
unwind at.id as tenantid
WITH import,at,"https://auvikapi.us1.my.auvik.com/v1/inventory/device/info?tenants="+tenantid+"&filter[modifiedAfter]="+coalesce(at.lastupdate,lastupdate)+"&include=deviceDetail" as url
SET at.url=url
FOREACH (ignoreMe in CASE WHEN not at.url in coalesce(import.urllist,[]) then [1] ELSE [] END | SET import.urllist=coalesce(import.urllist,[]) + at.url)
RETURN at.url,import;

// SECTION iterate through the entire tenant device URL list and create :Auvikdevice nodes
WITH *,'Mysupersecrettoken' as token
WITH *,'{Authorization:"Basic '+token+'",Accept:"application/json"}' as theparams
CALL apoc.periodic.commit("MATCH (import:Auvikimport {id:1})
WITH import,head(import.urllist) as nexturl
WITH import,coalesce(import.url,nexturl) as theurl
SET import.url=''
WITH import,theurl where trim(theurl)<>''
CALL apoc.load.jsonParams(theurl,"+theparams+",null) YIELD value AS value
WITH import,value
UNWIND value.data as device
MERGE (ad:Auvikdevice {id:device.id}) ON CREATE
SET ad.type=device.attributes.deviceType,ad.model=device.attributes.makeModel,ad.serialnumber=device.attributes.serialNumber,ad.description=device.attributes.description,ad.modified=device.attributes.lastModified,ad.vendor=device.attributes.vendorName,ad.devicename=device.attributes.deviceName,ad.swversion=device.attributes.softwareVersion,ad.fwversion=device.attributes.firmwareVersion
WITH import,value,device,ad
UNWIND device.attributes.ipAddresses as deviceip
FOREACH (ignoreMe in CASE WHEN not deviceip in coalesce(ad.ipaddress,[]) then [1] ELSE [] END | SET ad.ipaddress=coalesce(ad.ipaddress,[]) + deviceip)
WITH import,value,ad,device LIMIT {limit}
UNWIND device.relationships as relationship
OPTIONAL MATCH (an:Auviknetwork {id:relationship.networks.data[0].id})
OPTIONAL MATCH (at:Auviktenant {id:relationship.tenant.data.id})
FOREACH (ignoreMe in CASE WHEN exists(an.id) then [1] ELSE [] END | MERGE (ad)-[:WITHIN_NETWORK]->(an))
FOREACH (ignoreMe in CASE WHEN exists(at.id) then [1] ELSE [] END | MERGE (ad)-[:WITHIN_TENANT]->(at))
WITH import,value,value.links.next as nextpage,head(import.urllist) as nexturl
WITH import,nexturl,nextpage,CASE WHEN (nextpage is null or nextpage='' and (nexturl is null or trim(nexturl)='')) and (size(import.urllist) is null or size(import.urllist)=0) then 0 ELSE 1 END AS count
FOREACH (_ in CASE WHEN count = 0 THEN [] ELSE [1] END | SET import.url = nextpage)
FOREACH (ignoreMe in CASE WHEN (trim(nextpage)='' or nextpage is null) THEN [1] ELSE [] END | SET import.url=nexturl SET import.urllist = FILTER(x in import.urllist WHERE x <> nexturl) )
RETURN count"
,
{limit:1000}) YIELD batches,batchErrors,failedBatches,failedCommits,commitErrors,executions,runtime,wasTerminated
RETURN *
5

@pdrangeid happy to see you've made progress

and thanks for the 'thanks' really do appreciate it