How to access JSON Array items

[jnickell]

I will start by admiting that I'm a novice and out of my league in this area. I'm attempting to wrap my head around using Logic Apps to handle the json output from a form to create a lead in our CRM solution.  For reference of anyone that's interested, I'm coming from FormSite going to Dynamics CRM in the end.

At this point where I'm getting hung up is how to handle the data coming in.

The JSON is coming in in the format below:

{
    "headers": {
        "Cache-Control": "no-cache",
        "Connection": "keep-alive",
        "Pragma": "no-cache",
        "Accept": "text/html,application/json,application/xml",
        "Host": "prod-12.northcentralus.logic.azure.com",
        "User-Agent": "Java/1.8.0_151",
        "Content-Length": "363",
        "Content-Type": "application/json; charset=UTF-8"
    },
    "body": {
        "id": "11665467",
        "items": [
            {
                "values": [
                    {
                        "position": 1,
                        "value": "White"
                    }
                ],
                "id": "0",
                "position": 0
            },
            {
                "id": "1",
                "position": 1,
                "value": "Testing"
            }
        ]
    }

I've found references for handling arrays, but I'm not seeing how to handle an array of the type I'm dealing with, and I could be overlooking, or not be making the connection. Each of the items is not merely another instance of a single item type. Each item in the array is: the question's ID, it's place on the form, and the response value or in the case of a multi-select or drop down the 'values'.  I only care about the ID, and it's value(s).

I'm wanting to get these value fields into the appropriate CRM field. From the research I've been able to do so far, it looks like I might be able to accomplish this through 'Compose', but again, I'm a novice.

I'm really not wanting someone to do the entire solution for me, but I am really struggling at figuring out how LogicApps will allow me to access the specific items, and their values at each field.  

Thanks for your patience and assistance!

[v-xida-msft · Accepted solution]

Hi @jnickell,

Do you want to access specific item within "items" key whose value is an array?

If you want to access the specific item whose question ID is euqal to 0, please take a try with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a "Compose" action, Inputs field set to the JSON data that you mentioned.
• Add a "Compose 2" action, Inputs field set to following formula:

• outputs('Compose')?['items']

  Add a "Compose 3" action, Inputs set to following formula:

  Note: Within Inputs field of "Compose 3", type ID key and the corresponding value set to following formula:

first(outputs('Compose_2'))?['id']

Type values key and the corresponding value set to following formula:

first(outputs('Compose_2'))?['values']

Note: Within the JSON Data that you provided, you may miss a right curly braces (" } ") at the end of json data.

Image reference:

The flow works successfully as below:

In addition, if you want to access the specific item whose question ID is euqal to 1, please take a try with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a "Compose" action, Inputs field set to the JSON data that you mentioned.
• Add a "Compose 2" action, Inputs field set to following formula:

• outputs('Compose')?['items']

  Add a "Compose 3" action, Inputs set to following formula:Note: Within Inputs field of "Compose 3", type ID key and the corresponding value set to following formula:

outputs('Compose_2')?[1]?['id']

Type value key and the corresponding value set to following formula:

outputs('Compose_2')?[1]?['value']

Image reference:The flow works successfully as below:

Best regards,

Kris

[jnickell]

Kris,

  Thanks for you response.  On an initial read through it looks promising.  I'm not able to find the time to actually test right now, but I will mark this as an acceptable answer.

[DavesTechTips]

Hi @v-xida-msft 

As always, thanks for the info.

Just one question. Above you mention "In addition, if you want to access the specific item whose question ID is euqal to 1, please take a try with the following workaround:" and then specify the the following expression

outputs('Compose_2')?[1]?['id']

From my understanding the above expression will get the second item in the list (with the array item 1 specified), an not actually search the items and return the specific item whose question item is "1".

Please let me know if I am missing something, or if there is a way to search the items and return the ones that have specific property values? I have been using xpath for this, but from your post I am thinking that there might be another way for me to accomplish this.

Have a great day.

twitter: @dawidvh

youTube: https://www.youtube.com/channel/UCIMe_ErQZP5chbI9UyW0xLw

>>If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.<<

[ericonline]

Hi @v-xida-msft  and @DavesTechTips , 
How about accessing only certain columns and values within an array and excluding others?

Example json:

{
  "d": {
    "results": [
      {
        "__metadata": {
          "id": "c7e8ca72-9007-40c6-b775-538affd3d806",
          "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)",
          "etag": "\"6\"",
          "type": "SP.Data.TestProjectDataListItem"
        },
        "FirstUniqueAncestorSecurableObject": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FirstUniqueAncestorSecurableObject"
          }
        },
        "RoleAssignments": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/RoleAssignments"
          }
        },
        "AttachmentFiles": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/AttachmentFiles"
          }
        },
        "ContentType": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/ContentType"
          }
        },
        "GetDlpPolicyTip": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/GetDlpPolicyTip"
          }
        },
        "FieldValuesAsHtml": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FieldValuesAsHtml"
          }
        },
        "FieldValuesAsText": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FieldValuesAsText"
          }
        },
        "FieldValuesForEdit": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FieldValuesForEdit"
          }
        },
        "File": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/File"
          }
        },
        "Folder": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/Folder"
          }
        },
        "LikedByInformation": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/LikedByInformation"
          }
        },
        "ParentList": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test//_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/ParentList"
          }
        },
        "Properties": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/Properties"
          }
        },
        "Versions": {
          "__deferred": {
            "uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/Versions"
          }
        },
        "FileSystemObjectType": 0,
        "Id": 35514,
        "ServerRedirectedEmbedUri": null,
        "ServerRedirectedEmbedUrl": "",
        "ContentTypeId": "0x0100F454567E17A56C498EC3619A0BB9A73500D527A9BA30B7AC4C8FBB138AF4F017BC",
        "Title": null,
        "Column1": "235514",
        "Column2": "testThing",
        "Column3": "2020-01-01T06:00:00Z",
        "ColumnN": "There are a LOT of columns to get past this one too!"
      }
    ],
    "__next": "https://ourcompany.sharepoint.com/sites/test/_api/web/lists/GetByTitle('Master%20Test%20Data')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d35514&%24top=1&%24orderby=ID+desc"
  }
}

I only want "Column1" - "ColumnN", none of the other stuff in the array. 
Thank you

[DavesTechTips]

Hi @ericonline 

The easiest would be to use the Select Action which will allow you to map certain columns (keys) and values.

Select certain JSON columns in Flow

Please let me know if you don't come rigth.

Have a great day.

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

[ericonline]

Hi @DavesTechTips , 

Thanks for the reply. The Select action would work IF I didn't have multiple lists to query. I'm trying to build a job that will pull data from 12 different Sharepoint lists. I think I'd have to have 12 different Selects and 12 different Parse JSON's to accomplish. 

Also, one of the lists is 180 columns wide!

Back to the drawing board for me!

[DavesTechTips]

Hi @ericonline 

Ouch, that sounds like a Flow that woudl require a nice big screen 😉

In the SharePoint Get Items action, you can limit the columns that will be returned from the SharePoint List by referencing a view. You could then possibly use Odata filters to only get the necessary data.

I am sure you know how to do this, but below is a quick video that demos this.

https://youtu.be/m_j_xN7-LjU

Let me know if you don't come right and if you could possible send me more info to look at to see if I can think of anything else.

Have a great day!

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

[ericonline]

Just to follow up here...

I ended up:

1. IN A TEST FLOW:
   1. Making an HTTP call for 1 record
2. Copying the response schema to VS Code
3. Paring the schema down to only the required columns ("Col1 - ColN" above)
4. IN ACTUAL FLOW:
   1. Paste in the altered schema
   2. Back in VS Code use some regex's to transform the schema to a comma separated list of required columns
   3. Pasted these into the $select area of the HTTP request

That pulled back a nice clean response without all the cruft.

[DavesTechTips]

Hi @ericonline 

Thank you for this. i have fought with regex quite a bit but there seems to be caveats on various chars and data types. What regex did you use, or did you use a tool to create regex.

I can't help to feel that there is nothing regular about regex 😉

[ericonline]

Hi @DavesTechTips ,

In VS Code, CTRL+F to bring up the Find menu. In there, click the RegEx button:

I used Find with 8 spaces and " to peel off the front, but the real RegEx used was:

":.*

Which searches for ":, then using .* peels off everything to the end-of-line. Replace with " , " results in:

Probably wasn't the SuperHero RegEx you were looking for, but there it is!!

[skesharwani]

Thanks this helped me parsing through my JSON output 🙂

[Ricardo111]

Nice solution, I can confirm this worked nicely. Thank you.