Collect JSON data?

[christoffeldg]

Hey all,

I have a variable called "JSONCollection" that is set like this: 

Set(JSONCollection, JSON(ShowColumns(collection, "Title", "Score")))

This gives me a nice JSON like this: [{"Title":"Item1", "Score":2},{"Title":"Item2","Score":1}]

I then set the JSON in a text field for some quick manipulation.

Finally I want to put my JSON back into a collection, and this is somehow not working? It just gives me an empty array.

ClearCollect(collectionModified, JSONCollection) 

I saw that the value being passed to the collection was actually an array so I tried removing the brackets but no dice:

ClearCollect(collectionModified, Substitute(Substitute(JSONCollection, "[", ""), "]", ""))

How can I collect my JSON again for usage?

[timl]

Hi @christoffeldg 

There isn't a direct way to convert JSON data to a collection. There's a post here on the topic, and a common workaround is to use Flow.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Parse-JSON-string-in-Power-APPS/td-p/381832

There's also a post in the ideas that you can vote for here.

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Parse-JSON-in-PowerApps/idi-p/359563

[v-bofeng-msft · Accepted solution]

Hi @christoffeldg :

I am afraid that PowerApp cannot directly convert the JSON string into the data of the corresponding structure.

However, similar functions can be achieved through a combination of functions (only applicable to the JSON string in your case).I've made a test for your reference:

ClearCollect(
    VarCollection,
    ForAll(
        Split(
            Substitute(
                Substitute(
                    Substitute(
                        Substitute(
                            Substitute(
                                Substitute(
                                    JSONCollection,
                                    "[",
                                    ""
                                ),
                                "]",
                                ""
                            ),
                            Char(34),
                            ""
                        ),
                        "},{",
                        "#"
                    ),
                    "{",
                    ""
                ),
                "}",
                ""
            ),
            "#"
        ),
        {
            Score: If(
                First(
                    Split(
                        First(
                            Split(
                                Result,
                                ","
                            )
                        ).Result,
                        ":"
                    ).Result
                ).Result = "Score",
                Last(
                    Split(
                        First(
                            Split(
                                Result,
                                ","
                            )
                        ).Result,
                        ":"
                    ).Result
                ).Result,
                Last(
                    Split(
                        Last(
                            Split(
                                Result,
                                ","
                            )
                        ).Result,
                        ":"
                    ).Result
                ).Result
            ),
            Title: If(
                First(
                    Split(
                        Last(
                            Split(
                                Result,
                                ","
                            )
                        ).Result,
                        ":"
                    ).Result
                ).Result = "Title",
                Last(
                    Split(
                        Last(
                            Split(
                                Result,
                                ","
                            )
                        ).Result,
                        ":"
                    ).Result
                ).Result,
                Last(
                    Split(
                        First(
                            Split(
                                Result,
                                ","
                            )
                        ).Result,
                        ":"
                    ).Result
                ).Result
            )
        }
    )
)

Best Regards,

Bof

[christoffeldg]

Thanks everyone,

I don't really get why this isn't available as an out of the box function? It seems so easy to implement?

[DavesTechTips]

To Parse JSON directly in Power Apps is currently under review so please go and vote for this to make sure that it gets lots of love and attention 😉

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Parse-JSON-in-PowerApps/idi-p/359563

[Flowerbot]

What a joke this environment is, so many BASIC functions near to impossible to achieve.

[MortenSch]

I just want to check in to thank you v-bofeng-msft for your contribution to this thread. This helped me greatly in my app where I *must* use the regular "Respond to a PowerApp or flow" action when sending information from Flow to the PowerApp.

[sindhureddy]

Hi @v-bofeng-msft 
This worked for 2 outputs but similarly when I tried for 5 I am stuck. Can you please help me here for output like [{"Title":"", "Author":"","Status":"","Type":"","Count":""},{"Title":"", "Author":"","Status":"","Type":"","Count":""}]

[ganeshsanap]

@christoffeldg @sindhureddy @MortenSch @Flowerbot @DavesTechTips 

Power apps now supports experimental ParseJSON function which might be helpful to you. Check below links for detailed information:

1. Power Fx: Introducing ParseJSON 
2. ParseJSON function in Power Apps (experimental) 

---

Please click Accept as solution & 👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it 👍