CODEWITHSUNDEEP
google-bigquerynanjson-extract
Tunneller
Tunneller

Reputation: 599

Does BigQuery support NaN inside Json?

Consider BigQuery Schema "Time=Timestamp, Data=String" and the following two rows

2012-11-01 03:45:23 UTC, { "Sensor1": [45.56], "Sensor2": [45.56], "Sensor3": [34.45]}
2012-11-01 05:45:23 UTC, { "Sensor1": [11.43], "Sensor2": [NaN], "Sensor3": [12.34]}

Then SELECT Time, JSON_EXTRACT(Data, '$.Sensor1') is perfect

2012-11-01 03:45:23 UTC,  [45.56]
2012-11-01 05:45:23 UTC,  [11.43]

but SELECT Time, JSON_EXTRACT(Data, '$.Sensor3') gives

2012-11-01 03:45:23 UTC,  [34.45]
2012-11-01 05:45:23 UTC,  null

Is that expected behaviour? Would I be better off just omitting that NaN from Sensor 2?

Upvotes: 0

Views: 1096

Answers (2)

Tunneller
Tunneller

Reputation: 599

So for my application I always have { "nameA": [array...], "nameB": [array...], ...} where array is the usual range of floating point numbers, i.e. including some NaN's. As noted above, JSON_EXTRACT doesnt really work. But this javascript UDF does the job just fine.

CREATE TEMP FUNCTION EXTRACT_DATA(mystr STRING, param STRING)
RETURNS Array < FLOAT64 > LANGUAGE js AS
"""
   const i0 = mystr.indexOf(param);
    if (i0 == 0) {
        return null;
    }
    const i1 = mystr.substring(i0).indexOf("[");
    if (i1 == 0) {
        return null;
    }
    const i2 = mystr.substring(i0+i1).indexOf("]");
    if (i2 == 0) {
        return null;
    }
    const myArray = mystr.substr(i0+i1+1,i2-1).split(',')
    return myArray.map(Number);
     }
""" ;

I don't know how expensive ($'s and cpu_time) would this be compared to JSON_EXTRACT on arrays that didn't have NaN.

Upvotes: 0

Ricco D
Ricco D

Reputation: 7287

This is an expected behavior. As per https://www.json.org/json-en.html, valid JSON values can only be string, number, true or false or null. Hence NaN is interpreted by BigQuery as null since it is considered as an invalid value.

A value can be a string in double quotes, or a number, or true or false or null, or an object or an array. These structures can be nested.

If you want retain the value NaN you can enclose it with double quotes ("NaN") but it will be considered as string. Or you can just omit the NaN as you mentioned in your question if you won't be using/needing it.

Upvotes: 2

Related Questions