Big query remove column nested in STRUCT object

2
votes

I am encountering some troubles to delete one column from my bigquery table. I have a STRUCT object called jsonPayload. This fields has may sub-fields. e.g:

jsonPayload.source
jsonPayload.status
jsonPayload.markers.message_timestamp
jsonPayload.markers.id

and I want to remove the column jsonPayload.markers.message_timestamp which is a string without removing any others fields.

It is quite the same situation of this thread BigQuery select expect double nested column but when I execute this, it removes all the jsonPayload.* like jsonPayload.source

I can't find the right syntax to do this. Someone can help me ?

Edit:

Here is the data structure of my bigquery table:

Field name Type Mode
jsonPayload RECORD NULLABLE
jsonPayload.source STRING NULLABLE
jsonPayload.msg STRING NULLABLE
jsonPayload.level STRING NULLABLE
jsonPayload.err STRING NULLABLE
jsonPayload.timestamp STRING NULLABLE
jsonPayload.markers.message_partition FLOAT NULLABLE
jsonPayload.markers.message_topic STRING NULLABLE
jsonPayload.markers.message_offset FLOAT NULLABLE
jsonPayload.markers.message_timestamp STRING NULLABLE

Just want to remove the last column(jsonPayload.markers.message_timestamp) and keep all the others.

I've tried this one but it seems to delete also all fields that are not markers (jsonPayload.source,...):

SELECT * REPLACE(
  (SELECT AS STRUCT(SELECT AS STRUCT jsonPayload.markers.* EXCEPT (message_timestamp)))
  AS jsonPayload)
FROM mytable.dataset

Thanks

1
google-bigquery
not clear - is it STRUCT or JSON - provide example of data and expected output so we can help – Mikhail Berlyant
Thanks Mikhail. I've added an extract of the data structure to better explain the issue I'm facing of. – JPape

1 Answers

1
votes

Below is for BigQuery Standard SQL

select * replace(
  (select as struct jsonPayload.* replace(
    (select as struct jsonPayload.markers.* except(message_timestamp))
    as markers
  )
  ) AS jsonPayload) 
from `project.dataset.table`