cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnickell
Level: Powered On

How to access JSON Array items

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: How to access JSON Array items

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:6.JPG

    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:7.JPG

 

8.JPG

The flow works successfully as below:9.JPG

 

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:10.JPGNote: 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:11.JPGThe flow works successfully as below:12.JPG

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Highlighted
Community Support Team
Community Support Team

Re: How to access JSON Array items

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:6.JPG

    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:7.JPG

 

8.JPG

The flow works successfully as below:9.JPG

 

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:10.JPGNote: 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:11.JPGThe flow works successfully as below:12.JPG

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

jnickell
Level: Powered On

Re: How to access JSON Array items

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.

Super User
Super User

Re: How to access JSON Array items

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.<<

Super User
Super User

Re: How to access JSON Array items

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

Super User
Super User

Re: How to access JSON Array items

Hi @ericonline 

 

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

Select certain columns in Flow.jpgSelect 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.

 

Super User
Super User

Re: How to access JSON Array items

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!

Super User
Super User

Re: How to access JSON Array items

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.

Super User
Super User

Re: How to access JSON Array items

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.

Super User
Super User

Re: How to access JSON Array items

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 😉

Super User
Super User

Re: How to access JSON Array items

Hi @DavesTechTips ,

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

image.png

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

":.*

image.png

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

image.png

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

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,720)