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
- Discover the Exquisite Beauty of Lab Diamond Necklaces
- Crafting the Perfect Custom Engagement Ring: A Guide to Creating Your Dream Ring
- The Security Shield: Safeguarding Data and Applications in Low-Code Environments
- The Importance Of Net Linking For SEO
- How To Use A Honeypot To Protect Corporate Data?
- 7 Ways To Watch Cyfrowy Polsat GO Abroad
- 5 Common Credit Card Scams and How to Avoid Them
- How to Check if POS Systems are Running Smoothly?
- 9004/HB1 LED Headlights Bulbs: A New Year Present by SuncntAuto
- 3 Enterprise Benefits of Having a Cloud Data Warehouse
- 5 Reasons to Switch to Wireless Charging
- The Shoprider Scootie 4-Wheel Mobility Scooter
- WordPress Image Compression Doesn’t Have To Be Hard. Read These 7 Tips
- How To Get A Fabulous Image Gallery Website On a Budget
- Things You Need When You Are In Oil And Gas Industry
Recent Posts
- Discover the Exquisite Beauty of Lab Diamond Necklaces
- Crafting the Perfect Custom Engagement Ring: A Guide to Creating Your Dream Ring
- The Security Shield: Safeguarding Data and Applications in Low-Code Environments
- The Importance Of Net Linking For SEO
- How To Use A Honeypot To Protect Corporate Data?
- 7 Ways To Watch Cyfrowy Polsat GO Abroad
- 5 Common Credit Card Scams and How to Avoid Them
- How to Check if POS Systems are Running Smoothly?
- 9004/HB1 LED Headlights Bulbs: A New Year Present by SuncntAuto
- 3 Enterprise Benefits of Having a Cloud Data Warehouse
- 5 Reasons to Switch to Wireless Charging
- The Shoprider Scootie 4-Wheel Mobility Scooter
- WordPress Image Compression Doesn’t Have To Be Hard. Read These 7 Tips
- How To Get A Fabulous Image Gallery Website On a Budget
- Things You Need When You Are In Oil And Gas Industry
More To View
- Discover the Exquisite Beauty of Lab Diamond Necklaces
- Crafting the Perfect Custom Engagement Ring: A Guide to Creating Your Dream Ring
- The Security Shield: Safeguarding Data and Applications in Low-Code Environments
- The Importance Of Net Linking For SEO
- How To Use A Honeypot To Protect Corporate Data?
- 7 Ways To Watch Cyfrowy Polsat GO Abroad
- 5 Common Credit Card Scams and How to Avoid Them
- How to Check if POS Systems are Running Smoothly?
- 9004/HB1 LED Headlights Bulbs: A New Year Present by SuncntAuto
- 3 Enterprise Benefits of Having a Cloud Data Warehouse
- 5 Reasons to Switch to Wireless Charging
- The Shoprider Scootie 4-Wheel Mobility Scooter
- WordPress Image Compression Doesn’t Have To Be Hard. Read These 7 Tips
- How To Get A Fabulous Image Gallery Website On a Budget
- Things You Need When You Are In Oil And Gas Industry
Popular Posts
- Discover the Exquisite Beauty of Lab Diamond Necklaces
- Crafting the Perfect Custom Engagement Ring: A Guide to Creating Your Dream Ring
- The Security Shield: Safeguarding Data and Applications in Low-Code Environments
- The Importance Of Net Linking For SEO
- How To Use A Honeypot To Protect Corporate Data?
- 7 Ways To Watch Cyfrowy Polsat GO Abroad
- 5 Common Credit Card Scams and How to Avoid Them
- How to Check if POS Systems are Running Smoothly?
- 9004/HB1 LED Headlights Bulbs: A New Year Present by SuncntAuto
- 3 Enterprise Benefits of Having a Cloud Data Warehouse