WEB API - JSONPath - Returned Multiple Tokens

[AndrewWilson]

Hi All,

I'm wondering if I can get some advice, to maybe point me in the right direction. I'm currently trying to extract a collection, from within a collection, returned from the SharePoint GRAPH API.

To take an example straight from Microsoft, I'm trying to extract "fields" as a complete collection for every item. Please note that in my example I expect different headers under fields, so defining the headers will not work.

{
  "value": [
    {
      "id": "2",
      "fields": {
        "Name": "Gadget",
        "Color": "Red",
        "Quantity": 503
       }
    },
    {
      "id": "4",
      "fields": {
        "Name": "Widget",
        "Color": "Blue",
        "Quantity": 2357
       }
    },
    {
      "id": "7",
      "fields": {
        "Name": "Gizmo",
        "Color": "Green",
        "Quantity": 92
       }
    }
  ]
}​

What my current issue is, I'm pulling around 3900 SharePoint items from a SharePoint List, then looping through the collection, and merging each line to a new collection becomes a very long task.

I've tried a couple different methods, following the Blue Prism 6.4 - Web API User Guide, however I'm unable to get past the "Internal : Unexpected error Path returned multiple tokens."

At the moment my main method has been to set the type to collection, JSONPath, and setting the path to $..fields
or $.value['fields']

I believe this may be because each "Value" has its own "Fields" collection, which means I need some form of a ForEach loop using custom code to extract this information. 

If possible, could somebody point me in the right direction to lookup what's needed to do this, or even better have some previous examples of others with a similar issue?

I've found these examples on community so far:

View Question

Blue Prism Community

However I've been struggling to adapt the code to meet the needs of this work.

Any help appreciated

[AndrewWilson · Accepted solution]

@ewilson Thank you very much for explaining it. That helped massively.

Given I could not use the JSON Pathing I've gone down the custom code route, and put together some work that has thankfully worked. I'm now pulling together around 4000 items in a completed detailed list in less than 30 seconds!

For anybody with a similar issue, wanting to pull SPO List item details from a large SharePoint list, here are a few pointers.

You will need to create an additional API to pull the column headers​. I recommend reading here List columnDefinitions in a list - Microsoft Graph v1.0 | Microsoft Docs and adding this to Blue Prism existing SPO GRAPH API on DX.
Name:
Parameters:
Request:
Headers:
Response:
If you have over 200 columns in SharePoint I recommend not using Json Path $.value and instead just $, and then getting the remaining headers with GRAPHs "NextLink" which is returned on the top level. 

Now that the API was completed and working I could now get the headers. I stored these in a collection, then wrote these out to a CSV (for use later in some code) then added additional fields that are not a header to the csv. (For example the eTag for referencing the items)

This resulted in a long csv with all my headers. 

Now using the "Get Items in List by Fields" Web API Action, return the "Response Content" to a text data item. (This is the JSON we will be using)

I then created a custom code object, added "Response_Content" and "columnHeaders" as the inputs, and "sharePointListItems" as the output. 


Then the secret sauce, the code. (I'll include a copy at the bottom)

What we are doing is loading the response back into a JSON, and then moving up a level to "Value".

We then split the CSV headers we got from SharePoint into an array to loop through. 


Then we create all our headers for the data in SharePoint by looping through all items in the CSV creating a new column for each one.

Now we loop through all the objects from the JSON, taking the fields data (This is where all the SharePoint list data is stored)

Now again, for each field in the csv from earlier, we call the fields value for that column. and store it in the row, before adding the completed row to the table

Then finally we output the table 

Running this code resolved my issue, as I can now pull thousands of records in around 30 seconds. I hope what I found may help some other people out there with a similar issue.

@ewilson Thanks again for your help on understanding Json structure! 

Here is the code for compiling the headers and response together:

// Load Response
JObject responseObject = JObject.Parse(Response_Content);
JArray topLevel = (JArray)responseObject["value"];

//Get Headers CSV and create table
string[] headers = columnHeaders.Split(',');

//Create Headers for dataTable
var dataTable = new DataTable();

foreach (string header in headers)
	{
	dataTable.Columns.Add(header);
	}

//Loops each JSON Object and gets the "Fields" data
foreach (JObject Item in topLevel)
	{
	DataRow row = dataTable.NewRow();  
	JObject fields = (JObject) Item["fields"];


	// Cycles each column header in headers, and polls fields for that information
	foreach (var header in headers)
		{
			row[header] = (string) fields[header]; 
		}

	dataTable.Rows.Add(row);
	}

sharePointListItems = dataTable;