Select nested array value in JSON

[ericonline]

Jeez! Why is this so tough to get! 🙂

Need the 'results' array nested here:

{
  "d": {
    "results": [
      {
        "__metadata": {
          "id": "240b3a48-d569-4ba1-8bfe-698922ff05f1",
          "uri": "https://company.sharepoint.com/sites/test/_api/Web/Lists(guid'80cb32af-1111-47fd-9262-e2c47a82b400')/Items(10913)",
          "etag": "\"12\"",
          "type": "SP.Data.TestListItem"
        },
        "Id": 10913,
        "ID": 10913
      }
    ],
    "__next": "https://company.sharepoint.com/sites/test/_api/web/lists/GetByTitle('Test%20Users')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d10913&%24select=ID&%24top=1&%24orderby=ID+asc"
  }
}

Specifically, just the "ID" value.

[ericonline]

I feel like one of these should work, but they don't:

Output is blank for these

• outputs('Parse_JSON_2')?['results']
• outputs('Parse_JSON_2')?['d']?['results']
• outputs('Parse_JSON_2')?['d']?['results'][1]
• body('Parse_JSON_2')?['results']

Entire json payload is returned from this

• body('Parse_JSON_2')?['d']?['results']

Error for this: 'body('Parse_JSON_2')?['d']?['results'][1]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

• body('Parse_JSON_2')?['d']?['results'][1]

[RezaDorrani · Accepted solution]

@ericonline 

body('Parse_JSON_2')?['d']?['results']?[0]?['ID']

U were very close 🙂

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[RezaDorrani]

@ericonline 

Arrays begin with index 0 always

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ericonline]

You're a good man @RezaDorrani ! Thank you. 

Do you know why...

• body('Get_Sharepoint_List_Using_HTTP') works in a Create File (content) action but
• body('Get_Sharepoint_List_Using_HTTP')?['d']?['results']?[0]?['Title']?['column1']?['column2']?['column3']?['column4']?['column5']?['etc'] does not work?

Column1-etc are all in the 'results' array (I left them out in the original post).

[RezaDorrani]

Hi @ericonline 

body('Get_Sharepoint_List_Using_HTTP')?['d']?['results']?[0]?['Title']

body('Get_Sharepoint_List_Using_HTTP')?['d']?['results']?[0]?['internalcolumnname'] - format will give you the data for any column in the first item of the results array

Can you elaborate your question futher with screenshots please

[ericonline]

Scenario:

• Send HTTP Request to Sharepoint
• Parse JSON
• Create file

I'd like to skip the Parse JSON step by using the expression body('Send_HTTP_Request_to_Sharepoint')?['d']?['results']?[0]?['Column1']?['Column2']?['ColumnN'] directly in the File Content area of Create File action.

Unfortunately, the file is always blank when I attempt this. I have to use outputs('Parse_JSON') to create the file.

[RezaDorrani]

@ericonline 

Screenshot please of the JSON output from flow

[ericonline]

Here is the schema from Get full list:

{
    "statusCode":200,
    "headers":{
        "Transfer-Encoding":"chunked",
        "Vary":"Accept-Encoding",
        "X-SharePointHealthScore":"0"
    },
    "body":{
        "d":{
            "results":[
                {
                    "__metadata":{
                        "id":"1111-236a-1111-9aeb-dc020c8ee4b6",
                        "uri":"https://mycompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1111-c27a-4de7-1111-3b743548dd11')/Items(1884)",
                        "etag":"\"17\"",
                        "type":"SP.Data.test"
                    },
                        "Column1":1884,
                        "Column2":null,
                        "Column3":null
                }
            ]
        }
    }
}

I'd like to skip the Parse JSON step...

But when I try the body() expression shown above, the file is always empty.

[RezaDorrani]

Hi @ericonline 

body('Get_Full_List')?['body']?['d']?['results']?[0]?['Column1']

body('Get_Full_List')?['body']?['d']?['results']?[0]?['Column2']

body('Get_Full_List')?['body']?['d']?['results']?[0]?['Column3']

if u need the uri then

body('Get_Full_List')?['body']?['d']?['results']?[0]?['__metadata']?['uri']

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly