Handling Unpredictable JSON Keys with Python and Jinja

When working with data that has arbitrary and unpredictable keys, extracting specific values can be a challenge. In this post, we’ll explore how to use Python and Jinja templating in dbt to extract desired values from JSON-like data.

Introduction to the Problem

The problem at hand is that the JSON blob column in our Redshift table contains data with arbitrary top-level keys. The structure of each JSON object is consistent within itself, but the top-level keys are different across objects. This makes it difficult to use regular expressions or other string-based solutions to extract specific values.

We’ll take an approach that involves using Python’s built-in json module and Jinja templating in dbt to dynamically handle the unpredictable key structure.

Understanding JSON and Key Extraction

Before we dive into the code, let’s quickly review how JSON works. A JSON object is a collection of key-value pairs, where each key is a string and must be unique within an object. The values can be strings, numbers, booleans, arrays, or other JSON objects.

When extracting specific values from a JSON-like object, we need to find the desired key within the object. In our case, we’re interested in extracting name and type values from each JSON blob.

Using Python’s json Module

To work with JSON data in Python, we can use the json module. This module provides functions for parsing and generating JSON data.

Here’s an example of how to extract specific values from a JSON object using the json module:

import json

# Sample JSON blob
json_blob = '{"key1": {"name": 1, "type": "foo"}, "keyA": {"name": 2, "type": "bar"}}'

# Load JSON data into a Python dictionary
data = json.loads(json_blob)

# Extract specific values from the dictionary
name = data['key1']['name']
type_ = data['key1']['type']

print(name)  # Output: 1
print(type_)  # Output: foo

# To extract multiple key-value pairs, you can use a loop or a list comprehension:
names = [value for value in data.values() if 'name' in str(value)]
types = [str(value) for value in data.values() if 'type' in str(value)]

print(names)  # Output: ['1', '2']
print(types)   # Output: ['foo', 'bar']

Using Jinja Templating in dbt

Now that we’ve covered how to extract specific values from a JSON object using Python, let’s focus on using Jinja templating in dbt.

dbt is an open-source SQL framework that allows you to write data models and views in SQL. One of the features of dbt is its support for Jinja templating, which enables you to create dynamic SQL queries using templates.

Here’s an example of how to use Jinja templating in dbt to extract specific values from a JSON-like object:

{%
  set (
    json_key = 'key1',
    name_column = 'name',
    type_column = 'type'
  )
%}

{{ config(materialized='temp') }}

SELECT 
  {{ jsonb_build_object(
    (jsonb_array_elements(json_blob)->> name_column),
    (jsonb_array_elements(json_blob)->> type_column)
  ) }} AS values
FROM 
  your_table;

In this example, we’re using the config function to specify that we want to materialize our SQL query as a temporary table. We’re then using Jinja templating to create a dynamic SQL query that extracts specific values from the JSON blob.

The jsonb_build_object function is used to build an object with key-value pairs, where each value comes from the name_column and type_column. The (jsonb_array_elements(json_blob)->> name_column) syntax is used to access the name value for each JSON object in the array.

Putting it All Together

Now that we’ve covered how to use Python’s json module and Jinja templating in dbt, let’s put it all together.

Here’s an example of a full database model that uses these techniques:

{%
  set (
    table_name = 'your_table',
    json_column = 'json_blob'
  )
%}

{{ config(materialized='table') }}

CREATE TABLE {{ full_join(table_name, ['id'], 'inner', ['id']) }} 
(
  id INT,
  name VARCHAR(255),
  type VARCHAR(255)
);

{%
  set (
    json_key = 'key1',
    name_column = 'name',
    type_column = 'type'
  )
%}

SELECT 
  {{ jsonb_build_object(
    (jsonb_array_elements({{ json_column }})->> name_column),
    (jsonb_array_elements({{ json_column }})->> type_column)
  ) }} AS values
FROM 
  {{ full_join(table_name, ['id'], 'inner', ['id']) }};

In this example, we’re creating a database model that joins the table table on the id column. We’re then using Jinja templating to create a dynamic SQL query that extracts specific values from the JSON blob.

Conclusion

Extracting arbitrary keys from JSON-like data can be challenging, but using Python’s json module and Jinja templating in dbt provides a powerful solution. By leveraging these tools, you can dynamically handle unpredictable key structures and extract desired values with ease.

In this post, we’ve covered the basics of how to use Python’s json module and Jinja templating in dbt. We’ve also provided examples of full database models that demonstrate how to put these techniques into practice.

Last modified on 2023-07-23