Parse JSON Issue

(0) Share

Report

Posted on by CW-27040836-0

Hi Guys,

Banging my head against a brick wall here.

I have some JSON that i am parsing from a HTTP request. Done this many times with no issue. I have a specific return of type number that i am struggling to do anything with. Parse JSON seems to work, but then using the variable anywhere just passes a null. I have tried converting and formatting as number (type n4). But i just keep getting the input passed in as null and therefore an error. Any help much appreciated.

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (1)

Sort by

Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Could you provide an example of what you mean about "using the variable"? The number is a floating point number. If you use it like a string or an integer you will get a null.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Suggested answer

venturemavenwill 1,198 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Could you check the schema in SQL to see what kind of data type the column is expecting?

A type mismatch could also cause a null value during insertion.

Was this reply helpful? Yes No
CW-27040836-0 10 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the advice.

The SQL type is also a float.

What is confusing me though is that the input to the SQL is coming through as null. If it was a type mismatch would it not come through as the number and then reject it during the processing ?

Below is the input to the SQL write.

I have also tried saving it as various types of variable in automate, it always comes back as null being passed and throws an error.

Was this reply helpful? Yes No
Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Can you show a screenshot of how you are passing the values from the Parse JSON into SQL? I suspect you need a specific data type cast at that spot to make sure the number is what SQL is expecting.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Suggested answer

venturemavenwill 1,198 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
A bit of a brute force method, but try to manually define everything to see if you are able to retrieve a value. In your case you would insert the following formula into the field

float(body('Parse_JSON')?['body']?['result']?['score'])

This forces the flow to look at exactly the place defined in your JSON, and cast it as a float.

Was this reply helpful? Yes No
CW-27040836-0 10 on at

Like (0)

Report
Copy link

Link copied!

No joy forcing it either.

I have expanded the troubleshooting and found that actually nothing is coming out of the parse JSON. Even though the output looks fine all of the variables when used are null.

As you can see in the above screenshots results.verdict = Risky and that is being picked up in the Parse JSON block. When i try and do anything with this it just returns null.

Was this reply helpful? Yes No
Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Try using a Compose instead of a Variable. Variables will do automatic casting so it depends what type Variable you pick. Compose will retain it as JSON.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Verified answer

venturemavenwill 1,198 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Can you also try

float(body('Parse_JSON')?['result']?['score'])

or even

float(body('HTTP')?['result']?['score'])

And see if either of these variables work?

Was this reply helpful? Yes No
CW-27040836-0 10 on at

Like (0)

Report
Copy link

Link copied!

That seems to be working this morning.

Thanks, I can now move on with my life !

Bizarely one of the other flows that i have using Parse JSON went down yesterday (just seen the failure reports), but this morning it is working fine. I am wondering if this was some other sort of issue that compounded the fact.

Was this reply helpful? Yes No