json_items¶

json_items(json_string)

Description¶

Extract key_value_items from json_string which has only flat (no nested) key-values. Return key_value_items as array< struct<key string, value string> >

Usage¶

Call or Deploy json_items ?

Call json_items directly

The easiest way to use bigfunctions

json_items 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_items in your project

Why deploy?

You may prefer to deploy json_items 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_items function can be deployed with:

pip install bigfunctions
bigfun get json_items
bigfun deploy json_items

Examples¶

EUUSeurope-west1

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

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

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

+-----------------------------------------------------------------------------------------------------+
| key_value_items                                                                                     |
+-----------------------------------------------------------------------------------------------------+
| [
|   struct("created_at" as key, "date" as value),
|   struct("user" as key, "name" as value)
| ]
 |
+-----------------------------------------------------------------------------------------------------+

Use cases¶

You have a table in BigQuery containing a column with JSON strings representing product details. Each JSON string has a flat structure (no nested objects). You want to extract the key-value pairs from these JSON strings and analyze them.

Example Table:

products
| product_id | details                                    |
|------------|--------------------------------------------|
| 1          | '{"name": "Laptop", "price": "1200", "brand": "XYZ"}' |
| 2          | '{"name": "Mouse", "price": "25", "brand": "ABC"}'   |
| 3          | '{"name": "Keyboard", "price": "75", "brand": "XYZ"}'|

Query using json_items:

SELECT
    product_id,
    item.key,
    item.value
  FROM
    `your-project.your_dataset.products`,
    UNNEST(bigfunctions.your-region.json_items(details)) AS item;

Result:

| product_id | key    | value |
|------------|--------|-------|
| 1          | name   | Laptop|
| 1          | price  | 1200  |
| 1          | brand  | XYZ   |
| 2          | name   | Mouse |
| 2          | price  | 25    |
| 2          | brand  | ABC   |
| 3          | name   | Keyboard |
| 3          | price  | 75    |
| 3          | brand  | XYZ   |

This allows you to easily query and analyze individual attributes of the products, such as finding all products of a certain brand or within a specific price range. You've effectively transformed semi-structured JSON data into a relational format for easier analysis with standard SQL. Remember to replace your-project, your_dataset, and your-region with your actual values.

Need help or Found a bug?

Get help using json_items

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about json_items

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