2

I'm trying to remove a field which is nested inside multiple records. Following this question, I tried the following (remove column d which has path a.b.d) but get an error: Unrecognized name: b at [6:68]

WITH T AS (
  SELECT * from unnest([struct(struct(struct(10 as c,'test' as d) as b,'field2' as field2) as a)])

)

select * replace ((select as struct a.* replace( (select as struct b.* except (d)) as b)) as a)  from T;
Lior
  • 1,357
  • 3
  • 21
  • 29

1 Answers1

6

from what I understood in your question - your original row is as below 

STRUCT(STRUCT(STRUCT(10 AS c,'test' AS d) AS b,'field2' AS field2) AS a) original_struct

and you need to get it to 

STRUCT(STRUCT(STRUCT(10 AS c) AS b,'field2' AS field2) AS a) original_struct

Below is for BigQuery Standard SQL for this to accomplish 

#standardSQL
WITH t AS (
  SELECT STRUCT(STRUCT(STRUCT(10 AS c,'test' AS d) AS b,'field2' AS field2) AS a) original_struct 
) 
SELECT 
  original_struct,
  (
    SELECT AS STRUCT (
      SELECT AS STRUCT (
        SELECT AS STRUCT * EXCEPT(d) 
        FROM UNNEST([original_struct.a.b]) b
      ) b, 
      original_struct.a.field2
    ) a
  ) new_struct
FROM t

original struct look as 

Row original_struct.a.b.c   original_struct.a.b.d   original_struct.a.field2    
1   10                      test                    field2

and new struct is 

Row new_struct.a.b.c    new_struct.a.field2  
1   10                  field2

So, to further apply this to your particular case - see example below 

#standardSQL
WITH t AS (
  SELECT 1 id, 2 x, 3 y, STRUCT(STRUCT(STRUCT(10 AS c,'test' AS d) AS b,'field2' AS field2) AS a) xyz, 4 v, 5 w 
) 
SELECT * REPLACE (
  (
    SELECT AS STRUCT (
      SELECT AS STRUCT (
        SELECT AS STRUCT * EXCEPT(d) 
        FROM UNNEST([xyz.a.b]) b
      ) b, 
      xyz.a.field2
    ) a
  ) AS xyz)
FROM t
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Is there a way to use replace * as in my original query? The original schema I'm using is huge, and I don't want to rebuild it via a query, but rather use * (and replace). – Lior Mar 19 '18 at 19:31
  • Did you see example on the very bottom of my answer? It does exactly this! You don't need to enlist all fields in your schema here - but you need to reflect your nested structure! – Mikhail Berlyant Mar 19 '18 at 19:35
  • You mentioned field xyz.a.field2 by name. What happens if there where hundred of fields like this (xyz.a.field1 through xyz.a.field100) ? Using * would make things much easier. – Lior Mar 19 '18 at 19:49
  • :o) i answered specific question! with what I believe is the best way to handle case in that specific question! If you have new question where you have hundred of such fields that for some reason you need to nuke - just post new question and someone might be able to answer it :o) i will try too - if you post a new question. But this one - I consider is answered (I mean by me - even if you expected something else) – Mikhail Berlyant Mar 19 '18 at 19:52