How do I use BigQuery DML to transform some fields of a struct nested within an array, within a struct, within an array?
On December 11, 2021 by Anne WilliamsI think this is a more complex version of the question in Update values in struct arrays in BigQuery.
I’m trying to update some of the fields in a struct, where the struct is heavily nested. I’m having trouble creating the SQL to do it. Here’s my table schema:
CREATE TABLE `my_dataset.test_data_for_so`
(
date DATE,
hits ARRAY<STRUCT<search STRUCT<query STRING, other_column STRING>, metadata ARRAY<STRUCT<key STRING, value STRING>>>>
);
This is what the schema looks like in the BigQuery GUI after I create the table:
Here’s the data I’ve inserted:
INSERT INTO `my_dataset.test_data_for_so` (date, hits)
VALUES (
CAST('2021-01-01' AS date),
[
STRUCT(
STRUCT<query STRING, other_column STRING>('foo bar', 'foo bar'),
[
STRUCT<key STRING, value STRING>('foo bar', 'foo bar')
]
)
]
)
My goal is to transform the “search.query” and “metadata.value” fields. For example, uppercasing them, leaving every other column (and every other struct field) in the row unchanged.
I’m looking for a solution involving either manually specifying each column in the SQL, or preferably, one where I can only mention the columns/fields I want to transform in the SQL, omitting all other columns/fields. This is a minimal example. The table I’m working on in production has hundreds of columns and fields.
For example, that row, when transformed this way, would change from:
[
{
"date": "2021-01-01",
"hits": [
{
"search": {
"query": "foo bar",
"other_column": "foo bar"
},
"metadata": [
{
"key": "foo bar",
"value": "foo bar"
}
]
}
]
}
]
to:
[
{
"date": "2021-01-01",
"hits": [
{
"search": {
"query": "FOO BAR",
"other_column": "foo bar"
},
"metadata": [
{
"key": "foo bar",
"value": "FOO BAR"
}
]
}
]
}
]
About Annie
Latest Posts
Lab Diamonds Cut Grading: A Comprehensive Guide
Laboratory Diamonds: A Modern and Sustainable Choice for Fine Jewelry
Diamonds: A Revolution in the Jewelry Industry
Lab Diamonds on a Budget: How to Get the Best Value Without Compromising Quality
Novita Diamonds: The Story Behind a Revolution in Fine Jewelry
Why Lab Diamond Engagement Rings Are the Future of Modern Weddings
The Beauty and Value of Pink Lab Diamonds: A Modern Marvel
The Rise of Novita Diamonds: A Women-Led Revolution in the Jewelry Industry
Lab Grown Diamond Stud Earrings: The Modern Choice for Sustainable Luxury
The Beauty and Rarity of Pink Lab Diamonds
Blood Diamonds: Understanding Their Impact and Ethical Implications
Shine Bright With Your Diamonds
Contact Novita Lab Diamonds: Your Guide to High-Quality Lab-Grown Diamonds
The Guide to Buy and Sell Bitcoin in Australia
Diamantschliff: The Art and Science Behind Brilliant Cuts
Recent Posts
Lab Diamonds Cut Grading: A Comprehensive Guide
Laboratory Diamonds: A Modern and Sustainable Choice for Fine Jewelry
Diamonds: A Revolution in the Jewelry Industry
Lab Diamonds on a Budget: How to Get the Best Value Without Compromising Quality
Novita Diamonds: The Story Behind a Revolution in Fine Jewelry
Why Lab Diamond Engagement Rings Are the Future of Modern Weddings
The Beauty and Value of Pink Lab Diamonds: A Modern Marvel
The Rise of Novita Diamonds: A Women-Led Revolution in the Jewelry Industry
Lab Grown Diamond Stud Earrings: The Modern Choice for Sustainable Luxury
The Beauty and Rarity of Pink Lab Diamonds
Blood Diamonds: Understanding Their Impact and Ethical Implications
Shine Bright With Your Diamonds
Contact Novita Lab Diamonds: Your Guide to High-Quality Lab-Grown Diamonds
The Guide to Buy and Sell Bitcoin in Australia
Diamantschliff: The Art and Science Behind Brilliant Cuts
More To View
Lab Diamonds Cut Grading: A Comprehensive Guide
Laboratory Diamonds: A Modern and Sustainable Choice for Fine Jewelry
Diamonds: A Revolution in the Jewelry Industry
Lab Diamonds on a Budget: How to Get the Best Value Without Compromising Quality
Novita Diamonds: The Story Behind a Revolution in Fine Jewelry
Why Lab Diamond Engagement Rings Are the Future of Modern Weddings
The Beauty and Value of Pink Lab Diamonds: A Modern Marvel
The Rise of Novita Diamonds: A Women-Led Revolution in the Jewelry Industry
Lab Grown Diamond Stud Earrings: The Modern Choice for Sustainable Luxury
The Beauty and Rarity of Pink Lab Diamonds
Blood Diamonds: Understanding Their Impact and Ethical Implications
Shine Bright With Your Diamonds
Contact Novita Lab Diamonds: Your Guide to High-Quality Lab-Grown Diamonds
The Guide to Buy and Sell Bitcoin in Australia
Diamantschliff: The Art and Science Behind Brilliant Cuts
Popular Posts
Lab Diamonds Cut Grading: A Comprehensive Guide
Laboratory Diamonds: A Modern and Sustainable Choice for Fine Jewelry
Diamonds: A Revolution in the Jewelry Industry
Lab Diamonds on a Budget: How to Get the Best Value Without Compromising Quality
Novita Diamonds: The Story Behind a Revolution in Fine Jewelry
Why Lab Diamond Engagement Rings Are the Future of Modern Weddings
The Beauty and Value of Pink Lab Diamonds: A Modern Marvel
The Rise of Novita Diamonds: A Women-Led Revolution in the Jewelry Industry
Lab Grown Diamond Stud Earrings: The Modern Choice for Sustainable Luxury
The Beauty and Rarity of Pink Lab Diamonds