Conversion of Array of numbers to Float/Int in JSON file imported in Cloud fusion pipeline

[iamnicks96]

Hi Community members,

We have imported a JSON with Key-value pairs (From Airtable using HTTP task and Get method) where we will be using Key as column name and values will be column values and insert that to a BQ table. Now one of the key(Semester_id) in JSON(will be column in BQ) is having a value which is an Array of numbers and it's having only a single number value in whole JSON file as shown below:

The current output of JSONParser task in cloud fusion is as below which is an array:

As per the documentation of JSON parser we can use MIN MAX function to get the min max values out of an int array and output will in in 'double' data type:

So I tried to use this min function using both standalone and with Math.min as shown below:

But after using above, no records were shown in the output of JSON parser if we see using preview:

Can anyone help us out on how we can extract min/max number from an array of number in JSON Value and give a values in 'double' format that we can pass to a BQ table.

[lsolatorio]

Hi @iamnicks96, 

Looking at the screenshots you have provided, one possible reason that I can think of is that your data has special characters [square brackets] mixed with the numerical values. Since you are using the MIN function, which reads and determines the lowest value across an array of numbers, it will not be able to process non-numerical data hence not returning any records in your table. You may try omitting the special characters and solely work with numerical data for mathematical functions. You can also review your logs to see if there are errors related to the failed conversion.

I hope this helps.

Usable resource:
 - JSON Parser Transformation