Exploring JSON manipulation in Redshift with dbt

JSON is a popular data format due to its lightweight structure, readability, and compatibility across programming languages. Converting semistructured JSONs to a structured format within your database can be a hassle, though. This post explores how you can use Redshift SQL and dbt to manipulate JSON within your warehouse, using two practical and real world examples.

Amazon Redshift + dbt ❤️ JSON

Storing JSON in Redshift

Before we start, it is good to know that Redshift can store JSONs in columns of either type varchar or type SUPER.

If your JSON is stored in a varchar typed column, you first need to parse the JSON to be able to interact with it. For this we can use the native parse_json() function. Note that this column type supports up to 65535 bytes of information to be stored.

If your individual objects are larger, it is good to know that Redshift has another data type — called SUPER — which supports up to 16 MB (!) of data, in the case of JSON.

Redshift uses PartiQL to navigate and iterate over or into arrays and structures. PartiQL enables us to do all sorts of transformations on the object(s). See the docs for more information.

Deconstructing JSON objects

Lets say we run a webshop, and we have some JSONs containing which products are in a particular cart, with productUuid as key, and the quantity as value, like below:

{
  "1ee513d6-923b-6b20-bb40-a9d6a4c267c2": 1,
  "1ee513d6-85e9-66d8-92f5-a9c231f94f7f": 2
}

If the JSON is stored in a column, it’s possible to deconstruct this in SQL like so:

with cart as (
    select
        1 as website_event_id,
        json_parse('{"1ee513d6-923b-6b20-bb40-a9d6a4c267c2":1, "1ee513d6-85e9-66d8-92f5-a9c231f94f7f": 2}') as json
)

select
    website_event_id,
    key as product_uuid,
    value as quantity
from
    cart as t,
    unpivot t.json as value at key

Note the unpivot t.json as value on key here, this is the line that deconstructs the JSON.

The output will look like the following, making it easy for further processing

Parsing arrays

Lets step it up. Consider the following JSON array representing a simple cart with product and quantity:

[
  {
    "productUuid":"1ee513d6-923b-6b20-bb40-a9d6a4c267c2",
    "quantity":3
  },
  {
    "productUuid":"1ee513d6-85e9-66d8-92f5-a9c231f94f7f",
    "quantity":4
  },
  {
    "productUuid":"1ee513d6-846a-6bd6-84a5-eb32fd7f25f1",
    "quantity":1
  },
  {
    "productUuid":"1ee513fa-fbc0-620c-8af8-c124ab14ded5",
    "quantity":11
  },
  {
    "productUuid":"1ee513d6-858a-62be-8c7d-0b5fecfc4bd9",
    "quantity":1
  }
]

This value is stored in a column named cart_products, part of the carts table, that holds other general information such as cart_uuid & cart_value as well. How would we parse this array? Let’s look at the following dbt model:

with seq as (
    {{ generate_number_list(0, 100) }}
),

exploded_array as (
    select
        cart_uuid,
        client_uuid,
        json_extract_array_element_text(cart_products, seq.i) as json,
        cart_total
    from {{ source("ldg", "carts") }}, seq
    where seq.i < json_array_length(cart_products)
),

final as (
    select
        cart_uuid,
        client_uuid,
        json_extract_path_text(json, 'productUuid') as product_uuid,
        json_extract_path_text(json, 'quantity') as quantity,
        cart_total
    from exploded_array
)

select * from final

There are a few things to note here:

• The key to make this work is the dbt generate_number_list macro. See below. This macro generates a list of numbers from 0 to 100, and uses that list to extract each object in the array based on their index (indices are zero-based).

{%- macro generate_number_list(lower_bound, upper_bound, column_name='i', increment=1) -%}
    {# Define loop #}
    {%- for number in range(lower_bound, upper_bound, increment) -%}
        select {{ number }} as {{ column_name }}
        {% if not loop.last %}
            union all
        {% endif %}
    {% endfor %}
{% endmacro %}

• The where seq.i < json_array_length(cart_products) clause makes sure that only the indices that actually point to an existing object get returned.

• Having exploded the array, we now have a record per object in the original array — stored in a column named json — and can reach that object by using the json_extract_path() function.

That’s it! Hopefully you can apply one of the methods explored in this post within your dbt project.

Hi, I’m Bastiaan 👋🏼 Data Lead at a scale-up. I write about the Modern Data Workflow, where I explore tools & processes to supercharge your data capabilities. Follow me for more!