How can i insert(or modify) struct to ARRAY<STRUCT<STRING, STRUCT>> in BigQuery?

1
votes

The records "event_params" is array of struct
And values are also struct totally event_params structure type is

ARRAY<STRUCT<STRING, STRUCT(string_value, int_value, float_value, double_value)>

I want to add struct(or modify) to ARRAY<STRUCT<STRING, STRUCT>> in BigQuery Table.

Problem 1

This is a table to solve the problem

enter image description here

This is what i want to be.

enter image description here

These queries didn't work

ARRAY_CONCAT(event_params, [(new_data_key, new_input_data_value, null, null, nul)]

or

ARRAY_CONCAT(event_params, [(new_data_key, (new_input_data_value, null, null, nul))]

And Error message is

No matching signature for function ARRAY_CONCAT for argument types: ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>>, ARRAY<STRUCT<STRING, STRING, INT64, ...>>. Supported signature: ARRAY_CONCAT(ARRAY, [ARRAY, ...])

Should anyone tell me Answer Query??

Thank you in advance for your reply.

Problem 2 (Not Solved)

Is it possible change data in ARRAY<STRING, STRUCT>?

ex) [Based on Figure 1 of the original article]
change event_params.key & event_params.value.int_value

# value.string_value
k6(original) -> k7 (what i want just change k6 -> k6-1)

# value.int_value
1(original) -> 3 (what i want is original * 3)

I handled this problem using UNNEST rows and then NEST them again. (I think it is very long and not good answer)

1
google-bigquery

1 Answers

0
votes

You'll have to update the entire array field. Use ARRAY_CONCAT:

update my_dataset.my_table
set event_params = ARRAY_CONCAT(
  event_params,
  [(new_data_key, (new_input_data_value, null, null, nul))]
)

Explicitly specifying type can help:

update my_dataset.my_table
set event_params = ARRAY_CONCAT(
  event_params,
  ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64>>>[(new_data_key, (new_input_data_value, null, null, nul))]
)