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
Solved! Go to 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:
variables('OriginalJSON')?['quotes']
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.
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)))
item()?['Code'
Number field set to following formula:
item()?['Number']
Image reference:
The flow works successfully as below:
Best regards,
Kris
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.
json(base64ToString(body('Get_file_content')?['$content']))
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
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
You're welcome @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.
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
Yes I want to loop thru each item of the quotes, name "quotes" will always remain the same. Thanks for quick reply.
Another question: Do each quote need its own separate output? For example:
Thanks
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
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:
variables('OriginalJSON')?['quotes']
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.
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)))
item()?['Code'
Number field set to following formula:
item()?['Number']
Image reference:
The flow works successfully as below:
Best regards,
Kris
Hi there I'm getting error on "Compose 2"
and here is the error:
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
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
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.
@parv Please mark this as an answer...
My friend, you could make a community post with this solution.
Some suggested suitable titles:
Emmanuel
Check out new user group experience and if you are a leader please create your group
See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.
User | Count |
---|---|
44 | |
43 | |
43 | |
42 | |
33 |
User | Count |
---|---|
85 | |
80 | |
62 | |
50 | |
45 |