cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

HELP: Nested JSON Nightmare!

"Calling all cars!" Help! Days in, sooooo close. Need your eyes!

Schema in Collection: Works just fine.

{
    "LEVEL1": [
        {
            "LEVEL2": {
                "dontWorry": "aboutMe"
            },
            "LEVEL2": {
                "dontWorry": "aboutMe"
            },
            "LEVEL2": [
                {
                    "LEVEL3": [
                        {
                            "LEVEL4": "thing",
                            "LEVEL4_A": {
                                "ID": "1234",
                            },
                            "LEVEL4_B": {
                                "thingToPatch1": "Y",
"thingToPatch2": "X"
} } ] } ] } ] }

PROBLEM

  • I need to look at LEVEL4_A ID and Patch LEVEL4_B thingToPatch.
  • I cannot for the life of me figure out what to put in the { } !

SOOO CLOSE WITH THIS:

Patch(
    First(
        First(
            colCollection.LEVEL2
        ).LEVEL2.LEVEL3
    ).LEVEL3.LEVEL4_A,

LookUp(
        First(
            First(
                colCollection.LEVEL2
            ).LEVEL2.LEVEL3
        ).LEVEL4_A.ID,
    LEVEL4_A.ID = ThisItem.LEVEL4_A.ID
),

    {
        WHAT ON EARTH GOES HERE?
    }

)

I cannot find a value that works for the Patch({update}). 
Has anyone faced this before? I'm a few days in now and wondering if I hit a limit in PowerApps.
(Definitely hit a limit in my understanding). Any ideas #PowerHomies?

@RandyHayes , @timl , @wyotim , @RusselThomas , @Drrickryp , @mr-dang@KickingApps 

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: HELP: Nested JSON Nightmare!

Some more big ideas:

  • If you see [], this indicates a table. You'll need to use First() then .value to drill into the first one.
  • If you only see {} this is just an object/record. You can drill in directly using dot notation (level1.level2.level3)
  • I didn't study your schema. I was using First() and intellisense to figure it out. This is a good strategy for exploring new APIs.

 

If the API is only consumed in PowerApps and Flow, by all means keep its schema simple. It is a luxury to have control over your output.

 

If its schema is important for showing hierarchy of data and it's used elsewhere, then keep the complexity. In PowerApps and Flow, pancake it to the parts you need. Then do some concatenation to open it it up again when you need to POST.

 

To do 50 at a time, you'll wrap ForAll around Patch. There's a few threads on this forum on that pattern.

View solution in original post

23 REPLIES 23
Super User
Super User

Re: HELP: Nested JSON Nightmare!

@ericonline 

You're in too deep!! Smiley LOL

 

At a glance, I would say there is a flaw in your lookup...it would return the LEVEL4_A record...not the LEVEL4_B record that you are trying to patch.

Perhaps take a look at that factor.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Eloy
Level: Powered On

Re: HELP: Nested JSON Nightmare!

Maybe you already know this but what goes there is the record you want to update in the format {fieldname: 'Value to change to"}

 

Patch( datasource,filter record you want to find,{field:new value} )

So assuming you have the correct record filtered

Patch(
    First(
        First(
            colCollection.LEVEL2
        ).LEVEL2.LEVEL3
    ).LEVEL3.LEVEL4_A,

LookUp(
        First(
            First(
                colCollection.LEVEL2
            ).LEVEL2.LEVEL3
        ).LEVEL4_A.ID,
    LEVEL4_A.ID = ThisItem.LEVEL4_A.ID
),

    {
        thingToPatch:"new Value"
    }

)

If that is not the case, then you have to look at making sure you have found the correct record to update on the second Patch parameter, being the long code you have First( First( .....

 

Super User
Super User

Re: HELP: Nested JSON Nightmare!

Thanks for the reply @RandyHayes !

The way I'm thinking: 
Use the Lookup to reference 4_A as it is the ID of the record, then where matching, Patch 4_B value.

Super User
Super User

Re: HELP: Nested JSON Nightmare!

Thank you @Eloy for the thoughtful response. I think I found the correct syntax for the UPDATE. 

{
       LEVEL4_B:{
            thingToPatch: "value"
        }
}

Now perhaps the Lookup() is the culprit! Eyeyeye

Super User
Super User

Re: HELP: Nested JSON Nightmare!

I came to say what @RandyHayes said.

And also, wow!

And furthermore, maybe you could put a temporary text box in and use your LookUp section in the code you posted to see what is going on by putting a period at the end and seeing what Intellisense suggests? When I am getting in the thick of it, little stuff like that helps me find what is going on. I am sure you have probably done something like that, but just in case you haven't. 

 

 

Super User
Super User

Re: HELP: Nested JSON Nightmare!

Thanks @wyotim . Unfortunately, this isn't a flat table.
My thought was that the LookUp is looking into Level4_A for the ID, then Patching the corresponding record in LEVEL4_B.

image.png
I literally have the lil buzzards in the SAME GALLERY. 
image.png

Which is combined with a goofy:

AddColumns(
    First(
        First(
            colCollection.LEVEL2
        ).LEVEL2.LEVEL3
    ).LEVEL3,
    "LEVEL4_B",
    If(
        LEVEL4_B = "Y",
    1,0
    )
)

The label for the ID is set to: ThisItem.LEVEL4_A.ID
The label for the "thingToPatch" is set to: ThisItem.LEVEL4_B.thingToPatch
lord

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: HELP: Nested JSON Nightmare!

Trying this out now. Please let me know if you've already resolved this, @ericonline.

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: HELP: Nested JSON Nightmare!


@ericonline wrote:

 

{
    "LEVEL1": [
        {
            "LEVEL2": {
                "dontWorry": "aboutMe"
            },
            "LEVEL2": {
                "dontWorry": "aboutMe"
            },
            "LEVEL2": [
                {
                    "LEVEL3": [
                        {
                            "LEVEL4": "thing",
                            "LEVEL4_A": {
                                "ID": "1234",
                            },
                            "LEVEL4_B": {
                                "thingToPatch1": "Y",
"thingToPatch2": "X"
} } ] } ] } ] }

 


If I understand correctly, you want to:

  • look up the record matching the ID in LEVEL4_A
  • revise the content in thingToPatch1

Note I removed an extra comma

 


@ericonline wrote:

 

Patch(
    First(
        First(
            colCollection.LEVEL2
        ).LEVEL2.LEVEL3
    ).LEVEL3.LEVEL4_A,

LookUp(
        First(
            First(
                colCollection.LEVEL2
            ).LEVEL2.LEVEL3
        ).LEVEL4_A.ID,
    LEVEL4_A.ID = ThisItem.LEVEL4_A.ID
),

    {
        WHAT ON EARTH GOES HERE?
    }

)

 


  • The first argument needs to be a table at the top level.
  • The second argument needs to be a record. The first argument of the LookUp needs to be a table with identical schema to the table that you're patching.
  • Between curly braces, you'll place the content you're updating. Normally, you patch content at the top level and would only show schema that you are updating. Since you're updating many levels deep, you'll need the schema and content for all fields above it.
    Note: there are many patterns that you can do in Patch; I'm describing the most common one.

 

I'll be attaching an app file with commenting code on what you'll need to do as an example. 

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: HELP: Nested JSON Nightmare!

Attached is the app with a suggested Patch statement.

 

Please note, revising content many levels deep is not easy.

 

My suggestion is since this is a collection, make it shaped the way you want. Flatten the schema by adding columns at the top level that pull up content from deeper levels. Only keep the content you care about.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 197 members 4,871 guests
Please welcome our newest community members: