HELP: Nested JSON Nightmare!

[ericonline]

"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 

[KickingApps]

This is what had me perplexed; I couldn't figure out how to use the [ ].  Basically, @Mr-Dang-MSFT , your patch almost mirrors the JSON.  Clever.

[ericonline]

Man! Woman and Child! this project just increased in difficulty!

Thank you @Mr-Dang-MSFT for the sample app, it is very helpful.

The big revelations are:

• Must maintain the original schema for each Update (I don't know if this is possible when the Gallery is 50 items deep)
• Must maintain the unused values in the original schema for each Update (I don't know if this is possible when the Gallery is 50 items deep)
• I'm not sure I'll be able to POST the results of this to the web service i'm using.

Questions:

• Is there a better way to do this? 
  • I used AddColumns for display purposes, with LEVEL4_A and B right next to eachother in the Gallery.
    • But I can't find a way to reference A and patch B!
    • I'm literally trying to say:
      • OnChange, where 4_A = ThisItem.ID, Patch 4_B with "N" or "Y" accordingly
      • Collect the Changes so I can POST them using a Custom Connector (which is already built and working great in the Custom Connector Test area)
    • The values are so close to eachother! I wonder if I'm missing something very simple
  •  
  • 

It must not be very common to manipulate JSON 3-4 levels deep with PowerApps. 

* I'm now wondering if I should to get the API provider to flatten the JSON before collecting. 

Man! Thanks again everyone. Please keep the ideas coming!

(As an aside, the additional comma in original post was from redacting).

[ericonline]

@KickingApps , another interesting piece about the [ ] is that they appear to require a ".Value" when the JSON is collected raw.

Since we're collecting the JSON from a GET request, the ".Value" 's don't appear when "digging" into the hierarchy.

complex one!

[Mr-Dang-MSFT · Accepted solution]

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.

[timl]

Hi @ericonline 

As I'm coming into this late, there's not that much of value that I can really add.

But well done everyone for all the great suggestions - @Mr-Dang-MSFT @RandyHayes  @KickingApps @wyotim @Eloy . It's wonderful to see such cohesive community effort.

And thanks @ericonline for coming up with some of the most complicated questions on this forum!

[wyotim]

Well said @timl! And very true that @ericonline brings up some of the most interesting questions. As someone who usually deals with considerably simpler issues, I really appreciate being able to get exposure to more complex data scenarios!

@Mr-Dang-MSFT, masterful explaination and suggested solution, as always!

#PowerHomiesFTW 

[ericonline]

GOT IT!

Thank you everyone for the insights, ideas and great examples.
Breakdown (the logical one, not the mental one 🙂 😞

• I was receiving that nested JSON as a GET response from a Custom Connector.
• I was trying to Patch changes into the nested GET response with the idea that I'd POST the whole thing at once (thus the nightmare)
• A hot shower later (yep, that was the moment), I realized that instead of maintaining the whole schema in the Patch statement, I could instead Collect the "ID" and "thisThing" from the Gallery they were already sitting in!
• Then put this Collection into the POST schema (forget trying to Patch the GET).
• The JSON was still nested 4 levels, but it worked. See code below.

I wrote up some lessons learned here. The big revelation was that the SAME schema that shows "Success" in the Custom Connector Test area did NOT work for the Custom Connector POST function, Table() was required in lieu of the [ ] 's .
I repeat: Square brackets WOULD NOT WORK in the function!

/*================================================================
POST request
NOTE: Square brackets do NOT work in lieu of Table()!
================================================================*/
ClearCollect(colPostResults,
    myCustomConnector.POST(
        {
            LEVEL1:
            Table(
                {
                    LEVEL2_A:{
                        thinga: varThinga,
                        thngb: varThingb
                    },
                    LEVEL2_B:
                    colStatuses
                }
            )
        }
    )
)

OnChange of the toggles:

Collect(colStatuses,
    Table(
        {
            LEVEL_3:
            Table(
                {
                    LEVEL_4A: {               //Remember me?  :)
                        ID: ThisItem.ID,
                    },
                    LEVEL_4B:{               //Muahahahah! Remember me too? :(
                        thingToPatch: 
                        If(
                            Toggle.Value = false,"N",
                            Toggle.Value = true,"Y"
                        )
                    }
                }
            )
        }
    )
)

SO STOKED! Its working like a CHARM.

[KickingApps]

Appstronaut badge worthy is you ask me! Excellent work and thanks for sharing your knowledge as you traveled this path; I learned so much.

[wyotim]

Awesome! Thanks for sharing the solution and lessons learned. I agree with @KickingApps: Appstronaut level! You need to add Deeply-Nested JSON Whisperer to your list of skills (and consider changing your forum pic to Robert Redford). 

[ericonline]

Maybe i'll name my first boy Jaysahn. Second one, Seequahl. 🙂