cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ac89
New Member

How can I separate and access this nested information from a JSON object?

Hi all,

 

I'm making a system which references a noSQL dataset I keep as a set of JSON files in sharepoint. This data is all about tools so it holds information like the type of tool, whether it's a multi-tool, and a list of recent health and safety tests carried out on it. Because not all tools are single-use tools (that is to say some drills also have a hammer drill or chisel setting which require separate health and safety checks) and some tools are tested more frequently than others, interrogating the test data is proving more difficult than I thought. Here's an example of some different tools:

 

a single-use tool with only one recent test:

 

 

 

{
    "Desc": "2 Stroke Breaker",
    "multi": false,
    "tests": {
        "D": {
            "2019-04-12": 133
        }
    }
}

 

 

 

 

a single-use tool with a few recent tests:

 

 

 

{
    "Desc": "Hedgecutter",
    "multi": false,
    "tests": {
        "D": {
            "2019-10-09": 19,
            "2021-11-08": 20
        }
    }
}

 

 

 

 

a multi-tool:

 

 

 

{
    "Desc": "Drill",
    "multi": true,
    "tests": {
        "C": {
            "2021-04-27": 173
        },
        "D": {
            "2021-04-27": 46
        },
        "H": {
            "2021-04-27": 86
        }
    }
}

 

 

 

 

Because every JSON file is slightly different in this respect, what's the best way for me to dynamically break down a JSON file so I can access every single test whilst also maintaining what test group it belongs to (D/H/C)? My flow will only ever access one file but which one it accesses will vary from run to run so it seems I can't rely on parse JSON with a set schema unless I'm using the parse JSON feature wrong?

 

I'm also not against rebuilding my JSON files with a different test structure if that will makes life easier. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
joelzehring
Advocate III
Advocate III

Would it be an option to change the "tests" property to an array of test objects like below? This would allow you to use Power Automate actions like Apply to each, Filter array, and Select against the array regardless of the number or types of tests.

...
"tests": [
  {
    "group": "D",
    "date": "2019-04-12",
    "score": 133
  }
]

or

...
"tests": [
  {
    "group": "D",
    "date": "2019-10-09",
    "score": 19
  },
  {
    "group": "D",
    "date": "2021-11-08",
    "score": 20
  }
]

or

...
"tests": [
  {
    "group": "C",
    "date": "2021-04-27",
    "score": 173
  },
  {
    "group": "D",
    "date": "2021-04-27",
    "score": 46
  },
  {
    "group": "H",
    "date": "2021-4-27",
    "score": 86
  }
]

 With the above schema, in an Apply to each action, you might store the entire JSON in a variable (currentTool) and reference the array like this:

variables('currentTool')['tests']

and then act only on certain tests with a condition like this:

equals(items()['group'],'D')

As it stands, this kind of iteration would require a bunch of if() and contains() expressions, and even then, when you get down to the date level, it would still be tough to return all the properties and values in a given test group.

 

Thoughts?

View solution in original post

3 REPLIES 3
joelzehring
Advocate III
Advocate III

Would it be an option to change the "tests" property to an array of test objects like below? This would allow you to use Power Automate actions like Apply to each, Filter array, and Select against the array regardless of the number or types of tests.

...
"tests": [
  {
    "group": "D",
    "date": "2019-04-12",
    "score": 133
  }
]

or

...
"tests": [
  {
    "group": "D",
    "date": "2019-10-09",
    "score": 19
  },
  {
    "group": "D",
    "date": "2021-11-08",
    "score": 20
  }
]

or

...
"tests": [
  {
    "group": "C",
    "date": "2021-04-27",
    "score": 173
  },
  {
    "group": "D",
    "date": "2021-04-27",
    "score": 46
  },
  {
    "group": "H",
    "date": "2021-4-27",
    "score": 86
  }
]

 With the above schema, in an Apply to each action, you might store the entire JSON in a variable (currentTool) and reference the array like this:

variables('currentTool')['tests']

and then act only on certain tests with a condition like this:

equals(items()['group'],'D')

As it stands, this kind of iteration would require a bunch of if() and contains() expressions, and even then, when you get down to the date level, it would still be tough to return all the properties and values in a given test group.

 

Thoughts?

Rhiassuring
Super User
Super User

Hah, so, I was busy testing this out and when I refreshed I saw @joelzehring 's response which might be a lot cleaner than mine! But I'm not a JSON writing whiz, I can just get stuff out of it.

But ... just in case it's of use, here's what I did:

 

I'm using this as my test data:

 

Rhiassuring_0-1649982088674.png

 

I initialized an Array called arrTestTypes: ["C","H","D"] and then initialized a string variable called "txtCurrentTestType".

 

I used a Parse JSON - but I only submitted this to it to generate the schema:

 

{
"Desc": "Drill",
"multi": true,
"tests": { } 
}


Then I created an "Apply to Each" and crammed my "arrTestTypes" into it. I set the txtCurrentTestType variable to "Current Item", and used a "Compose" to get the current "Test". If there isn't one, it just skips it.

 

body('Parse_JSON')?['tests']?[variables('txtCurrentTestType')]

This returns an array of all the test dates / values per test type.

 

Here's how D came through:

 

Rhiassuring_1-1649982417931.png

 

Here's how H came through:

Rhiassuring_2-1649982462287.png

 

And C:

Rhiassuring_3-1649982474454.png

 

Here's the whole deal: 

Rhiassuring_4-1649982524472.png

 



 

Hopefully this is helpful -- at least you have choices!!! Hahah. 

Cheers,


Rhia

 

 

 

ac89
New Member

Thanks guys, I'm quite new to Power Automate and the transfer from free typing to module-based solutions is weird. I reformatted the JSON docs, used Parse JSON to get the elements and then used filter arrays to narrow down the right info for each flow, including loads of error checking along the way. I've been testing it for an hour now and it seems to work exactly as I wanted!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (4,216)