azureazure-logic-apps
How to extract data from nested objects with in an array
Trying to load some data into a table from an API call; the issue being I cant seem to grab the data from the nested objects within the array.
I've managed to load all of it, minus the splits of survey_data - that all went into one column. Tried using cross apply with openjson but that failed to extract the option and answer.
The end goal is extract and load the id, option, answer [from the Options object] along side the question in the survey data object.
What's a decent method of tackling those nested objects?
Payload example (took some data out for privacy)
{
"data": [
{
"id": "8",
"contact_id": "12345",
"status": "Incomplete",
"is_test_data": "1",
"data_quality": [
],
"region": "111",
"survey_data": {
"3": {
"id": 3,
"type": "parent",
"question": "I think apples are the best",
"section_id": 4,
"options": {
"10004": {
"id": 10004,
"option": "Agree",
"answer": "Agree"
}
},
"shown": true
},
"6": {
"id": 6,
"type": "parent",
"question": "I think oranges are the best",
"section_id": 4,
"options": {
"10019": {
"id": 10019,
"option": "Agree",
"answer": "Agree"
}
},
"shown": true
},
"5": {
"id": 5,
"type": "parent",
"question": "fruit care about my health",
"section_id": 4,
"options": {
"10014": {
"id": 10014,
"option": "Agree",
"answer": "Agree"
}
},
"shown": true
},
"7": {
"id": 7,
"type": "parent",
"question": "fruit are healthy",
"section_id": 4,
"options": {
"10024": {
"id": 10024,
"option": "Agree",
"answer": "Agree"
}
},
"shown": true
},
"33": {
"id": 33,
"type": "parent",
"question": "fruit help me focus",
"section_id": 4,
"options": {
"10052": {
"id": 10052,
"option": "Agree",
"answer": "Agree"
}
},
"shown": true
},
"12": {
"id": 12,
"type": "ESSAY",
"question": "i hope to...",
"section_id": 4,
"shown": true
}
}
},
{
"id": "9",
"contact_id": "67890",
"status": "Complete",
"is_test_data": "1",
"data_quality": [
],
"region": "456",
"survey_data": {
"3": {
"id": 3,
"type": "parent",
"question": "I think Apples are the best.",
"section_id": 4,
"options": {
"10003": {
"id": 10003,
"option": "Strongly agree",
"answer": "Strongly agree"
}
},
"shown": true
},
"6": {
"id": 6,
"type": "parent",
"question": "I think oranges are the best",
"section_id": 4,
"options": {
"10018": {
"id": 10018,
"option": "Strongly agree",
"answer": "Strongly agree"
}
},
"shown": true
},
"5": {
"id": 5,
"type": "parent",
"question": "fruit care about my health",
"section_id": 4,
"options": {
"10013": {
"id": 10013,
"option": "Strongly agree",
"answer": "Strongly agree"
}
},
"shown": true
},
"7": {
"id": 7,
"type": "parent",
"question": "fruit are healthy",
"section_id": 4,
"options": {
"10023": {
"id": 10023,
"option": "Strongly agree",
"answer": "Strongly agree"
}
},
"shown": true
},
"33": {
"id": 33,
"type": "parent",
"question": "fruit help me focus",
"section_id": 4,
"options": {
"10053": {
"id": 10053,
"option": "Strongly agree",
"answer": "Strongly agree"
}
},
"shown": true
},
"12": {
"id": 12,
"type": "ESSAY",
"question": "I hope to...",
"section_id": 4,
"answer": "eat all the fruit",
"shown": true
}
}
}
]
}
Thanks in advance.
Solution
I hate to say it but with the dynamic object names comes the need to use something that works dynamically.
My suggestion would be to use the Advanced Data Operations connector and more specifically the Json to Table operation ...
Basically, the JSON variable holds the JSON you provided in your question.
I've used the Filter option to only give me records where the answer property is not equal to null.
Note: Something looks off with the last item in your json so just check that. It may have skewed the results.
That produced the resulting output which looks like this ...
[
{
"status": "Incomplete",
"region": "111",
"is_test_data": "1",
"id": 10004,
"contact_id": "12345",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "I think apples are the best",
"option": "Agree",
"answer": "Agree"
},
{
"status": "Incomplete",
"region": "111",
"is_test_data": "1",
"id": 10014,
"contact_id": "12345",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "fruit care about my health",
"option": "Agree",
"answer": "Agree"
},
{
"status": "Incomplete",
"region": "111",
"is_test_data": "1",
"id": 10019,
"contact_id": "12345",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "I think oranges are the best",
"option": "Agree",
"answer": "Agree"
},
{
"status": "Incomplete",
"region": "111",
"is_test_data": "1",
"id": 10024,
"contact_id": "12345",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "fruit are healthy",
"option": "Agree",
"answer": "Agree"
},
{
"status": "Incomplete",
"region": "111",
"is_test_data": "1",
"id": 10052,
"contact_id": "12345",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "fruit help me focus",
"option": "Agree",
"answer": "Agree"
},
{
"status": "Complete",
"region": "456",
"is_test_data": "1",
"id": 10003,
"contact_id": "67890",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "I think Apples are the best.",
"option": "Strongly agree",
"answer": "Strongly agree"
},
{
"status": "Complete",
"region": "456",
"is_test_data": "1",
"id": 10013,
"contact_id": "67890",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "fruit care about my health",
"option": "Strongly agree",
"answer": "Strongly agree"
},
{
"status": "Complete",
"region": "456",
"is_test_data": "1",
"id": 10018,
"contact_id": "67890",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "I think oranges are the best",
"option": "Strongly agree",
"answer": "Strongly agree"
},
{
"status": "Complete",
"region": "456",
"is_test_data": "1",
"id": 10023,
"contact_id": "67890",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "fruit are healthy",
"option": "Strongly agree",
"answer": "Strongly agree"
},
{
"status": "Complete",
"region": "456",
"is_test_data": "1",
"id": 12,
"contact_id": "67890",
"type": "ESSAY",
"shown": true,
"section_id": 4,
"question": "I hope to...",
"option": null,
"answer": "eat all the fruit"
},
{
"status": "Complete",
"region": "456",
"is_test_data": "1",
"id": 10053,
"contact_id": "67890",
"type": "parent",
"shown": true,
"section_id": 4,
"question": "fruit help me focus",
"option": "Strongly agree",
"answer": "Strongly agree"
}
]
Now, I'm not 100% sure if that will fit the bill for you perfectly because there are a number of properties that are not represented because of the fact that the property is represented multiple times but if you're only after the id, option and answer properties then you can see them there.
Obviously, this is a paid connector but it's hell cheap and does what you need.
- Get username from Azure Identity
- What prevents Databricks IP access lists from being executed
- HTTP 404 error with Swagger UI on Azure Functions app
- Azure devops - build number vs build id
- Unable to Retrieve Hidden MailFolders (isHidden = true) with Microsoft Graph API
- Parse array & json to right numbers of column
- Run script on Azure VM Scale Set when deprovisioning
- Azure Pipeline to trigger Pipeline using YAML
- Keycloak Admin Console Login - Infinite Loop
- Make Azure Keyvault secrets available in entire pipeline
- Azure Logic Apps - Queries for telemetry 2.0 show no results
- Print function not working without flush=True when used with the While loop
- Cannot authorize variable group in Azure Pipelines
- Azure Maps rejecting Route Request with ZipCode
- Create-React-App hosted in Azure getting a 404 with manual refresh or by typing the url manually on any page except the home page
- How to set up a simple Next.js web app with CI/CD on Azure?
- How to avoid explicitly acquiring access tokens when calling Microsoft Graph API in Node.js?
- Why I'm getting 404 Resource Not Found to my newly Azure OpenAI deployment?
- Scanning for malware in files uploaded to Azure
- Azure blob, controlling filename on download
- How to delete/clear active/dead-letter messages from Azure Service Bus Queue?
- How to Generate .AAB file and Sign Android app Bundle using azure pipeline
- Get Access Token from Microsoft Graph for ClientId with delegated permissions
- Can't create password containing parentheses in Azure CLI
- Azure Web App Cannot Access Azure Container Registry Using Managed Identity
- Azure Access token just created but not capable to verify that is authentic
- Querying azure table storage for null values
- Azure App Functions, Storage queue trigger
- Error while running Terraform Import Command to import Azure Key Vault
- How to get Node.js app running on Azure App Service?