cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gmaasz
Helper IV
Helper IV

Converting JSON object to array

I am using a flow to check the changes within a sharepoint list by looking at the version history of the field changing. I seem to get the HTTP request correct but when trying to use the output it is in the form of an object, not an array. Any ideas on generating the output of the HTTP request as an array? The speific fields I want to use and retrieve from the versions are the display names of a person field.

flowimage1.JPG

Error:

flowimage2.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

The formula you are looking for should be

body('Parse_JSON')?['value'][0]?['Fail_x005f_x002d_x005f_Accept_x005f_x0020_x005f_Signoff']?['LookupValue']

and

body('Parse_JSON')?['value'][1]?['Fail_x005f_x002d_x005f_Accept_x005f_x0020_x005f_Signoff']?['LookupValue']


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

9 REPLIES 9
Pstork1
Dual Super User III
Dual Super User III

Can you show the return value you are getting back from the request?  I suspect it is coming in the form of JSON.  If it is you can use a Parse JSON action to break the JSON down into dynamic content that you can use in other actions.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@Pstork1  Can you please send me the JSON required to parse this in the parse function.flowimage3.JPG

Pstork1
Dual Super User III
Dual Super User III

Just copy what you see in the Body portion of the response in your screenshot below.  That would be the sample payload that you use in the Parse JSON action to generate the schema.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

All this works great, now I only receive the last version within my select function... The output of the parse contains both versions, but the body of my select function contains only the second.

flowimage4.JPG

Pstork1
Dual Super User III
Dual Super User III

The output of the Parse is an indexed array, but Select is designed to Select based on a Key value.  you can access the values in the array directly using JSON notation in a compose statement.  If you will send a sample copy of your output used in the Parse I can explain what the JSON notation would look like.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.


@Pstork1 wrote:

The output of the Parse is an indexed array, but Select is designed to Select based on a Key value.  you can access the values in the array directly using JSON notation in a compose statement.  If you will send a sample copy of your output used in the Parse I can explain what the JSON notation would look like.



{
"value": [
    {
      "Created": "2019-10-09T10:56:08",
      "IsCurrentVersion": true,
      "VersionId": 1024,
      "VersionLabel": "2.0",
      "ContentTypeId": {
        "StringValue": "0x0100DDC73B6EA313734EBF300A42771F906A"
      },
      "Title": "testdrnr",
      "OData__x005f_ModerationComments": null,
      "File_x005f_x0020_x005f_Type": null,
      "ComplianceAssetId": null,
      "PO_x005f_x0020_x005f__x005f_x002f_x005f__x005f_x0020_x005f_Job_x005f_x0020": null,
      "Item_x005f_x0020_x005f_Number": null,
      "Entity": null,
      "Supplier": null,
      "Quantity": null,
      "Type_x005f_x0020_x005f_of_x005f_x0020_x005f_Attachment": [
        "Dimensional checklist"
      ],
      "Additional_x005f_x0020_x005f_Reference_x005f_x0020": null,
      "AX_x005f_x0020_x005f_Quality_x005f_x0020_x005f_Order_x005f_x00": null,
      "Comments": null,
      "Serial_x005f_x0020_x005f_Number": null,
      "Status": "Pass",
      "Country_x005f_x0020_x005f_of_x005f_x0020_x005f_Origin": null,
      "NCR_x005f_x0020_x005f_Number": null,
      "Fail_x005f_x002d_x005f_Accept_x005f_x0020_x005f_Signoff": {
        "LookupId": 681,
        "LookupValue": "Testperson1",
        "Email": "test@testemail.com"
      },
      "ID": 15,
      "Modified": "2019-10-09T10:56:08Z",
      "Author": {
        "LookupId": 284,
        "LookupValue": "Testperson2",
        "Email": "test2@testemail.com"
      },
      "Editor": {
        "LookupId": 284,
        "LookupValue": "TestPerson2",
        "Email": "gmaasz@masterdrilling.com"
      },
      "OData__x005f_HasCopyDestinations": null,
      "OData__x005f_CopySource": null,
      "owshiddenversion": 2,
      "WorkflowVersion": 1,
      "OData__x005f_UIVersion": 1024,
      "OData__x005f_UIVersionString": "2.0",
      "Attachments": false,
      "OData__x005f_ModerationStatus": 0,
      "InstanceID": null,
      "Order": 1500,
      "GUID": "77777224-7fee-4e23-b544-9d5546038032",
      "WorkflowInstanceID": null,
      "FileRef": "/sites/recordcenter/Lists/QC 009/15_.000",
      "FileDirRef": "/sites/recordcenter/Lists/QC 009",
      "Last_x005f_x0020_x005f_Modified": "2019-10-09T10:38:06Z",
      "Created_x005f_x0020_x005f_Date": "2019-10-09T10:38:06Z",
      "FSObjType": "0",
      "SortBehavior": {
        "LookupId": 15,
        "LookupValue": "0"
      },
      "FileLeafRef": "15_.000",
      "UniqueId": "c0d9a928-c81b-401a-ba34-aaffa7bc7842",
      "SyncClientId": {
        "LookupId": 15,
        "LookupValue": null
      },
      "ProgId": "",
      "ScopeId": "{4E363355-FBD6-4082-8C9B-85B18258D602}",
      "MetaInfo": "",
      "OData__x005f_Level": 1,
      "OData__x005f_IsCurrentVersion": true,
      "ItemChildCount": "0",
      "FolderChildCount": "0",
      "Restricted": "",
      "OriginatorId": "",
      "NoExecute": "0",
      "ContentVersion": "0",
      "OData__x005f_ComplianceFlags": "",
      "OData__x005f_ComplianceTag": "",
      "OData__x005f_ComplianceTagWrittenTime": "",
      "OData__x005f_ComplianceTagUserId": "",
      "AccessPolicy": "",
      "OData__x005f_VirusStatus": "",
      "OData__x005f_VirusVendorID": "",
      "OData__x005f_VirusInfo": "",
      "AppAuthor": null,
      "AppEditor": null,
      "SMTotalSize": {
        "LookupId": 348,
        "LookupValue": ""
      },
      "SMLastModifiedDate": "2019-10-09T10:56:08Z",
      "SMTotalFileStreamSize": "0",
      "SMTotalFileCount": {
        "LookupId": 0,
        "LookupValue": ""
      }
    },
    {
      "Created": "2019-10-09T10:38:06",
      "IsCurrentVersion": false,
      "VersionId": 512,
      "VersionLabel": "1.0",
      "ContentTypeId": {
        "StringValue": "0x0100DDC73B6EA313734EBF300A42771F906A"
      },
      "Title": "testdrnr",
      "OData__x005f_ModerationComments": null,
      "File_x005f_x0020_x005f_Type": null,
      "ComplianceAssetId": null,
      "PO_x005f_x0020_x005f__x005f_x002f_x005f__x005f_x0020_x005f_Job_x005f_x0020": null,
      "Item_x005f_x0020_x005f_Number": null,
      "Entity": null,
      "Supplier": null,
      "Quantity": null,
      "Type_x005f_x0020_x005f_of_x005f_x0020_x005f_Attachment": [
        "Dimensional checklist"
      ],
      "Additional_x005f_x0020_x005f_Reference_x005f_x0020": null,
      "AX_x005f_x0020_x005f_Quality_x005f_x0020_x005f_Order_x005f_x00": null,
      "Comments": null,
      "Serial_x005f_x0020_x005f_Number": null,
      "Status": "Pass",
      "Country_x005f_x0020_x005f_of_x005f_x0020_x005f_Origin": null,
      "NCR_x005f_x0020_x005f_Number": null,
      "Fail_x005f_x002d_x005f_Accept_x005f_x0020_x005f_Signoff": {
        "LookupId": 284,
        "LookupValue": "TestPerson2",
        "Email": "test2@testemail.com"
      },
      "ID": 15,
      "Modified": "2019-10-09T10:38:06Z",
      "Author": {
        "LookupId": 284,
        "LookupValue": "TestPerson2",
        "Email": "test2@testemail.com"
      },
      "Editor": {
        "LookupId": 284,
        "LookupValue": "TestPerson2",
        "Email": "test2@testemail.com"
      },
      "OData__x005f_HasCopyDestinations": null,
      "OData__x005f_CopySource": null,
      "owshiddenversion": 1,
      "WorkflowVersion": 1,
      "OData__x005f_UIVersion": 512,
      "OData__x005f_UIVersionString": "1.0",
      "Attachments": false,
      "OData__x005f_ModerationStatus": 0,
      "InstanceID": null,
      "Order": 1500,
      "GUID": "77777224-7fee-4e23-b544-9d5546038032",
      "WorkflowInstanceID": null,
      "FileRef": "/sites/recordcenter/Lists/QC 009/15_.000",
      "FileDirRef": "/sites/recordcenter/Lists/QC 009",
      "Last_x005f_x0020_x005f_Modified": "2019-10-09T10:38:06Z",
      "Created_x005f_x0020_x005f_Date": "2019-10-09T10:38:06Z",
      "FSObjType": "0",
      "SortBehavior": {
        "LookupId": 15,
        "LookupValue": "0"
      },
      "FileLeafRef": "15_.000",
      "UniqueId": "c0d9a928-c81b-401a-ba34-aaffa7bc7842",
      "SyncClientId": {
        "LookupId": 15,
        "LookupValue": null
      },
      "ProgId": "",
      "ScopeId": "{4E363355-FBD6-4082-8C9B-85B18258D602}",
      "MetaInfo": "",
      "OData__x005f_Level": 1,
      "OData__x005f_IsCurrentVersion": false,
      "ItemChildCount": "0",
      "FolderChildCount": "0",
      "Restricted": "",
      "OriginatorId": "",
      "NoExecute": "0",
      "ContentVersion": "0",
      "OData__x005f_ComplianceFlags": "",
      "OData__x005f_ComplianceTag": "",
      "OData__x005f_ComplianceTagWrittenTime": "",
      "OData__x005f_ComplianceTagUserId": "",
      "AccessPolicy": "",
      "OData__x005f_VirusStatus": "",
      "OData__x005f_VirusVendorID": "",
      "OData__x005f_VirusInfo": "",
      "AppAuthor": null,
      "AppEditor": null,
      "SMTotalSize": {
        "LookupId": 348,
        "LookupValue": ""
      },
      "SMLastModifiedDate": "2019-10-09T10:56:08Z",
      "SMTotalFileStreamSize": "0",
      "SMTotalFileCount": {
        "LookupId": 0,
        "LookupValue": ""
      }
    }
  ]
}
Pstork1
Dual Super User III
Dual Super User III

The formula used will depend a bit on which value you want to retrieve.  But here's an example from your data. If I want to retrieve the email address of the Editor person object for the latest version I would use the following

body('Parse_JSON')?['value'][0]?['Editor']?['Email']

But if I want the one from the oldest version (since they are in order from newest to oldest in the array) I would use this

body('Parse_JSON')?['value'][1]?['Editor']?['Email']

Note the only difference is the ) based array index I've included after value.

 

Here is what its made up of.

Body() is the body of your Flow

'Parse_JSON' is the name of the Action that parses the JSON

'value' is the name of the array

0 or 1 is the index of the item in the array

'Editor' is the name of the Field in the array

'Email' is the name of the Field inside the Editor object.

The ?'s between the fields mean if the field doesn't exist don't throw an error return a null

 

Hope that explains it and helps.  If you need more just let me know which person object you are trying to retrieve.  You enter these codes in the dyanmic content expression box.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@Pstork1 



I am trying to get the displayname of the 'Fail Accept Sign-Off Owner' for the last 2 versions to compare them. Still struggling to get it though. Can you please assist with the specific formula which I should enter into the compose field. I am struggling with this coding stuff 🙂


@Pstork1 wrote:

The formula used will depend a bit on which value you want to retrieve.  But here's an example from your data. If I want to retrieve the email address of the Editor person object for the latest version I would use the following

body('Parse_JSON')?['value'][0]?['Editor']?['Email']

But if I want the one from the oldest version (since they are in order from newest to oldest in the array) I would use this

body('Parse_JSON')?['value'][1]?['Editor']?['Email']

Note the only difference is the ) based array index I've included after value.

 

Here is what its made up of.

Body() is the body of your Flow

'Parse_JSON' is the name of the Action that parses the JSON

'value' is the name of the array

0 or 1 is the index of the item in the array

'Editor' is the name of the Field in the array

'Email' is the name of the Field inside the Editor object.

The ?'s between the fields mean if the field doesn't exist don't throw an error return a null

 

Hope that explains it and helps.  If you need more just let me know which person object you are trying to retrieve.  You enter these codes in the dyanmic content expression box.


 

Pstork1
Dual Super User III
Dual Super User III

The formula you are looking for should be

body('Parse_JSON')?['value'][0]?['Fail_x005f_x002d_x005f_Accept_x005f_x0020_x005f_Signoff']?['LookupValue']

and

body('Parse_JSON')?['value'][1]?['Fail_x005f_x002d_x005f_Accept_x005f_x0020_x005f_Signoff']?['LookupValue']


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (38,649)