Is it possible to disable or completely ignore the null values in BigQuery that come from firebase events of a (mobile) app?
Example:
key => my_custom_firebase_event:
value => {"string_value": null, "int_value": "123456789", "doube_value":null}
Preferred:
value => {"int_value": "123456789"}
You can use the functions UNNEST to save into an array and OFFSET to get each value from the array.
You can see this query with the same example of your post.
WITH sample AS (
SELECT JSON_QUERY('{"raw_result":{"string_value": null, "int_value": "123456789", "doube_value":null}}', '$.raw_result') json
),
sample2 as (
SELECT concat(K ,v) as int_value
FROM sample as t,
UNNEST(bqutil.fn.json_extract_keys(json)) k WITH OFFSET ki,
UNNEST(bqutil.fn.json_extract_values(json)) v WITH OFFSET vi
WHERE ki = vi and k='int_value'
)
select TO_JSON_STRING(t) from sample2 as t
This was the output.
