#StandardSQLWITH tableA AS (SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_idUNION ALLSELECT ["T008", "T009"] AS T_id, NULL AS L_id)SELECT * FROM tableA, UNNEST(L_id) AS unnestWhen I executed this code, I expected the result such as that below.
RowNumber T-id L-id unnest1 T001,T002,T003 1,5 12 T001,T002,T003 1,5 53 T004,T005 NULL NULLBut I get this result instead:
RowNumber T-id L-id unnest1 T001,T002,T003 1,5 12 T001,T002,T003 1,5 5I lost the third row.Then, I saw the official Google documentation, which states the following:
UNNEST treats NULL as follows.・NULL and empty ARRAY generate zero rows.・An ARRAY containing NULL generates a row containing a NULL value.But I don't want to lose my null row.
How can I keep the null row?
Please tell me the solution...
Best Answer
Instead of CROSS JOIN, use LEFT JOIN. This will return a row with nulls for an empty array. You may also be interested in the working with arrays topic from the documentation.
#StandardSQLWITH tableA AS (SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_idUNION ALLSELECT ["T008", "T009"] AS T_id, NULL AS L_id)SELECT * FROM tableALEFT JOIN UNNEST(L_id) AS value;