Semi-structured data handling (JSON) in dbt - Step-by-Step Execution

Concept Flow - Semi-structured data handling (JSON)

Load JSON data

↓

Parse JSON fields

↓

Extract nested values

↓

Transform or filter data

↓

Load into structured table

↓

Use in SQL queries

This flow shows how JSON data is loaded, parsed, transformed, and stored in a structured format for querying.

Execution Sample

dbt

select
  id,
  json_extract_path_text(data, 'user', 'name') as user_name,
  (json_extract_path_text(data, 'user', 'age'))::int as user_age
from raw_json_table
where (json_extract_path_text(data, 'user', 'age'))::int > 30

This code extracts user name and age from JSON data and filters users older than 30.

Execution Table

Step	Action	Input Data	Extracted Values	Filter Result	Output Row
1	Read row 1	{"user":{"name":"Alice","age":35}}	user_name=Alice, user_age=35	35 > 30 is True	Row included: id=1, user_name=Alice, user_age=35
2	Read row 2	{"user":{"name":"Bob","age":28}}	user_name=Bob, user_age=28	28 > 30 is False	Row excluded
3	Read row 3	{"user":{"name":"Carol","age":40}}	user_name=Carol, user_age=40	40 > 30 is True	Row included: id=3, user_name=Carol, user_age=40
4	End of data	No more rows	N/A	N/A	Query ends

💡 All rows processed; filter excludes users age <= 30

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	Final
id	N/A	1	2	3	3
user_name	N/A	Alice	Bob	Carol	Carol
user_age	N/A	35	28	40	40
output_rows_count	0	1	1	2	2

Key Moments - 2 Insights

Why is the row with user age 28 excluded from the output?

Because the filter condition 'user_age > 30' is false for age 28, as shown in execution_table row 2.

How does json_extract_path_text work with nested JSON?

It navigates the JSON keys step-by-step, for example 'user' then 'name', extracting the text value, as seen in execution_table extracted values.

Visual Quiz - 3 Questions

Look at the execution table, what is the user_age extracted at step 3?

A 28

B 40

C 35

D N/A

💡 Hint

Check the 'Extracted Values' column at step 3 in the execution_table.

At which step does the filter condition become false?

A Step 2

B Step 1

C Step 3

D No step

💡 Hint

Look at the 'Filter Result' column in execution_table for the false condition.

If we change the filter to 'user_age > 25', how many rows would be included?

A 1

B 2

C 3

D 0

💡 Hint

Refer to variable_tracker output_rows_count and consider ages 35, 28, 40.

Concept Snapshot

Semi-structured data handling (JSON) in dbt:
- Use json_extract_path_text() to get nested JSON values
- Cast extracted text to needed types (e.g., ::int)
- Filter or transform based on extracted values
- Load results into structured tables for SQL queries
- Enables querying JSON data like normal columns

Full Transcript

This lesson shows how to handle JSON data in dbt. We start by loading JSON rows. Then we extract nested values using json_extract_path_text. For example, we get user name and age from the JSON. We convert age to integer to filter users older than 30. The execution table shows each row read, values extracted, filter applied, and whether the row is included. The variable tracker follows variables like id, user_name, user_age, and counts output rows. Key moments clarify why some rows are excluded and how JSON extraction works. The quiz tests understanding of extracted values, filter steps, and effects of changing filter conditions. The snapshot summarizes the main steps to handle JSON data in dbt.

Practice

1. What is the main purpose of using JSON functions in dbt when working with semi-structured data?

easy

A. To convert JSON data into CSV files automatically

B. To encrypt JSON data for security

C. To create new JSON files from scratch

D. To extract and manipulate nested data inside JSON columns

Solution

1. Step 1: Understand JSON data in dbt

   JSON data often contains nested information that needs to be accessed or transformed.

2. Step 2: Purpose of JSON functions

   JSON functions allow extracting specific parts of this nested data for analysis or transformation.

3. Final Answer:

   To extract and manipulate nested data inside JSON columns -> Option D

4. Quick Check:

   JSON functions = extract nested data [OK]

Hint: JSON functions help get nested info from JSON columns [OK]

Common Mistakes:

• Thinking JSON functions create files
• Assuming JSON functions encrypt data
• Confusing JSON extraction with file conversion

2. Which of the following is the correct syntax to extract a value from a JSON column named data using dbt's SQL with PostgreSQL syntax?

easy

A. SELECT data->name AS name FROM table_name;

B. SELECT data->>'name' AS name FROM table_name;

C. SELECT data['name'] AS name FROM table_name;

D. SELECT data->>name AS name FROM table_name;

Solution

1. Step 1: Identify PostgreSQL JSON extraction syntax

   PostgreSQL uses ->> to extract JSON text values by key as text.

2. Step 2: Apply correct syntax to extract 'name'

   The correct syntax is data->>'name' to get the text value of the 'name' key.

3. Final Answer:

   SELECT data->>'name' AS name FROM table_name; -> Option B

4. Quick Check:

   PostgreSQL JSON text extraction = data->>'key' [OK]

Hint: Use ->> with quotes for JSON text extraction in PostgreSQL [OK]

Common Mistakes:

• Using square brackets instead of ->>
• Missing quotes around the key name
• Using -> instead of ->> for text extraction

3. Given the following dbt SQL snippet using Snowflake syntax:

SELECT
  data:id::string AS user_id,
  data:attributes:age::int AS age
FROM users

What will be the output columns for a row where data contains {"id": "U123", "attributes": {"age": 30}}?

medium

A. user_id = 'U123', age = 30

B. user_id = 'id', age = 'attributes:age'

C. user_id = NULL, age = NULL

D. user_id = 'U123', age = NULL

Solution

1. Step 1: Understand Snowflake JSON extraction

   Snowflake uses colon notation data:id to access JSON keys and casting (::string, ::int) to convert types.

2. Step 2: Apply extraction to given JSON

   For {"id": "U123", "attributes": {"age": 30}}, data:id::string extracts 'U123', and data:attributes:age::int extracts 30.

3. Final Answer:

   user_id = 'U123', age = 30 -> Option A

4. Quick Check:

   Snowflake JSON path extraction = correct values [OK]

Hint: Use colon paths and cast to get JSON values in Snowflake [OK]

Common Mistakes:

• Confusing JSON keys with strings
• Not casting extracted values
• Using wrong path syntax

4. You wrote this dbt SQL code to extract a nested JSON value:

SELECT
  data->'user'->>email AS email
FROM users

But you get an error. What is the likely cause?

medium

A. Using ->> after -> on a JSON object without parentheses

B. Using -> instead of ->> for text extraction

C. Missing quotes around 'email' key

D. The JSON column 'data' is not valid JSON

Solution

1. Step 1: JSON path operators require quoted keys

   Keys in -> and ->> must be string literals enclosed in single quotes.

2. Step 2: Identify syntax issue

   In data->'user'->>email, email lacks quotes and is interpreted as an identifier (e.g., column name) instead of a JSON key, causing a syntax error. Correct syntax: data->'user'->>'email'.

3. Final Answer:

   Missing quotes around 'email' key -> Option C

4. Quick Check:

   JSON keys must be quoted strings [OK]

Hint: Always quote JSON keys in -> and ->> operators [OK]

Common Mistakes:

• Forgetting parentheses in chained JSON extraction
• Misquoting JSON keys
• Assuming JSON column is invalid without checking

5. You have a JSON column info in a dbt model with records like:

{"user": {"id": "U1", "tags": ["a", "b", "c"]}}

How would you write a dbt SQL expression to create a new row for each tag using PostgreSQL JSON functions?

hard

A. SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag;

B. SELECT user_id, unnest(info->'user'->'tags') AS tag FROM table_name;

C. SELECT user_id, json_each_text(info->'user'->'tags') AS tag FROM table_name;

D. SELECT user_id, explode(info->'user'->'tags') AS tag FROM table_name;

Solution

1. Step 1: Identify function to expand JSON arrays in PostgreSQL

   PostgreSQL uses json_array_elements_text() to turn JSON arrays into a set of text rows.

2. Step 2: Apply function to nested JSON array

   Extract the 'tags' array with info->'user'->'tags' and expand using comma syntax in FROM: SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag.

3. Final Answer:

   SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag; -> Option A

4. Quick Check:

   Use json_array_elements_text() to expand JSON arrays [OK]

Hint: Use FROM table, json_array_elements_text(...) AS tag to explode JSON arrays in PostgreSQL [OK]

Common Mistakes:

• Using unnest() on JSON instead of arrays
• Using json_each_text() which is for objects
• Using explode() which is not PostgreSQL function