cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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
Highlighted
Community Support
Community Support

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
Highlighted
Memorable Member
Memorable Member

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!
Highlighted
Regular Visitor

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

Highlighted
Memorable Member
Memorable Member

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!
Highlighted
Regular Visitor

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.

Highlighted
Memorable Member
Memorable Member

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!
Highlighted
Regular Visitor

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.

Highlighted
Memorable Member
Memorable Member

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!
Highlighted
Regular Visitor

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 

Highlighted
Community Support
Community Support

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

Highlighted
Regular Visitor

Re: JSON File - For Each

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

Highlighted
Regular Visitor

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

Highlighted
Memorable Member
Memorable Member

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!
Highlighted
Regular Visitor

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

Highlighted
New Member

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.

Highlighted
Advocate II
Advocate II

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (5,616)