-
It appears that you’re dealing with a complex nested data structure in SQL, which can get quite tricky to handle. The issue you’re facing seems to revolve around the CASE WHEN statement in conjunction with SAFE_OFFSET. SAFE_OFFSET is normally used to extract a value from an array without running into out-of-bound errors. However, in your case, the product.v2ProductName doesn’t require unnesting or offsetting when the type is ‘EVENT’.
Since ‘type’ and ‘product’ don’t need to be unnested, your CASE statement might be failing because it’s trying to SAFE_OFFSET on a non-array value or NULL.
Alternatively, the issue might be stemming from how SQL is handling the nesting of your hits.product.v2ProductName field. If the structure is not consistent across all your data (specifically, if ‘EVENT’ type does not always have a non-repetitive array for this field), it could cause errors.
Try revisiting how you’re handling your nested product data, particularly when the type is ‘EVENT’. Assure that your SQL query is able to handle these conditions correctly. If the problem persist, you may need to change the structure of your data for better consistency, or alter your query to handle this inconsistency.
