Does BigQuery support NaN inside Json?

Question

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?

score 2 · Accepted Answer · answered Jan 11 '22 at 03:10

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.

Yeh, the surprise to me was that the parser then proceeded to trash the rest of the line after the NaN, as opposed to just setting sensor2 to "null" and leaving poor Sensor3 alone. — Tunneller, Jan 11 '22 at 05:45

score 0 · Answer 2 · edited Jan 17 '22 at 13:51

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.

2.95 seconds for 120,000 rows with 6 sensors per row using JSON_EXTRACT vs 3.05 seconds using the Javascipt UDF..... I was expecting the latter to be much slower actually, especially as JSON_EXTRACT doesnt work for me ;-) — Tunneller, Jan 15 '22 at 23:58

Does BigQuery support NaN inside Json?

2 Answers2