Скачать или смотреть Querying a Struct in BigQuery: Filtering by Multiple Key-Value Pairs with unnest()

Discover how to efficiently query structured data in BigQuery by filtering rows with specific key-value pairs. Follow our step-by-step guide to use `unnest()` effectively.
---
This video is based on the question https://stackoverflow.com/q/67534537/ asked by the user 'bry888' ( https://stackoverflow.com/u/7629879/ ) and on the answer https://stackoverflow.com/a/67534557/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to query a struct in BigQuery where two key: value pairs matter

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Problem: Filtering Structs in BigQuery

When working with structured data in BigQuery, you may encounter situations where you need to filter results based on multiple key-value pairs. In this guide, we will demonstrate how to achieve this using a practical example involving event data.

Let’s say you have event data structured as follows:

[[See Video to Reveal this Text or Code Snippet]]

From this dataset, you want to filter the rows to only show those that have both the color blue and size big. After filtering, the desired output should be:

[[See Video to Reveal this Text or Code Snippet]]

The Solution: Using unnest() to Query Structs

To achieve this filtering, we will leverage the unnest() function in BigQuery. This function allows you to expand arrays and structs into a more manageable format for querying.

Step-by-Step Guide

Here’s a breakdown of how to query the struct by filtering the required key-value pairs:

Understanding the unnest() Function:
The unnest() function takes an array, in our case, the event data, and converts it into a table format so that we can access individual key-value pairs.

Writing the SQL Query:
We will use a WHERE clause with EXISTS to ensure that both conditions are met. Here's how the SQL query would look:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query:

The SELECT t.* statement retrieves all columns from the table t.

The EXISTS conditions check if there exists an entry in the unnest(t.event) where:

The key is color and its value is blue.

The key is size and its value is big.

If both conditions are satisfied, that row will be included in the final output.

Conclusion

By following the outlined steps and using the example query, you can filter your data in BigQuery effectively based on multiple key-value pairs. Utilizing unnest() is a powerful way to handle structured data and can simplify complex queries.

Feel free to try this approach with your own datasets and adjust the key-value pairs according to your filtering requirements. If you have any further questions regarding BigQuery, don’t hesitate to reach out. Happy querying!