Apologies, this may not be as detailed as it was before -- this new forum really is terrible, and after I hit enter it emptied all the content from the input box. In any case, I am trying to figure out the best way to extract the data I want from a SharePoint Search Rest query. Because the output's JSON structure is basically a bunch of nested object arrays with the nondescript keys for the properties I selected [i.e. instead of Title being a Key and the value being 'Doc Title', I get Key:Title, Value: Pages where Key is always the property I selected and Value is always the property value which makes it difficult to select content I want effectively through any normal means), I am using xpath.
I am, however, a bit confused as to how I can do the following:
a.) transform the entire array from the Select output to a string versus base64
b.) filter out the junk data that I do not want from the SP rest call
c.) join the dataset together so it creates one array for each result with the following info as Keys: Title, URL, ParentLink
Here is my flow setup.
1.) Send a HTTP req to SharePoint
URI: _api/search/query?queryText='contentclass:STS_List_850 AND Path:mysharepoint.com/*'&selectproperties='Title, Path, ParentLink'&rowlimit=500
The output that it renders is this [this is a truncated version]
"Table": {
"__metadata": { "type": "SP.SimpleDataTable"},
"Rows": {
"results": [
{
"__metadata": {
"type": "SP.SimpleDataRow"
},
"Cells": {
"results": [
{
"__metadata": {
"type": "SP.KeyValue"
},
"Key": "Title",
"Value": "Pages",
"ValueType": "Edm.String"
},
{
"__metadata": {
"type": "SP.KeyValue"
},
"Key": "Path",
"Value": "https://my.sharepoint.com/SiteAssets/Pages",
"ValueType": "Edm.String"
},
{
"__metadata": {
"type": "SP.KeyValue"
},
"Key": "ParentLink",
"Value": "https://my.sharepoint.com/SiteAssets/Forms/AllItems.aspx",
"ValueType": "Edm.String"
},
2. Compose (convert the output above into xml with root
Compose: xml(json(concat('{"root":{"items":', body(output(SendRequest)[d][results][etc][etc],'}}')))
3. Select (create a table of the Keys and Values)
Field: xpath(outputs('Compose'), '//Key'))
Value: xpath(outputs('Compose'), '//Value))
4. For Each. (this is where things get tricky for me. it appears that I have to do a For loop in order to convert everything into string from base64; and more importantly, that I have to do each of my keys - field and value -- one at a time??)
Parameters/output from previous step: outputs('Select')['body'][0][Field Type]
Compose[2]: base64ToString(item()?['$content'])
The following produces what I want, technically. There are no errors. But I feel as if this way is not efficient and could cause significant delay. Additionally, I'm not sure how to properly filter out the junk data nor am I sure how to join 'Field' and 'Value' into an array to make my csv table.
