Skip to main content

// advent-of-postgres/day-8

Day 8 of 24IntermediateJSONBPath operators

The JSON Logs

The logs are JSON, and the field you need is three levels deep. Find where the gold-tier users hit errors.

Opens 8 December 2026, 00:00 UTC

Gold-tier users are the elves who pay for priority gift tracking, and support has a pile of complaints from them. Pepper wants to know which region to look at first.

All service logs for 8 December are in logs:

Column Type Notes
id bigint Primary key
ts timestamptz When the line was written
body jsonb The structured log line

A typical body:

{
  "level": "error",
  "service": "gift-router",
  "msg": "request handled",
  "ctx": { "region": "eu-north", "user": { "id": "u42", "tier": "gold" } }
}

Anonymous requests have no user block at all.

Your task

Which region had the most error-level log lines from gold-tier users?

Submit the region.

Stuck? Show a hint

->> returns text, and #>> takes a path like '{ctx,user,tier}'. Lines without a user block return NULL there.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.