Skip to content

json_keys

json_keys(json_string)

Description

Extract keys from json_string which has only flat (no nested) key-values. Return keys as an array<string>

Usage

Call or Deploy json_keys ?
Call json_keys directly

The easiest way to use bigfunctions

  • json_keys function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy json_keys in your project

Why deploy?

  • You may prefer to deploy json_keys in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

json_keys function can be deployed with:

pip install bigfunctions
bigfun get json_keys
bigfun deploy json_keys

Examples

select bigfunctions.eu.json_keys("{\"created_at\": \"2022-01-01\", \"user\": \"sidali\"}")

select bigfunctions.us.json_keys("{\"created_at\": \"2022-01-01\", \"user\": \"sidali\"}")

select bigfunctions.europe_west1.json_keys("{\"created_at\": \"2022-01-01\", \"user\": \"sidali\"}")

+------------------------+
| keys                   |
+------------------------+
| ['created_at', 'user'] |
+------------------------+

Use cases

You have a BigQuery table containing a column with JSON strings representing product information. Each JSON string has a flat structure (no nested objects) with keys like "product_name", "price", "category", etc. You want to extract all the keys present in these JSON strings.

Example Table:

| product_info (STRING) | |---|---| | {"product_name": "Laptop", "price": 1200, "category": "electronics"} | | {"product_name": "T-shirt", "category": "clothing", "color": "blue"} | | {"price": 25, "product_name": "Book", "author": "Jane Doe"} |

Query using json_keys:

SELECT bigfunctions.us.json_keys(product_info) AS keys
FROM `your_project.your_dataset.your_table`;

Result:

| keys (ARRAY) | |---|---| | ["product_name", "price", "category"] | | ["product_name", "category", "color"] | | ["price", "product_name", "author"] |

This allows you to dynamically determine the keys present in each JSON string without hardcoding them. You could then use this information for various purposes like:

  • Schema validation: Verify that all expected keys are present in the JSON data.
  • Data transformation: Create new columns based on the extracted keys.
  • Dynamic querying: Construct queries that access fields within the JSON based on the available keys.
  • Data analysis: Analyze the frequency of different keys to understand the structure of your JSON data.
Need help or Found a bug?
Get help using json_keys

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about json_keys

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on