JSON File - For Each

[parv]

Hi there,

I have following JSON File and would like to look thru each line under "quotes", how I can do this:

{
  "success": true,
  "terms": "terms",
  "privacy": "privacy",
  "timestamp": 1430068515,
  "source": "USD",
  "quotes": {
    "USDAUD": 1.278384,
    "USDCHF": 0.953975,
    "USDEUR": 0.919677,
    "USDGBP": 0.658443,
    "USDPLN": 3.713873
  }
}

Thanks in advance,

P

[faustocapellanj]

Hi @parv,

This is an approach I took to achieve what you are looking for, however, I will assume you have this file stored in a SharePoint library. You might have to make some adjustments though.

• For the trigger, I used the "Manually trigger a flow".
• In the next step, I used the "SharePoint - Get file content" action. In here, you set the Site Address and the File Identifier.
• In the next step, I used a "Data Operations - Compose" action to convert the file content to JSON. This is due to the content being of a different type - "application/octet-stream". For the conversion, I used the formula below:
  

  json(base64ToString(body('Get_file_content')?['$content']))

• The formula above first converts the content from the action outputs to base64ToString, and once this conversion happens, then the output is converted to JSON.
• And for the last step, I used another "Data Operations - Compose" action. In this step, I retrieve "quotes" data, which will be available once the JSON conversion is completed. See the formula below for this:
  

  outputs('ConvertFileContentToJSON')['quotes']

Note: in the formula above, you can see the ConvertFileContentToJSON in parenthesis. This is the same "Data Operations - Compose" action; I just have the practice of renaming the steps for readability and for users to understand what they do.

Please check it out and let me know if you have any questions.

Regards,

Fausto Capellan, Jr

[parv]

Hey @faustocapellanj Thanks for the input, I will check it later today and get back to you on progress/update and/or if need more help.

Cheers mate!

parv

[faustocapellanj]

You're welcome @parv

[parv]

@faustocapellanj Still not worked, I'm able to extract quotes but I'm unable to find out how we can loop thru each quote line items and that is the ask here.

Sorry if I was not clrea in myoriginal request.

[faustocapellanj]

Hi @parv

So you want to loop through each quote and retrieve the respective value? If so, will the name of the quote vary or will it be the same?

Thanks,

Fausto Capellan, Jr

[parv]

Yes I want to loop thru each item of the quotes, name "quotes" will always remain the same. Thanks for quick reply.

[faustocapellanj]

Another question: Do each quote need its own separate output? For example: 

• "USDAUD": value
• "USDCHF": value

Thanks

[parv]

Yes sir, basically my ultimate goal is get each line under quote and insert as new row sql table, basically fromt this example five  rows will go in table:

 Code         Number

 USDAUD  1.278384
 USDCHF  0.953975
 USDEUR  0.919677
 USDGBP  0.658443
 USDPLN   3.713873 

[v-xida-msft · Accepted solution]

Hi @parv,

Do you want to insert each line under "quotes" into a SQL table?

I have made a test on my side and please take a try with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a Variables-> "Initialize variable" action, Name set to OriginalJSON, Type set to Object and Value set to the json file that you mentioned.
• Add a Variables-> "Initialize variable 2" action, Name set to QuotesArray, Type set to Array and Value set to empty.
• Add a "Compose" action, Inputs field set to following formula:

variables('OriginalJSON')?['quotes']

• Add a "Compose 2" action, Inputs set to following formula:

• lastIndexOf(outputs('Compose'),'}')

  Add a "Compose 3" action, Inputs field set to following formula:

• substring(outputs('Compose'),1,sub(outputs('Compose_2'),1))

  Add a "Compose 4" action, Inputs field set to following formula:

• split(outputs('Compose_3'),',')

  Add a "Apply to each" action, the "Select an output from previous steps" field set to output of "Compose 4" action.
• Within "Apply to each" action, add a "Compose 5" action, Inputs field set to following formula:

add(indexOf(concat('a',item()),'a'),2)

Add a "Compose 6" action, Inputs field set to following formula:

indexOf(concat('a',item()),':')

Add a "Append to array variable" action, Name choose QuotesArray, Value set to a json object. There are two elements within the json object, the name of first element is Code and the corresponding value set to following formula:

substring(concat('a',item()),outputs('Compose_5'),sub(sub(outputs('Compose_6'),1),outputs('Compose_5')))

The name of second element is Number and the corresponding value set to following formula:

substring(concat('a',item()),add(outputs('Compose_6'),1),sub(length(concat('a',item())),add(outputs('Compose_6'),1)))

• Under the "Apply to each" action, add a "Apply to each 2" action, input parameter set to variable QuotesArray. Within "Apply to each 2" action, add a "Insert row" action, Code field set to follwoing formula:

item()?['Code'

Number field set to following formula:

item()?['Number']

Image reference:

The flow works successfully as below:

Best regards,

Kris

[parv]

@v-xida-msftI will surely try it in next couple of hours. Stay tuned.

[parv]

Hi there I'm getting error on "Compose 2"

and here is the error:

[faustocapellanj]

Hi @parv

For that step, you will have to add a string() function to the expression in the format below:

lastIndexOf(string(outputs('Compose')),'}')

You will have to do something similar in the Compose 3 step:

substring(string(outputs('Compose')),1,sub(outputs('Compose 2'),1))

As a good practice, I always recommend to rename the Compose steps to something meaningful, that way you know what they are used for. Additionally, I'm trying to build the solution provided by @v-xida-msft, and I will add another post once I get to work.

Regards,

Fausto Capellan, Jr

[parv]

I think the issue was with "Compose" step and this what i changed it to and it worked:

Original fx expression provided by @v-xida-msft

variables('OriginalJSON')?['quotes']

fx expression fix :

string(variables('OriginalJSON')?['quotes'])

and this did it.

Thanks,

Parv

[jho]

Hi @v-xida-msft ,

your solution is awesome!
Do you have any idea how i can store the results in Collection Power Apps or in SharePoint List instead of insert row in SQL? I tried the "Response" but it didn't work.

[eopara]

@parv  Please mark this as an answer...

My friend, you could make a community post with this solution.

Some suggested suitable titles:

• convert octet stream to json microsoft flow
• read blob content microsoft flow

Emmanuel