BigQuery cannot create a view, but the query is fine

Question

I used BigQuery for analytics. I wrote this request.

[setup_chr_lvs] schema

userId is STRING,

lv - STRIMG,

chrLvs - STRING

"[1, 25, 344]" (for example)

SELECT userId,
   lv,
   INTEGER(SPLIT( REGEXP_REPLACE( REGEXP_REPLACE(chrLvs, r'\[', ''), r'\]', ''), ",")) AS chrLv
   FROM [kpi.setup_chr_lvs]

This scheme is successful. But can’t keep the view.

Failed to create view. Cannot create a valid output scheme for the field chrLV. Try renaming chrLv to f0_group.chrLvto an external SELECT.

Why?

Answer 1

-, , AS chrLv, AS f0_group.chrLv. , , , , .

SPLIT. SPLIT , . SPLIT "a,b,c" as val, - , val: string (repeated).

SPLIT, : , 3- , 3 . , : . , . ( , unflattened , : " " ( " " " " ).)

, ? , . SPLIT , INTEGER, .

, - - , . SPLIT, FLATTEN, INTEGER. :

SELECT
  userId,
  lv,
  INTEGER(chrLvStr) as chrLv
FROM FLATTEN(
  (
  SELECT 
    userId,
    lv,
    SPLIT( REGEXP_REPLACE( REGEXP_REPLACE(chrLvs, r'\[', ''), r'\]', ''), ",") AS chrLvStr
  FROM [test.setup_chr_lvs]
  ), 
  chrLvStr
)

( ), [test.setup_chr_lvs] , :

select "user1" as userId, "lv1" as lv, "[1, 24, 344]" as chrLvs

( , , FLATTEN.)

Answer 2

BigQuery, , , - chrLvs , "chrLv", .

"" , ?