Hi y'all,
This is a hard one... I am sending an API GET request that returns data for 4000 different records in JSON format. I am attempting to flatten the response, so I can display it in Gallery form in my Power App. However, the JSON structure is incredibly frustrating.
I need to extract each value from each key/value pair in both nested arrays "domain_metadata" and domain_private_metadata". For example, my output should have a column called "Department Name": that extracts the value property under the key "Ownership_Department-name".
Two complications that make this even more frustrating:
1) The key/value pairs in these arrays are not consistently on the same index. For some records, the department name key is on index [0]. but for others it might be index[1].
2) The arrays are dynamic. For instance, some records have 2 key/value pairs in the "domain_metadata" array and some have 6.
Here is a sample JSON from 2 records that shows the limitations I am talking about:
{
"body": {
"results": [
{
"resource": {
"name": "Dataset 1 Title",
"id": "abcd-efgh",
"description": "Dataset 1 Description"
},
"classification": {
"domain_category": "Financial",
"domain_tags": [
"sampleTag1",
"sampleTag2",
"sampleTag3"
],
"domain_metadata": [
{
"key": "Ownership_Department-name",
"value": "Financial Department"
},
{
"key": "Strategic-Area_Strategic-Direction-Outcome",
"value": "Health and Environment"
},
{
"key": "Publishing-Information_Update-Frequency",
"value": "Weekly"
}
],
"domain_private_metadata": [
{
"key": "Ownership_Public-Access-Level",
"value": "Public"
},
{
"key": "Publishing-Information_Automation-Method",
"value": "Microsoft O365"
},
{
"key": "Publishing-Information_Automation-Method-(if-Other)",
"value": ""
}
]
}
},
{
"resource": {
"name": "Dataset 2 Title",
"id": "abcd-efgh",
"description": "Dataset 2 Description"
},
"classification": {
"domain_category": "Geographic",
"domain_tags": [
"sampleTag1",
"sampleTag2"
],
"domain_metadata": [
{
"key": "Strategic-Area_Strategic-Direction-Outcome",
"value": "Locations and Maps"
},
{
"key": "Ownership_Department-name",
"value": "GIS Department"
}
],
"domain_private_metadata": [
{
"key": "Publishing-Information_Automation-Method",
"value": "Microsoft O365"
},
{
"key": "Publishing-Information_Automation-Method-(if-Other)",
"value": ""
},
{
"key": "Ownership_Public-Access-Level",
"value": "Public"
}
]
}
}
]
}
}
How can I get all this information flattened so I can work with it? I can't use loops because it has to loop through 4000+ rows and takes too much time. I have a Premium license, so I am open to Premium solutions.
