SQL-FRONTIER Benchmark Deep-Dive / Search & Retrieval / task_059_Q59_faceted_search_json_attributes
← Prev: Stopword Filtering and Qu... ⊞ All 120 Queries Next: Financial Ledger Running ... →
✔ PASS Search & Retrieval task_059_Q59_faceted_search_json_attributes Scaled: 1M+ Records Ordered: Yes

Faceted Search Aggregations over Semi-Structured JSON Attributes

Candidate Latency (1M Rows)
14.99 ms
(1.2x slower than golden)
Golden Latency (1M Rows)
12.66 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
38.1 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 23.1s
✔ Correctness Verified on 1M+ Records
Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap
Business Scenario & Problem

When a customer searches for 'laptops', e-commerce search engines return matching items along with dynamic facet counts (e.g. Brand: Apple (12), Dell (8); RAM: 16GB (15), 32GB (5)) extracted from JSON specs.

⚡ The Frontier Trap (Why LLMs Fail)

Running separate queries for each facet dimension multiplies query latency and database load.

Prompt / User Request Given to Model
When a customer searches for 'laptops', e-commerce search engines return matching items along with dynamic facet counts (e.g. Brand: Apple (12), Dell (8); RAM: 16GB (15), 32GB (5)) extracted from JSON specs.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: facet_dimension, facet_value, match_count.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 38.1 MiB)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 electronics_catalog 1,000,000 rows 38.1 MiB Data Volume
item_id (INTEGER) title (VARCHAR) specs (JSON)
Data Preview (showing sample rows from 1,000,000 total rows):
# item_id title specs
1 1 MacBook Pro 16 {"brand": "Apple", "ram": "32GB", "screen": 16}
2 2 MacBook Air 13 {"brand": "Apple", "ram": "16GB", "screen": 13}
3 3 XPS 15 Laptop {"brand": "Dell", "ram": "16GB", "screen": 15}
4 4 ThinkPad X1 {"brand": "Lenovo","ram": "16GB", "screen": 14}
5 4 MacBook Pro 16 NULL
6 5 MacBook Air 13 NULL
7 6 XPS 15 Laptop NULL
8 7 ThinkPad X1 NULL
9 8 MacBook Pro 16 NULL
10 9 MacBook Air 13 NULL
11 10 XPS 15 Laptop NULL
12 11 ThinkPad X1 NULL
13 12 MacBook Pro 16 NULL
14 13 MacBook Air 13 NULL
15 14 XPS 15 Laptop NULL

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 69.03 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
🔄 CTE 🔥 44.1% Cost
Latency: 30.42 ms
Rows In/Out: 0
Data Vol: 0 B
⚠️ Heavy Execution Bottleneck (44.1% of query runtime)
📐 PROJECTION 2.1%
Latency: 1.42 ms
Rows In/Out: 1,000,000
Data Vol: 30.76 MB
📁 Projections: ['brand', 'ram']
🔍 TABLE_SCAN 10.1%
Latency: 6.99 ms
Rows In/Out: 1,000,000
Data Vol: 15.38 MB
📁 memory.main.electronics_catalog
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 7
Data Vol: 536 B
📁 Projections: ['__internal_decomp...
⏳ ORDER_BY 0.3%
Latency: 0.18 ms
Rows In/Out: 7
Data Vol: 480 B
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 7
Data Vol: 736 B
📁 Projections: ['__internal_compre...
⚙️ UNION 0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 4
Data Vol: 416 B
📁 Projections: ['facet_dimension',...
📊 HASH_GROUP_BY 🔥 24.9% Cost
Latency: 17.17 ms
Rows In/Out: 4
Data Vol: 352 B
⚠️ Heavy Execution Bottleneck (24.9% of query runtime)
📐 PROJECTION 0.1%
Latency: 0.07 ms
Rows In/Out: 1,000,000
Data Vol: 15.38 MB
📁 Projections: brand
🔍 CTE_SCAN 0.0%
Latency: 0.02 ms
Rows In/Out: 1,000,000
Data Vol: 30.76 MB
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 3
Data Vol: 376 B
📁 Projections: ['facet_dimension',...
📊 HASH_GROUP_BY 18.3%
Latency: 12.66 ms
Rows In/Out: 3
Data Vol: 328 B
📐 PROJECTION 0.1%
Latency: 0.07 ms
Rows In/Out: 1,000,000
Data Vol: 15.38 MB
📁 Projections: ram
🔍 CTE_SCAN 0.0%
Latency: 0.02 ms
Rows In/Out: 1,000,000
Data Vol: 30.76 MB
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
CTE 44.1% 30.42 ms 0 0.00 MB - 🔥 BOTTLENECK
HASH_GROUP_BY 24.9% 17.17 ms 4 0.00 MB - 🔥 BOTTLENECK
HASH_GROUP_BY 18.3% 12.66 ms 3 0.00 MB - Normal
TABLE_SCAN 10.1% 6.99 ms 1,000,000 15.38 MB memory.main.electronics_catalog Normal
PROJECTION 2.1% 1.42 ms 1,000,000 30.76 MB Projections: ['brand', 'ram'] Normal
ORDER_BY 0.3% 0.18 ms 7 0.00 MB - Normal
PROJECTION 0.1% 0.07 ms 1,000,000 15.38 MB Projections: ram Normal
PROJECTION 0.1% 0.07 ms 1,000,000 15.38 MB Projections: brand Normal
CTE_SCAN 0.0% 0.02 ms 1,000,000 30.76 MB - Normal
CTE_SCAN 0.0% 0.02 ms 1,000,000 30.76 MB - Normal
PROJECTION 0.0% 0.01 ms 7 0.00 MB Projections: ['__internal_compress_string_ubigin Normal
PROJECTION 0.0% 0.01 ms 4 0.00 MB Projections: ['facet_dimension', 'facet_value', Normal
PROJECTION 0.0% 0.00 ms 3 0.00 MB Projections: ['facet_dimension', 'facet_value', Normal
PROJECTION 0.0% 0.00 ms 7 0.00 MB Projections: ['__internal_decompress_string(#0)' Normal
UNION 0.0% 0.00 ms 0 0.00 MB - Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 14.99 ms) 
WITH matching_items AS
  (SELECT item_id,
          title,
          specs->>'brand' AS brand,
          specs->>'ram' AS ram
   FROM electronics_catalog)
SELECT 'brand' AS facet_dimension,
       brand AS facet_value,
       count(*) AS match_count
FROM matching_items
GROUP BY brand
UNION ALL
SELECT 'ram' AS facet_dimension,
       ram AS facet_value,
       count(*) AS match_count
FROM matching_items
GROUP BY ram
ORDER BY facet_dimension,
         match_count DESC,
         facet_value ASC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 12.66 ms) 
-- Extract faceted breakdown of matching laptop brands and RAM capacities
 WITH matching_items AS
  (SELECT item_id,
          title,
          specs->>'brand' AS brand,
          specs->>'ram' AS ram
   FROM electronics_catalog)
SELECT 'brand' AS facet_dimension,
       brand AS facet_value,
       count(*) AS match_count
FROM matching_items
GROUP BY brand
UNION ALL
SELECT 'ram' AS facet_dimension,
       ram AS facet_value,
       count(*) AS match_count
FROM matching_items
GROUP BY ram
ORDER BY facet_dimension,
         match_count DESC,
         facet_value ASC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE electronics_catalog (item_id INT, title VARCHAR, specs JSON);


INSERT INTO electronics_catalog
VALUES (1, 'MacBook Pro 16', '{"brand": "Apple", "ram": "32GB", "screen": 16}'),
       (2, 'MacBook Air 13', '{"brand": "Apple", "ram": "16GB", "screen": 13}'),
       (3, 'XPS 15 Laptop', '{"brand": "Dell",  "ram": "16GB", "screen": 15}'),
       (4, 'ThinkPad X1', '{"brand": "Lenovo","ram": "16GB", "screen": 14}');

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (7 rows × 3 cols)
# facet_dimension facet_value match_count
1 brand NULL 999,996
2 brand Apple 2
3 brand Dell 1
4 brand Lenovo 1
5 ram NULL 999,996
6 ram 16GB 3
7 ram 32GB 1
Expected Golden Output (7 rows × 3 cols)
# facet_dimension facet_value match_count
1 brand NULL 999,996
2 brand Apple 2
3 brand Dell 1
4 brand Lenovo 1
5 ram NULL 999,996
6 ram 16GB 3
7 ram 32GB 1
← Prev: Stopword Filtering and Qu... ⊞ Back to Benchmark Dashboard Next: Financial Ledger Running ... →