Why JSONB for Regional Analytics
At ViralVidVault, each video has different analytics depending on which European region it trends in. Polish engagement rates, Dutch device breakdowns, Scandinavian watch-time patterns — this data has variable shape and evolves as we add new metrics. PostgreSQL JSONB handles this perfectly.
The Schema
CREATE TABLE video_trends (
id SERIAL PRIMARY KEY,
video_id TEXT NOT NULL REFERENCES videos(id),
snapshot_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
total_views BIGINT NOT NULL,
regional_data JSONB NOT NULL DEFAULT '{}',
engagement JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_trends_video ON video_trends(video_id, snapshot_at DESC);
CREATE INDEX idx_trends_regional ON video_trends USING GIN(regional_data);
CREATE INDEX idx_trends_engagement ON video_trends USING GIN(engagement);
A typical regional_data value:
{
"PL": {"views": 45000, "trending_rank": 3, "category": "Music"},
"NL": {"views": 12000, "trending_rank": 15, "category": "Entertainment"},
"SE": {"views": 8200, "trending_rank": 22, "category": "Music"},
"GB": {"views": 95000, "trending_rank": 8, "category": "Music"}
}
And engagement:
{
"like_rate": 0.052,
"comment_rate": 0.011,
"avg_watch_seconds": 156,
"devices": {"mobile": 0.58, "desktop": 0.35, "tablet": 0.07},
"peak_hour_utc": 19
}
Querying Regional Data
Find videos trending in Poland with high engagement:
SELECT v.title, t.regional_data -> 'PL' ->> 'views' AS pl_views,
t.engagement ->> 'like_rate' AS like_rate
FROM video_trends t
JOIN videos v ON v.id = t.video_id
WHERE t.regional_data ? 'PL'
AND (t.engagement ->> 'like_rate')::float > 0.05
ORDER BY (t.regional_data -> 'PL' ->> 'views')::int DESC
LIMIT 20;
The ? operator checks if the key 'PL' exists. The ->> operator extracts a text value. Cast to float or int for numeric comparisons.
Aggregate views by region across all videos:
SELECT region.key AS region,
COUNT(*) AS video_count,
SUM((region.value ->> 'views')::bigint) AS total_views,
AVG((region.value ->> 'trending_rank')::int) AS avg_rank
FROM video_trends t,
jsonb_each(t.regional_data) AS region
WHERE t.snapshot_at > NOW() - INTERVAL '24 hours'
GROUP BY region.key
ORDER BY total_views DESC;
jsonb_each unpacks the JSONB object into key-value rows. This lets you aggregate across all regions in a single query — something that would require separate columns (views_pl, views_nl, views_se...) without JSONB.
Updating JSONB Incrementally
When new trending data arrives, update specific regions without overwriting the entire object:
-- Add or update a single region
UPDATE video_trends
SET regional_data = jsonb_set(
regional_data,
'{NO}',
'{"views": 5400, "trending_rank": 18, "category": "Nature"}'::jsonb
)
WHERE video_id = 'abc123'
AND snapshot_at = (SELECT MAX(snapshot_at) FROM video_trends WHERE video_id = 'abc123');
-- Merge new engagement data
UPDATE video_trends
SET engagement = engagement || '{"share_rate": 0.023}'::jsonb
WHERE video_id = 'abc123';
The || operator merges objects. Existing keys are updated, new keys are added. No need to read-modify-write in application code.
Time-Series Analytics for the Dashboard
ViralVidVault admin dashboard shows trends over time:
SELECT
date_trunc('day', snapshot_at) AS day,
jsonb_object_agg(
region.key,
region.value ->> 'views'
) AS views_by_region
FROM video_trends t,
jsonb_each(t.regional_data) AS region
WHERE video_id = 'abc123'
AND snapshot_at > NOW() - INTERVAL '30 days'
GROUP BY day
ORDER BY day;
This returns one row per day with a JSONB object showing views per region — exactly what a chart library needs.
Performance Observations
On ~50,000 trend records with GIN indexes:
- Key existence check (
?): 1-2ms - Path extraction with filter: 3-5ms
jsonb_eachaggregation (24h window): 10-20ms- Full cross-region aggregation: 30-60ms
JSONB is not as fast as dedicated columns for simple lookups, but the flexibility to add regions, metrics, and breakdowns without schema changes is invaluable for a platform tracking trends across 7 European markets.
This article is part of the Building ViralVidVault series. Check out ViralVidVault to see these techniques in action.