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

Question

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

This is what i want to be.

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)

Sergey Geron · Accepted Answer · 2021-02-17 04:03:10Z

1

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

edited Feb 17, 2021 at 4:03

answered Feb 16, 2021 at 13:30

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

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

5 Comments

hanjong Over a year ago

It did not work. Because columns (event_params.values.xxx) is STRUCT<STRUCT>. THE 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, ...])". I think the answer query is more complicated. Thx!

Sergey Geron Over a year ago

Sorry, updated. If there will be any error messages, please post them.

hanjong Over a year ago

It works, You are my hero! Geron. Can i ask on more question? I updated, Please note my question 2. Thx a lot!

Sergey Geron Over a year ago

Updating elements in an array is not so convenient because it is not a usual use-case for the BigQuery. If you need to do lots of updates consider using BigTable instead. But maybe somebody knows more beautiful approach than unnest-nest. Try asking a new question (few will take a look at an already answered question).

hanjong Over a year ago

Got it, Thx for your help! Have a nice day :)

Collectives™ on Stack Overflow

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

Problem 1

Problem 2 (Not Solved)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem 1

Problem 2 (Not Solved)

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related