CODEWITHSUNDEEP
google-bigquery
highlytrainedbadger
highlytrainedbadger

Reputation: 146

How to update a nested record to a static value

I have nested data and want to update a value of a key of an array which is actually in array format user_properties['first_open_time']['int_value']. I want to change this value to a static value -1582243200000 ie.-. Sample structure of the data is as below:

    [
      {
        "user_properties": [
          {
            "key": "ga_session_id",
            "value": {
              "string_value": null,
              "int_value": "1582306435",
              "float_value": null,
              "double_value": null,
              "set_timestamp_micros": "1582306435527000"
            }
          },
          {
            "key": "ga_session_number",
            "value": {
              "string_value": null,
              "int_value": "1",
              "float_value": null,
              "double_value": null,
              "set_timestamp_micros": "1582306435527000"
            }
          },
          {
            "key": "first_open_time",
            "value": {
              "string_value": null,
              "int_value": "1582308000000",
              "float_value": null,
              "double_value": null,
              "set_timestamp_micros": "1582306432489000"
            }
          }
        ]
      }
    ]

Upvotes: 0

Views: 148

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59355

You basically need to reconstruct the struct, changing what you want to change in the process:

-- CREATE TABLE `temp.firebase_sample`
-- AS (
-- SELECT * FROM `bingo-blast-174dd.analytics_151321511.events_20200225`
-- );

UPDATE `temp.firebase_sample` a
SET user_properties = (
  SELECT
    ARRAY_AGG(STRUCT(key,
        STRUCT(value.string_value,
          IF(key='first_open_time', 1582243200000, value.int_value),
          value.float_value,
          value.double_value,
          value.set_timestamp_micros)))
  FROM UNNEST(a.user_properties) x)
WHERE TRUE

Upvotes: 1

Related Questions