Semi-structured data handling (JSON) in dbt

Introduction

Semi-structured data like JSON is common and flexible. Handling it lets you extract useful info from messy or nested data.

You get data from web APIs that return JSON responses.

You store user preferences or settings in JSON format.

You analyze logs or events saved as JSON strings.

You want to flatten nested JSON data for easier querying.

You combine structured tables with JSON columns in your database.

Syntax

dbt

select
  json_extract_path_text(json_column, 'key') as value
from table_name

Use json_extract_path_text to get text values from JSON keys.

dbt runs SQL on your warehouse, so JSON functions depend on your database (e.g., Snowflake, Redshift).

Examples

Extracts the 'name' field from the JSON column user_data.

dbt

select
  json_extract_path_text(user_data, 'name') as user_name
from users

Extracts nested key 'type' inside 'event' from JSON column event_json.

dbt

select
  json_extract_path_text(event_json, 'event', 'type') as event_type
from events

Gets the first element from a JSON array stored in json_array.

dbt

select
  json_extract_array_element_text(json_array, 0) as first_item
from data_table

Sample Program

This example shows how to extract a nested user name and the second action from JSON data stored in json_col.

dbt

with raw_data as (
  select '{"user": {"id": "123", "name": "Alice"}, "actions": ["login", "purchase"]}' as json_col
)
select
  json_extract_path_text(json_col, 'user', 'name') as user_name,
  json_extract_array_element_text(json_extract_path_text(json_col, 'actions'), 1) as second_action
from raw_data

Important Notes

JSON functions vary by database; check your warehouse docs for exact syntax.

Extracted JSON values are usually text; convert types if needed.

Use CTEs in dbt models to organize JSON extraction steps clearly.

Summary

Semi-structured JSON data is common and flexible.

Use JSON extraction functions to get nested info in dbt SQL models.

Check your database's JSON function support for best results.