dbt - Advanced Patterns
How can you combine JSON extraction and conditional filtering in dbt SQL to select users whose
data:profile:age is over 25 and data:profile:active is true?How can you combine JSON extraction and conditional filtering in dbt SQL to select users whose data:profile:age is over 25 and data:profile:active is true?
hard🚀 Application Q9 of 15
Step-by-Step Solution
Solution:
Step 1: Extract and cast age correctly
json_extract_path_text returns text, so cast age to INT for numeric comparison.Step 2: Compare active as string
active is text 'true', so compare to string 'true' for boolean check.Step 3: Evaluate options
UseWHERE CAST(json_extract_path_text(data, 'profile', 'age') AS INT) > 25 AND json_extract_path_text(data, 'profile', 'active') = 'true'correctly casts age and compares active as string. Others miss casting or use invalid syntax.Final Answer:
Use WHERE CAST(json_extract_path_text(data, 'profile', 'age') AS INT) > 25 AND json_extract_path_text(data, 'profile', 'active') = 'true' -> Option DQuick Check:
Cast extracted JSON text before numeric comparison [OK]
Quick Trick: Cast JSON text before numeric filters, compare booleans as strings [OK]
Common Mistakes:
MISTAKES- Comparing text directly to numbers
- Using boolean literals on text
- Skipping casting functions
Master "Advanced Patterns" in dbt
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore dbt Quizzes