Скачать или смотреть How to Extract JSON Keys in BigQuery

Discover how to effectively use BigQuery to extract key names from JSON strings and create new columns using SQL queries.
---
This video is based on the question https://stackoverflow.com/q/72319212/ asked by the user 'SherWind' ( https://stackoverflow.com/u/19161314/ ) and on the answer https://stackoverflow.com/a/72322945/ provided by the user 'Kolban' ( https://stackoverflow.com/u/1738915/ ) 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: Bigquery Get json key name

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.
---
How to Extract JSON Keys in BigQuery: A Simple Guide

Working with JSON data in BigQuery can sometimes be a challenge, especially when you need to identify and extract specific key names. If you've ever found yourself in a situation where you have a table containing JSON strings and need to return certain keys like "corp," "person," or "sme," this post is for you! Here, we'll walk through the problem and provide an effective SQL solution to help you achieve your goal.

The Problem

Let's say you have a BigQuery table with a column that contains JSON strings. You want to check each JSON object to find out if it includes any of the keys: "corp," "person," or "sme." Your end goal is to create a new column called cust_type, which lists which of these keys exists for each row.

Example JSON Data

Here are two examples of the JSON strings you might encounter in your BigQuery table:

Example 1: Corp

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

Example 2: Person

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

The Solution

To extract the desired key names, we can use the JSON_EXTRACT function in BigQuery. This function will allow us to check if each of the specified keys exists in the JSON string.

SQL Query Breakdown

Here’s how you can write an SQL query to create the cust_type column based on the JSON data:

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

Explanation of the Query

CASE Statement: The CASE statement is used here to check for the presence of each key.

JSON_EXTRACT Function: This function extracts the specified path from the JSON string. If the key exists, it will not return NULL.

UNNEST Function: The UNNEST function allows us to query multiple rows of JSON data at once. Here, we are using it to simulate a dataset.

Sample Output

Running the SQL query above would yield results as follows:

examplecust_type1corp2personWith this approach, you can easily determine which key exists in each JSON object and create a new column to reflect that information!

Conclusion

Working with JSON data in BigQuery doesn’t have to be daunting. By using the JSON_EXTRACT function together with a CASE statement, you can efficiently identify and extract the keys you need. Now, you should be able to create the cust_type column in your BigQuery table with ease. Give it a try and enhance your data analysis capabilities!

If you have further questions or need assistance, feel free to reach out. Happy querying!