Fetch value from SharePoint JSON output
I am getting below JSON object from a Sharepoint list. How can I get the value for Company in the Data operation ( select) for logic apps. I did item()['Company']?['Value'] and it is not working. Any suggestions?
"body": [
{
"Company": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 0,
"Value": "Test1"
},
"Date From": "2022-03-30",
"Date To": "2022-03-31",
"Title": "Title 1"
},
{
"Company": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 2,
"Value": "Line2"
},
"Date From": "2022-03-21",
"Date To": "2022-03-29",
"Title": "Title 2"
}
]
}
I am fetching share-point list and then using data operations (select) to get the JSON as output.
I need JSON in the below format so that I can pass this to stored procedure and insert into the Azure SQL DB. I have another 12 items in the list.
[
{
"Company": "Test1",
"Date From": "2022-03-30",
"Date To": "2022-03-31",
"Title": "Title 1"
},
{
"Company": "Line2",
"Date From": "2022-03-21",
"Date To": "2022-03-29",
"Title": "Title 2"
}
]
Rather than select, you can set a variable. We're all different but that makes far more sense to me.
Your expression is much the same, I used ...
item()['Company']['Value']
Just make sure you initialise the variable outside and prior to the For each ...
This is the result for the first item in the array ...
To compile a full JSON object and add it to an array, again, simply use a variable and specify the values as need be.
Firstly, initialize your array outside of the For each ...
... and then in the For each, add an object to the array variable on each loop (make sure you include the quotes around the expression where required) ...
You just have to compile the JSON. The end result will look like this ...
This is the JSON in full ...
[
{
"Company": "Line2",
"Date From": "2022-03-21",
"Date To": "2022-03-29",
"Title": "Title 2"
},
{
"Company": "Test1",
"Date From": "2022-03-30",
"Date To": "2022-03-31",
"Title": "Title 1"
}
]
Also, you'll notice my list has come out in a different order, that's because the For each runs in parallel, if you need to avoid that, change the settings so it runs in a single thread ...
This is the JSON definition of the LogicApp, you can load it into your tenant and test with it ...
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"For_each": {
"actions": {
"Append_to_SQL_Array": {
"inputs": {
"name": "SQL Array",
"value": {
"Company": "@{item()['Company']['Value']}",
"Date From": "@{item()['Date From']}",
"Date To": "@{item()['Date To']}",
"Title": "@{item()['Title']}"
}
},
"runAfter": {},
"type": "AppendToArrayVariable"
}
},
"foreach": "@variables('SharePoint JSON')",
"runAfter": {
"Initialize_SQL_Array": [
"Succeeded"
]
},
"runtimeConfiguration": {
"concurrency": {
"repetitions": 1
}
},
"type": "Foreach"
},
"Initialize_SQL_Array": {
"inputs": {
"variables": [
{
"name": "SQL Array",
"type": "array"
}
]
},
"runAfter": {
"Initialize_SharePoint_JSON": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_SharePoint_JSON": {
"inputs": {
"variables": [
{
"name": "SharePoint JSON",
"type": "array",
"value": [
{
"Company": {
"Id": 0,
"Value": "Test1",
"odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
},
"Date From": "2022-03-30",
"Date To": "2022-03-31",
"Title": "Title 1"
},
{
"Company": {
"Id": 2,
"Value": "Line2",
"odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
},
"Date From": "2022-03-21",
"Date To": "2022-03-29",
"Title": "Title 2"
}
]
}
]
},
"runAfter": {},
"type": "InitializeVariable"
},
"Initialize_variable": {
"inputs": {
"variables": [
{
"name": "Result",
"type": "array",
"value": "@variables('SQL Array')"
}
]
},
"runAfter": {
"For_each": [
"Succeeded"
]
},
"type": "InitializeVariable"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {},
"triggers": {
"Recurrence": {
"evaluatedRecurrence": {
"frequency": "Month",
"interval": 12
},
"recurrence": {
"frequency": "Month",
"interval": 12
},
"type": "Recurrence"
}
}
},
"parameters": {}
}
- Power Automate. Delete from parent and child list using ID from parent list
- Logic App to Queue - SSL connection could not be established
- Error in Azure logic app workflow when using inline java script action
- Azure Logic App - Create CSV form JSON to send in Email
- Azure Key Vault Disabled public access, how to access it through the portal via internet
- trigger logic app from Azure Service bus in sequance
- Initiate Azure DevOps Release Pipeline from Logic App and Retrieve Environment Variable Output from the pipeline
- xpath expression not working in Logic Apps
- Blocker when adding a blob action in the logic app
- Workflow and API connection provisioning for Standard logic app via Bicep templates/scripts
- Logic App (standard) deployment via DevOps pipeline - kudu service unavailable 503
- Logic Apps copy action gives: The managed identity used with this operation no longer exists. To continue, set up an identity or change the connection
- Logic App PDF File from Sharepoint uploading to Azure Blob is Blank
- Delete files from destination if deleted from source using logic app
- Creating CSV table from Crowdstrike API output
- Convert report JobFailureCode name Succes with Green and Failed with Red
- Logic App Workflow - Flat File Encode error - Invalid Schema
- Copy Folder along with files from Azure Blob to SharePoint
- In Azure Logic Apps, how do I take SQL query results that are JSON and populate an array so I can use the results is a conditional logic?
- Append result in Azure Logic Apps
- Create an API Connection to Azure KeyVault using Service Principal Authentication through ARM template
- How to trigger Azure Logic App when a file is not being uploaded in Blob Storage? When you have replacing previous file?
- How to iterate through a JSON array and read a specific value using Logic App?
- Generate workflow in current devops sprint through logic apps
- Trigger concurrency runtime configuration cannot be removed once specified
- The execution of template action 'Response' is skipped: the client application is not waiting for a response from service
- Using Terraform to deploy Standard Logic App with Liquid Template Maps
- VSCode Logic App func.exe Service Bus Transport Type
- How can I get the UPN of the person who is activating a EntraID Identity Governance Lifecycle Workflow?
- Azure Logic App - xpath for xml with namespace prefix