JSON function using JSONFormat.IndentFour not working as suggested in documentation

[MichelH]

I read the JSONFormat.IndentFour option should be able to format a json string, giving it proper indenting so it becomes readable.

The example I found here uses a label to display the formated json text.

It even shows a coloured formatted JSON output. (could it be real?)

When I try to do the same, I do not see this result unfortunately.

My plain JSON text, displayed in a label looks like this:

{"name":"c43187c3-3693-4ac9-844f-08dfefd8f09d","id":"/providers/Microsoft.Flow/....

After using the JSON function:

Set( OutPutTxt , JSON( MyJSONtxt ,JSONFormat.IndentFour ));

the output in the label looks like this:

"{\"name\":\"c43187c3-3693-4ac9-844f-08dfefd8f09d\",\"id\":\"/providers/Microsoft.Flow/....

Has anyone tried? Does anyone see where I go wrong here?

I've tried the same in an input field, and a rich text field.

I see the same results.

Thanks for any feedback.

[Pstork1]

Try loading it into a Collection instead of a Variable.  I think that's your problem. Even if its only one value it should be coming back from Flow in the form of a record as JSON.  

[MichelH]

Hi @Pstork1 ,

I will try that, but I have my doubts.

This is what it looks like.

{"name":"c43187c3-3693-4ac9-844f-08dfefd8f09d","id":"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c/flows/c43187c3-3693-4ac9-844f-08dfefd8f09d","type":"Microsoft.Flow/environments/flows","properties":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_logicflows","displayName":"Button -> Get row","userType":"Owner","definition":{"$schema":"https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#","contentVersion":"1.0.0.0","parameters":{"$connections":{"defaultValue":{},"type":"Object"},"$authentication":{"defaultValue":{},"type":"SecureObject"}},"triggers":{"manual":{"type":"Request","kind":"Button","inputs":{"schema":{"type":"object","required":[],"properties":{}}}}},"actions":{"Get_row":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"GetItem"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']"}},"method":"get","path":"/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MH_SAPdata_updates]'))}/items/@{encodeURIComponent(encodeURIComponent('121029'))}","authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}}},"Initialize_variable":{"runAfter":{"Get_row":["Succeeded"]},"type":"InitializeVariable","inputs":{"variables":[{"name":"com","type":"String","value":"@{body('Get_row')?['Comment']};@{body('Get_row')?['Reason']};@{body('Get_row')?['Status']}"}]}}},"outputs":{}},"triggerSchema":{"type":"object","required":[],"properties":{}},"state":"Started","connectionReferences":[{"connectionName":"0cc11648080d441ead1904e034b305bb","displayName":"SQL Server","id":"/providers/Microsoft.PowerApps/apis/shared_sql"}],"installedConnectionReferences":{"shared_sql":{"connectionName":"0cc11648080d441ead1904e034b305bb","apiDefinition":{"name":"shared_sql","id":"/providers/Microsoft.PowerApps/apis/shared_sql","type":"/providers/Microsoft.PowerApps/apis","properties":{"displayName":"SQL Server","iconUri":"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png","purpose":"NotSpecified","connectionParameters":{"server":{"type":"string","uiDefinition":{"displayName":"SQL server name","description":"SQL server name","tooltip":"SQL server name","constraints":{"tabIndex":1,"required":"true","capability":["gateway","cloud"]}}},"database":{"type":"string","uiDefinition":{"displayName":"SQL database name","description":"SQL database name","tooltip":"SQL database name","constraints":{"tabIndex":2,"required":"true","capability":["gateway","cloud"]}}},"authType":{"type":"string","allowedValues":[{"value":"windows"},{"value":"basic"}],"uiDefinition":{"displayName":"Authentication Type","description":"Authentication type to connect to your database","tooltip":"Authentication type to connect to your database","constraints":{"tabIndex":3,"required":"false","allowedValues":[{"text":"Windows","value":"windows"},{"text":"Basic","value":"basic"}],"capability":["gateway"]}}},"username":{"type":"securestring","uiDefinition":{"displayName":"Username","description":"Username credential","tooltip":"Username credential","constraints":{"tabIndex":4,"clearText":true,"required":"true","capability":["gateway","cloud"]}}},"password":{"type":"securestring","uiDefinition":{"displayName":"Password","description":"Password credential","tooltip":"Password credential","constraints":{"tabIndex":5,"required":"true","capability":["gateway","cloud"]}}},"gateway":{"type":"gatewaySetting","gatewaySettings":{"dataSourceType":"sql","connectionDetails":["server","database"]},"uiDefinition":{"constraints":{"tabIndex":6,"required":"false","capability":["gateway"]}}},"sqlConnectionString":{"type":"securestring","uiDefinition":{"displayName":"SQL Connection String","description":"SQL Connection String","tooltip":"Password credential","constraints":{"required":"false","hidden":"true"}}}},"runtimeUrls":["https://europe-001.azure-apim.net/apim/sql"],"primaryRuntimeUrl":"https://europe-001.azure-apim.net/apim/sql","metadata":{"source":"marketplace","brandColor":"#ba141a","allowSharing":true,"useNewApimVersion":"true","version":{"previous":"releases/v1.0.1305\\1.0.1305.1883","current":"releases/v1.0.1306\\1.0.1306.1889"}},"capabilities":["tabular","gateway","cloud"],"tier":"Premium","isCustomApi":false,"description":"Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.","createdTime":"2016-09-30T22:53:18.4800804Z","changedTime":"2020-02-19T17:33:10.3921889Z"}},"source":"Invoker","id":"/providers/Microsoft.PowerApps/apis/shared_sql","displayName":"SQL Server","iconUri":"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png","brandColor":"#ba141a","tier":"NotSpecified"}},"createdTime":"2018-10-24T07:27:34.7336734Z","lastModifiedTime":"2018-10-24T07:28:29.9313345Z","environment":{"name":"Default-e3cf3c98-a978-465f-8254-9d541eeea73c","type":"Microsoft.Flow/environments","id":"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c"},"definitionSummary":{"triggers":[{"type":"Request","kind":"Button"}],"actions":[{"type":"ApiConnection","swaggerOperationId":"GetItem","metadata":{"flowSystemMetadata":{"swaggerOperationId":"GetItem"}}},{"type":"InitializeVariable"}]},"creator":{"tenantId":"e3cf3c98-a978-465f-8254-9d541eeea73c","objectId":"cd3205c7-55ca-486e-88f7-232803bf2ba4","userId":"cd3205c7-55ca-486e-88f7-232803bf2ba4","userType":"ActiveDirectory"},"flowTriggerUri":"https://europe-001.azure-apim.net:443/apim/logicflows/c43187c3-3693-4ac9-844f-08dfefd8f09d/triggers/manual/run?api-version=2016-06-01","installationStatus":"Broken","provisioningMethod":"FromDefinition","flowFailureAlertSubscribed":true,"referencedResources":[]}}

 Now you understand why I'm keen on having the formatting.  🙂

[Pstork1]

are you sending that back from Flow using the Respond to Power Apps?  Or the HTTP response action?  For something that large it should be the http response action.  Respond to Power Apps is limited in size and will tend to truncate the JSON.

[MichelH]

Hi @Pstork1 ,

I'm using 'respond to power apps'.

The output is not truncated.

When I put it into the collection it does not throw an error.

When I display the output of the JSON function in a label

ClearCollect(FlowResCol,FlowAction2.Run("Get","",Gallery1.Selected.Id).res);
Set( OutPutTxt , JSON( FlowResCol ,JSONFormat.IndentFour ));

I see this:

[
    {
        "Value": "{\"name\":\"c43187c3-3693-4ca9-844f-08dfefd8f09d\",\"id\":\"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c/flows/c43187c3-3693-4ca9-844f-08dfefd8f09d\",\"type\":\"Microsoft.Flow/environments/flows\",\"properties\":{\"apiId\":\"/providers/Microsoft.PowerApps/apis/shared_logicflows\",\"displayName\":\"Button -> Get row\",\"userType\":\"Owner\",\"definition\":{\"$schema\":\"https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#\",\"contentVersion\":\"1.0.0.0\",\"parameters\":{\"$connections\":{\"defaultValue\":{},\"type\":\"Object\"},\"$authentication\":{\"defaultValue\":{},\"type\":\"SecureObject\"}},\"triggers\":{\"manual\":{\"type\":\"Request\",\"kind\":\"Button\",\"inputs\":{\"schema\":{\"type\":\"object\",\"required\":[],\"properties\":{}}}}},\"actions\":{\"Get_row\":{\"runAfter\":{},\"metadata\":{\"flowSystemMetadata\":{\"swaggerOperationId\":\"GetItem\"}},\"type\":\"ApiConnection\",\"inputs\":{\"host\":{\"connection\":{\"name\":\"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']\"}},\"method\":\"get\",\"path\":\"/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MH_SAPdata_updates]'))}/items/@{encodeURIComponent(encodeURIComponent('121029'))}\",\"authentication\":{\"type\":\"Raw\",\"value\":\"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']\"}}},\"Initialize_variable\":{\"runAfter\":{\"Get_row\":[\"Succeeded\"]},\"type\":\"InitializeVariable\",\"inputs\":{\"variables\":[{\"name\":\"com\",\"type\":\"String\",\"value\":\"@{body('Get_row')?['Comment']};@{body('Get_row')?['Reason']};@{body('Get_row')?['Status']}\"}]}}},\"outputs\":{}},\"triggerSchema\":{\"type\":\"object\",\"required\":[],\"properties\":{}},\"state\":\"Started\",\"connectionReferences\":[{\"connectionName\":\"0cc11648080d441ead1904e034b305bb\",\"displayName\":\"SQL Server\",\"id\":\"/providers/Microsoft.PowerApps/apis/shared_sql\"}],\"installedConnectionReferences\":{\"shared_sql\":{\"connectionName\":\"0cc11648080d441ead1904e034b305bb\",\"apiDefinition\":{\"name\":\"shared_sql\",\"id\":\"/providers/Microsoft.PowerApps/apis/shared_sql\",\"type\":\"/providers/Microsoft.PowerApps/apis\",\"properties\":{\"displayName\":\"SQL Server\",\"iconUri\":\"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png\",\"purpose\":\"NotSpecified\",\"connectionParameters\":{\"server\":{\"type\":\"string\",\"uiDefinition\":{\"displayName\":\"SQL server name\",\"description\":\"SQL server name\",\"tooltip\":\"SQL server name\",\"constraints\":{\"tabIndex\":1,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"database\":{\"type\":\"string\",\"uiDefinition\":{\"displayName\":\"SQL database name\",\"description\":\"SQL database name\",\"tooltip\":\"SQL database name\",\"constraints\":{\"tabIndex\":2,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"authType\":{\"type\":\"string\",\"allowedValues\":[{\"value\":\"windows\"},{\"value\":\"basic\"}],\"uiDefinition\":{\"displayName\":\"Authentication Type\",\"description\":\"Authentication type to connect to your database\",\"tooltip\":\"Authentication type to connect to your database\",\"constraints\":{\"tabIndex\":3,\"required\":\"false\",\"allowedValues\":[{\"text\":\"Windows\",\"value\":\"windows\"},{\"text\":\"Basic\",\"value\":\"basic\"}],\"capability\":[\"gateway\"]}}},\"username\":{\"type\":\"securestring\",\"uiDefinition\":{\"displayName\":\"Username\",\"description\":\"Username credential\",\"tooltip\":\"Username credential\",\"constraints\":{\"tabIndex\":4,\"clearText\":true,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"password\":{\"type\":\"securestring\",\"uiDefinition\":{\"displayName\":\"Password\",\"description\":\"Password credential\",\"tooltip\":\"Password credential\",\"constraints\":{\"tabIndex\":5,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"gateway\":{\"type\":\"gatewaySetting\",\"gatewaySettings\":{\"dataSourceType\":\"sql\",\"connectionDetails\":[\"server\",\"database\"]},\"uiDefinition\":{\"constraints\":{\"tabIndex\":6,\"required\":\"false\",\"capability\":[\"gateway\"]}}},\"sqlConnectionString\":{\"type\":\"securestring\",\"uiDefinition\":{\"displayName\":\"SQL Connection String\",\"description\":\"SQL Connection String\",\"tooltip\":\"Password credential\",\"constraints\":{\"required\":\"false\",\"hidden\":\"true\"}}}},\"runtimeUrls\":[\"https://europe-001.azure-apim.net/apim/sql\"],\"primaryRuntimeUrl\":\"https://europe-001.azure-apim.net/apim/sql\",\"metadata\":{\"source\":\"marketplace\",\"brandColor\":\"#ba141a\",\"allowSharing\":true,\"useNewApimVersion\":\"true\",\"version\":{\"previous\":\"releases/v1.0.1305\\\\1.0.1305.1883\",\"current\":\"releases/v1.0.1306\\\\1.0.1306.1889\"}},\"capabilities\":[\"tabular\",\"gateway\",\"cloud\"],\"tier\":\"Premium\",\"isCustomApi\":false,\"description\":\"Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.\",\"createdTime\":\"2016-09-30T22:53:18.4800804Z\",\"changedTime\":\"2020-02-19T17:33:10.3921889Z\"}},\"source\":\"Invoker\",\"id\":\"/providers/Microsoft.PowerApps/apis/shared_sql\",\"displayName\":\"SQL Server\",\"iconUri\":\"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png\",\"brandColor\":\"#ba141a\",\"tier\":\"NotSpecified\"}},\"createdTime\":\"2018-10-24T07:27:34.7336734Z\",\"lastModifiedTime\":\"2018-10-24T07:28:29.9313345Z\",\"environment\":{\"name\":\"Default-e3cf3c98-a978-465f-8254-9d541eeea73c\",\"type\":\"Microsoft.Flow/environments\",\"id\":\"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c\"},\"definitionSummary\":{\"triggers\":[{\"type\":\"Request\",\"kind\":\"Button\"}],\"actions\":[{\"type\":\"ApiConnection\",\"swaggerOperationId\":\"GetItem\",\"metadata\":{\"flowSystemMetadata\":{\"swaggerOperationId\":\"GetItem\"}}},{\"type\":\"InitializeVariable\"}]},\"creator\":{\"tenantId\":\"e3cf3c98-a978-465f-8254-9d541eeea73c\",\"objectId\":\"cd3205c7-55ca-486e-88f7-232803bf2ba4\",\"userId\":\"cd3205c7-55ca-486e-88f7-232803bf2ba4\",\"userType\":\"ActiveDirectory\"},\"flowTriggerUri\":\"https://europe-001.azure-apim.net:443/apim/logicflows/c43187c3-3693-4ca9-844f-08dfefd8f09d/triggers/manual/run?api-version=2016-06-01\",\"installationStatus\":\"Broken\",\"provisioningMethod\":\"FromDefinition\",\"flowFailureAlertSubscribed\":true,\"referencedResources\":[]}}"
    }
]

So the formatting kicks in but seems to get lost in the JSON. So do I.  🙂

Do you think the output would be different if it was returned by http response?

I hope not, but I can try.

[Pstork1]

As I said, I recommend using the HTTP respond to request action instead of the respond to Power Apps.  That action lets you actually load the JSON schema using a sample output. I don't know what else to recommend.  All I can do is tell you what does work.  If you want to keep trying it a different way then I don't know how to help you.

[MichelH]

I'm trying to this http response action as we speak.

Not used it before, so I guess I need to add something like this to the body or headers:

body('Get_Flow')

Now I think I need to connection between powerapps and flow again.

Power apps doesn't like the .res variable in which I try to return the json

Now my issue is that the .Run() just returns true, which is correctly formatted by the way.

[
    {
        "Value": true
    }
]

I guess I don't yet know how to return information using the 'http response action'

Nope that is just fine, thanks to @MR. Dang fine examples

Flow is definitely returning the whole body. 

So my issue is on the power apps side, where I just don't know how to get that.

[Pstork1]

If you changed the action that does the return you'll need to remove the flow and re-add it in Power Apps to get it to pick up the change.  Also, make sure you use a sample payload that you've gotten previously to load into the Response to generate the schema.

[MichelH]

Hi @Pstork1 ,

You nailed it. it works

But I suddenly realize why I can't use this.

The schema returned is different for each flow.

[
    {
        "id": "/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c/flows/c43187c3-3693-4ac9-84f4-08dfefd8f09d",
        "name": "c43187c3-3693-4ac9-844f-08dfefd8f09d",
        "properties": {
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_logicflows",
            "connectionReferences": [
                {
                    "connectionName": "0cc11648080d441eda1904e034b305bb",
                    "displayName": "SQL Server",
                    "id": "/providers/Microsoft.PowerApps/apis/shared_sql"
                }
            ],
            "createdTime": "2018-10-24T07:27:34.7336734Z",
            "creator": {
                "objectId": "cd3205c7-55ca-486e-88f7-232803bf2ba4",
                "tenantId": "e3cf3c98-a987-465f-8254-9d541eeea73c",
                "userId": "cd3205c7-55ca-486e-88f7-232803bf2ba4",
                "userType": "ActiveDirectory"
            },
            "definition": {
                "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
                "actions": {
                    "Get_row": {
                        "inputs": {
                            "authentication": {
                                "type": "Raw",
                                "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
                            },
                            "host": {
                                "connection": {
                                    "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']"
                                }
                            },
                            "method": "get",
                            "path": "/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MH_SAPdata_updates]'))}/items/@{encodeURIComponent(encodeURIComponent('121029'))}"
                        },
                        "metadata": {
                            "flowSystemMetadata": {
                                "swaggerOperationId": "GetItem"
                            }
                        },
                        "runAfter": {},
                        "type": "ApiConnection"
                    },
                    "Initialize_variable": {

 Bummer,...

[MichelH]

@Pstork1 

Or could there be a way to make 'http response action' just return everything?

A wildcard schema?

[Pstork1]

That is indeed a problem.  If the schema is similar, but some fields aren't returned sometimes because they are null, then there are ways to hand code the schema to allow for that.  But if the schemas are completely different then it just won't work.