BigQuery: select * replace from multiple nested column

Question

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;

Mikhail Berlyant · Accepted Answer · 2018-03-19 18:36:13Z

7

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

edited Mar 19, 2018 at 18:36

answered Mar 19, 2018 at 18:15

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lior Over a year ago

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).

Mikhail Berlyant Over a year ago

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!

Lior Over a year ago

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.

Mikhail Berlyant Over a year ago

: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)

Collectives™ on Stack Overflow

BigQuery: select * replace from multiple nested column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related