How can I force cast JSON-number values to float?

Nikolaos Delis 5

Exactly what the image shows. I have numbers that not always have decimal numbers and I need them to be float. As the image shows, this doesn't work today. What am I doing wrong? User's image

PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-08-25T08:44:49.5066667+00:00

@Nikolaos Delis - Thanks for the question and using MS Q&A platform.

Looks like you are trying to cast number directly and the reason the result as excepted as shown above.

Converts an expression of one data type to another within the supported data types in Stream Analytics Query Language.

If the conversion cannot be performed, the function fails and causes the job to stop. For example, the clause CAST ('this is a string' AS bigint) results in a job failure since the input string cannot be converted into the bigint data type. To avoid type cast failures, use TRY_CAST function instead.

For more details, refer to CAST (Azure Stream Analytics) and Azure Stream Analytics - SQL to convert string to float.

I hope this helps.
Nikolaos Delis 5 Reputation points

2023-08-26T07:17:12.9733333+00:00

@PRADEEPCHEEKATLA-MSFT thanks for your response.

I am afraid this is not the case though. My conversion does not fail, I do get a result, however not of the type I asked for. First of all TRY_CAST gives exactly the same result, bigint.

Additionally, I am not trying to convert text, but valid numbers. Don't you think that CAST('0' as float) and CAST('0.0' as float) should give me a float?

The Stackoverflow-article you refer to suggests I do exactly as I already do.
PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-08-31T05:12:54.5166667+00:00

@Nikolaos Delis - This is because how the json serializer serializes integral values. JSON definition does not have a distinction between 0 and 0.0. Inside the query however we will treat result of each one of these casts as float. Also if the output format is specified as say Avro, which does carry type information then the float type will propagate to the Avro result.
Nikolaos Delis 5 Reputation points

2023-08-31T06:39:59.2466667+00:00

@PRADEEPCHEEKATLA-MSFT thanks for your response.

In the example I have posted, no JSON is included, I explicitly wrote '0' and '0.0' so there is nothing to deserialize. Nevertheless, my issue is not with JSON. The issue is that although I use CAST(X as float) (or TRY_CAST) I don't get a float, but a bigint. Isn't this a bug?
PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-09-04T07:01:39.5033333+00:00

@Nikolaos Delis - We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Nikolaos Delis 5 Reputation points

2023-09-04T09:09:00.6633333+00:00

Thanks a lot @PRADEEPCHEEKATLA-MSFT
PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-09-07T03:56:37.79+00:00

@Nikolaos Delis - As per the above post, I didn't mention deserializer in my reply, I said SERIALIZER. The result of the query is serialized into JSON and when the JSON and this is where the type is lost, because it is irrelevant for JSON format. I.e. 0 of type BIGINT and 0 of type FLOAT are both look like 0 in JSON output.

Then portal just interprets this value as it sees it in resulting JSON.

And, as I mentioned, if the output format would be something that preserves types, like say AVRO, then the type set in the query would propagate to the output format as well.
Nikolaos Delis 5 Reputation points

2023-09-07T11:53:17.8566667+00:00

@PRADEEPCHEEKATLA-MSFT Ok, now I understand. This explains why it appears as Bigint on the table.

The output format, i.e. the data we wish to send to Postgres, should preserve their types. So when I write "CAST as Float" I expect a float value to be inserted in the Postgres table, which unfortunately does not happen. I have to make the Postgres-column to double in order to get it to work. Any comments on that?
PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-09-13T10:25:34.09+00:00

@Nikolaos Delis - yes, that sounds correct. If your output is Postgres then the schema of the Postgres table will define the type of data as long as it is convertible to it, which you sort of ensure by adding CAST on ASA side. So, basically inserting "0" into Postgress column of type FLOAT will work as expected and the data will be 0.0.

I hope this helps.
Nikolaos Delis 5 Reputation points

2023-09-13T12:51:43.9533333+00:00

@PRADEEPCHEEKATLA-MSFT what you write in your last sentence is essentially the error I am reporting. If I try to output 0 in a Postgres-column of type float, then I get a conversion error. If I try to output 0 in a Postgres-column of type double, then it works.
PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-09-18T08:23:04.5066667+00:00

@Nikolaos Delis - Glad to your issue has been resolved by using the data type as double.
Nikolaos Delis 5 Reputation points

2023-09-18T08:44:59.06+00:00

@PRADEEPCHEEKATLA-MSFT the issue is not resolved, I just found a workaround. Are we going to close it here though?
PRADEEPCHEEKATLA-MSFT 78,576 Reputation points Microsoft Employee

2023-09-18T09:15:47.0266667+00:00

@Nikolaos Delis - I will not close this thread. The information that you have shared I will be share with the internal team and getting back to you to give you a possible solution.