Like PIVOT conversion of JSON value

Original post is here.

JSON with arrays is troublesome in getting values when operating with LogicApps / Flow. Converting the values horizontally and vertically like PIVOT and converting so that related values can be acquired with specific key values may make subsequent processing easier.

For example in this case. Normally, you loop through SampleArray and check the values one by one. Since the current Logic Apps and Flow can only deal with this way, the purpose is to want to make it even easier here.

The whole diagram of LogicFlow created as a sample is as follows.

What I was using for testing is the value of Entity included in the LUIS analysis result. I used this because it is an array.

First, In the JSON analysis, only the Entity part is analyzed from the return value from LUIS. By doing so, we can specify the value from the dialog.

In that case there is one point attention. I think that it is common to populate the schema by inputting sample data, but the schema is generated with the required specification given as written in the previous entry. Therefore, we delete all required designation from the generated schema so that the value can be omitted. Of course, if it is not omitted, you can use it as it is.

Next, we are creating a dummy JSON. This is a receiver for creating a new JSON value in later processing. Appropriate values are created using JSON function.

json('{"title":""}')

I use JSON function like this. An empty JSON value can not be created, so some element is necessary. Then prepare a variable to receive the generation result. The type allows you to accept JSON values as objects.

This is the main point. Looping the value of the array in the loop, we create a new JSON based on each value.

For processing this time, simultaneous execution control is turned off by setting of ForEach loop so that it is processed sequentially one by one. This is to ensure that the results are set correctly when setting the result to the variable that becomes the receiver. When processing is done at the same time, this setting is necessary because there is a possibility that it is close to a phenomenon at the time of multithread execution such as entering only one value.

In the loop, we first obtain the value we want to key, give the value we want to obtain for that key, and generate the result as a new JSON. JSON analysis was performed first, so you can specify it from the dialog. And the most important point is the next "creation of JSON value based on Type value".

We combine functions to generate a new JSON value.

addProperty(variables('newValues'),replace(string(outputs('Typeの値を取得')),'.',''),replace(string(items('Entityの値でループ')),'.',''))

We add new keys and values using addProperty function. The first argument variavles ('newValues') is the JSON value to add. Since we want to summarize the key and value this time, we have specified variable to be the receiver. By referring to this variable, all values can be obtained.

The second argument [ replace (string (outputs ('Typeの値を取得')), '.', '') ] Specifies the key. In this case, since key values included values (.) That can not be used in JSON, they are eliminated. It is unnecessary if there is no character that can not be used in JSON in the case of using, but when referring to the result of the action with the outputs function, it is necessary to explicitly make it a character string with the string function.

The last argument [replace (string (item ('Entityの値でループ')), '.', '')] Specifies the value to be set for the new JSON to be created. We are removing characters that we can not use here either.

By doing so, we create a new JSON value with the key and value added to the JSON value set in the variable newValues. It is impossible to rewrite the original variable newValues and it can not be self-referenced, and we set the result generated here to the variable newValues with the next action. If you are a programmer, you will want to set the result of addProperty to newValues as it is, you will want to do with one action, but you need to separate it into two actions because you can not self-reference.

Processing all values in a loop, you can see that JSON value is set to the variable newValues with this feeling. By doing this, it is possible to obtain the value with the following description for the variable newValues.

variables('newValues')['Hisashi Food::Food']

It seems that it takes some labor, but by converting to horizontal and vertical like this it is easier to use the value than to loop every time in later processing.