cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
parv
Level: Powered On

JSON File - For Each

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: JSON File - For Each

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)))

 

3.JPG

  • 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:4.JPG

 

5.JPG

 

6.JPG

 

7.JPG

The flow works successfully as below:8.JPG

 

9.JPG

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
Super User
Super User

Re: JSON File - For Each

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

 


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


parv
Level: Powered On

Re: JSON File - For Each

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

Super User
Super User

Re: JSON File - For Each

You're welcome @parv


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


parv
Level: Powered On

Re: JSON File - For Each

@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.

Super User
Super User

Re: JSON File - For Each

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


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


parv
Level: Powered On

Re: JSON File - For Each

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

Super User
Super User

Re: JSON File - For Each

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

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

Thanks


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


parv
Level: Powered On

Re: JSON File - For Each

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 

Community Support Team
Community Support Team

Re: JSON File - For Each

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)))

 

3.JPG

  • 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:4.JPG

 

5.JPG

 

6.JPG

 

7.JPG

The flow works successfully as below:8.JPG

 

9.JPG

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

parv
Level: Powered On

Re: JSON File - For Each

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

parv
Level: Powered On

Re: JSON File - For Each

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

 

compose 2.PNG

 

 

and here is the error:

 

compose 2 error.PNG

Super User
Super User

Re: JSON File - For Each

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


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


parv
Level: Powered On

Re: JSON File - For Each

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
Level: Power Up

Re: JSON File - For Each

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
Level: Powered On

Re: JSON File - For Each

@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

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,208)