Deep Object Filter - Problem
Given a table containing JSON objects and a filter condition, perform a deep filtering operation that removes records where nested properties don't meet the criteria.
The deep filter should:
- Extract and evaluate nested JSON properties using
JSON_EXTRACT - Remove records where the filter condition evaluates to false
- Handle cases where JSON properties are null or missing
- Return only records with valid, non-empty nested data
If no records meet the criteria after filtering, return an empty result set.
Table Schema
Documents
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Primary key |
data
|
json | JSON object containing nested properties |
category
|
varchar | Document category |
Primary Key: id
Note: Each row contains a JSON document that may have nested objects and arrays
Input & Output
Example 1 — Filter Active Adult Users
Input Table:
| id | data | category |
|---|---|---|
| 1 | {"user":{"active":true,"age":25,"name":"John"},"settings":{"notifications":true}} | profile |
| 2 | {"user":{"active":false,"age":16,"name":"Jane"},"settings":{"notifications":false}} | profile |
| 3 | {"user":{"active":true,"age":30,"name":"Bob"},"settings":{"notifications":true}} | profile |
Output:
| id | data | category |
|---|---|---|
| 1 | {"user":{"active":true,"age":25,"name":"John"},"settings":{"notifications":true}} | profile |
| 3 | {"user":{"active":true,"age":30,"name":"Bob"},"settings":{"notifications":true}} | profile |
💡 Note:
Filters records where user.active is true AND user.age is >= 18. Record 2 is filtered out because the user is inactive (false) and under 18.
Example 2 — Handle Missing Properties
Input Table:
| id | data | category |
|---|---|---|
| 4 | {"user":{"active":true,"age":22}} | incomplete |
| 5 | {"settings":{"notifications":true}} | incomplete |
| 6 | {} | empty |
Output:
| id | data | category |
|---|
💡 Note:
No records match because they either lack required nested properties or have null/missing values. The filter requires both user.active=true and user.age>=18 and non-null settings.notifications.
Constraints
-
1 ≤ id ≤ 1000 -
datacontains valid JSON objects -
Nested properties may be
nullor missing
Visualization
Tap to expand
Understanding the Visualization
1
Input
Table with JSON objects
2
Extract
JSON_EXTRACT nested values
3
Filter
Apply WHERE conditions
Key Takeaway
🎯 Key Insight: Use JSON_EXTRACT to access nested properties and filter complex JSON data structures efficiently
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code