{"user_id": 10234, "event": "click", "timestamp": "2026-06-15T14:32:01Z", "payload": {"page": "/products/456", "device": "mobile", "duration_ms": 230, "referral": "wechat"}}

import duckdb
import json
import random
from datetime import datetime, timedelta

# Simulate 50,000 user behavior log entries
pages = ["/home", "/products/123", "/products/456", "/cart", "/checkout", "/profile", "/search"]
devices = ["mobile", "desktop", "tablet"]
referrals = ["wechat", "google", "direct", "douyin", "xiaohongshu"]
events = ["view", "click", "add_to_cart", "purchase", "share"]

rows = []
base_time = datetime(2026, 6, 1)

for i in range(50000):
 ts = base_time + timedelta(seconds=random.randint(0, 30*24*3600))
 row = {
 "user_id": random.randint(1000, 20000),
 "event": random.choice(events),
 "timestamp": ts.strftime("%Y-%m-%dT%H:%M:%SZ"),
 "payload": {
 "page": random.choice(pages),
 "device": random.choices(devices, weights=[0.6, 0.3, 0.1])[0],
 "duration_ms": random.randint(50, 5000),
 "referral": random.choice(referrals)
 }
 }
 rows.append(json.dumps(row))

# Write to JSONL format (one JSON object per line)
with open("user_events.jsonl", "w") as f:
 f.write("\n".join(rows))

print(f"Generated {len(rows)} JSON log entries")

import duckdb

con = duckdb.connect(":memory:")

# One line of code: read JSONL and automatically expand nested fields
result = con.execute("""
 SELECT 
 user_id,
 event,
 timestamp,
 payload.page,
 payload.device,
 payload.duration_ms,
 payload.referral
 FROM read_json_auto('user_events.jsonl')
""").fetchdf()

print(result.head())

{
 "user_id": 10234,
 "event": "purchase",
 "timestamp": "2026-06-15T14:32:01Z",
 "payload": {
 "page": "/products/456",
 "device": {
 "type": "mobile",
 "os": "iOS",
 "browser": "Safari"
 },
 "product": {
 "id": 456,
 "price": 299.00,
 "category": "electronics"
 },
 "duration_ms": 230
 }
}

SELECT 
 user_id,
 event,
 timestamp,
 payload.page,
 payload.device.type AS device_type,
 payload.device.os AS device_os,
 payload.product.price,
 payload.product.category,
 payload.duration_ms
FROM read_json_auto('deep_nested.jsonl')

SELECT 
 payload.page AS page,
 COUNT(*) AS total_views,
 COUNT(*) FILTER (WHERE event = 'view') AS views,
 COUNT(*) FILTER (WHERE event = 'click') AS clicks,
 COUNT(*) FILTER (WHERE event = 'add_to_cart') AS cart_adds,
 COUNT(*) FILTER (WHERE event = 'purchase') AS purchases,
 ROUND(100.0 * COUNT(*) FILTER (WHERE event = 'purchase') / NULLIF(COUNT(*), 0), 2) AS conversion_rate
FROM read_json_auto('user_events.jsonl')
GROUP BY payload.page
ORDER BY total_views DESC

SELECT 
 payload.device AS device_type,
 COUNT(*) AS total_events,
 ROUND(AVG(payload.duration_ms), 0) AS avg_duration_ms,
 ROUND(MEDIAN(payload.duration_ms), 0) AS median_duration_ms,
 COUNT(*) FILTER (WHERE event = 'purchase') AS purchases,
 ROUND(100.0 * COUNT(*) FILTER (WHERE event = 'purchase') / NULLIF(COUNT(*), 0), 2) AS purchase_rate
FROM read_json_auto('user_events.jsonl')
GROUP BY payload.device
ORDER BY total_events DESC

SELECT 
 DATE(timestamp) AS sale_date,
 EXTRACT(HOUR FROM timestamp) AS hour_of_day,
 COUNT(*) AS event_count,
 COUNT(DISTINCT user_id) AS unique_users,
 COUNT(*) FILTER (WHERE event = 'purchase') AS purchases,
 SUM(payload.duration_ms) AS total_engagement_ms
FROM read_json_auto('user_events.jsonl')
GROUP BY 1, 2
ORDER BY 1, 2

-- Discover all top-level keys in the JSON
SELECT DISTINCT json_keys(payload) AS keys
FROM read_json_auto('user_events.jsonl')
LIMIT 5

import duckdb
from typing import Optional
import pandas as pd

class JSONAnalyzer:
 """Lightweight JSON log analyzer"""
 
 def __init__(self, jsonl_path: str):
 self.con = duckdb.connect(":memory:")
 self.jsonl_path = jsonl_path
 self._table_loaded = False
 
 def _ensure_loaded(self):
 if not self._table_loaded:
 self.con.execute(f"CREATE TABLE events AS SELECT * FROM read_json_auto('{self.jsonl_path}')")
 self._table_loaded = True
 
 def page_ranking(self, top_n: int = 10) -> pd.DataFrame:
 """Page visit ranking and conversion rate"""
 self._ensure_loaded()
 return self.con.execute("""
 SELECT 
 payload.page,
 COUNT(*) AS views,
 COUNT(*) FILTER (WHERE event = 'purchase') AS purchases,
 ROUND(100.0 * COUNT(*) FILTER (WHERE event = 'purchase') / NULLIF(COUNT(*), 0), 2) AS conversion_rate
 FROM events
 GROUP BY payload.page
 ORDER BY views DESC
 LIMIT ?
 """, [top_n]).fetchdf()
 
 def device_comparison(self) -> pd.DataFrame:
 """Device type comparison analysis"""
 self._ensure_loaded()
 return self.con.execute("""
 SELECT 
 payload.device,
 COUNT(*) AS total,
 ROUND(AVG(payload.duration_ms), 0) AS avg_duration,
 ROUND(100.0 * COUNT(*) FILTER (WHERE event = 'purchase') / NULLIF(COUNT(*), 0), 2) AS purchase_rate
 FROM events
 GROUP BY payload.device
 ORDER BY total DESC
 """).fetchdf()
 
 def close(self):
 self.con.close()

# Usage example
analyzer = JSONAnalyzer("user_events.jsonl")
print("=== Page Ranking ===")
print(analyzer.page_ranking(5).to_string(index=False))
print("\n=== Device Comparison ===")
print(analyzer.device_comparison().to_string(index=False))
analyzer.close()