[Solved] You have a JSON column info in a dbt model with records like:{"user": {"id": "U1", "tags":... | dbt

dbt - Advanced Patterns

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?

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

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

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

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

Step-by-Step Solution

Solution:

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.
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.
Final Answer:
SELECT user_id, tag FROM table_name, json_array_elements_text(info->'user'->'tags') AS tag; -> Option A
Quick Check:
Use json_array_elements_text() to expand JSON arrays [OK]

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

Common Mistakes:

MISTAKES

Master "Advanced Patterns" in dbt

9 interactive learning modes - each teaches the same concept differently

More dbt Quizzes

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