Helpful Answers
@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:
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;
Thanks for your response!
I've just tried this. Sadly still getting
"Internal : Unexpected error Path returned multiple tokens."
No problem, I've already tested this far, and yes thankfully this works.
We're getting all of the fields collections through, no problem. and these fields have the data within them.
could you explain it a little more for me, just so it sinks in. Why does the Blue Prism example in "Blue Prism 6.4 - Web API User Guide" work, when calling $.staff-database['employee'] for returning all employee values, if the column headers need to be the same? (id 1245 does not have a job-title attribute)
As explained here:
I understood the issue I was having is that when calling "value" there are multiple "fields" for each id returned, which is what was giving us the "Multiple tokens" error
How familiar are you with JSON? The first thing to understand is the example from the Web API guide is actually invalid JSON. :face_with_tears_of_joy: There should be commas at the end of each line highlighted in read and no comma at the end of the line highlighted in blue. The blue one isn't a deal breaker, but based on the spec it's invalid.
The next thing to understand, and a key difference between the two examples, is that in the Web API example the employee element is a JSON array. So a JSON array within a JSON object (i.e. staff-database) whereas in the SharePoint example the fields element is a JSON object within a JSON array (i.e. value). This makes a difference in how you access the child elements.
Consider the following JSON Path:
$.staff-database["employee"]
NOTE: The JSON Path expression above returns the same output at $.staff-database
That JSON Path returns the following JSON:
This is a JSON array containing a JSON array of JSON objects. You can identify a JSON array by looking for [ ] (square brackets). A JSON object is indicated by { } (curly brackets).
If you use the following JSON Path on your original SharePoint example:
$.value
You'll get the following output:
This is a JSON array of JSON objects. So we have a slightly different JSON definition: JSON Array, JSON Array, JSON Objects vs JSON Array, JSON Objects
If you wanted to get a similar a result from the Web API example JSON, you would need to use a JSON Path expression like this:
$.staff-database["employee"]
This would give you the following output:
If Blue Prism is parsing a JSON array of objects it looks at the very first object in the array (assumption that arrays always contain items that are same type/definition) and uses that as the blue print of the DataTable it creates in memory to contain a row for each item in the array. It performs this interpretation exactly 1 time. If Blue Prism is parsing an array of objects within another array, it's interpreting the JSON object definition every time it sees a new row in the contained array. In that case, the blue print of the DataTable can actually change from one row to the next.
Does that help?
Cheers,
Eric
