TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 24

JSON and Semi-Structured Data: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1Difference between JSON and JSONB (storage, dedup keys, ordering, indexing).
  2. Q2What does -> return (jsonb) vs ->> (text)?
  3. Q3When do you use ->> vs -> in SELECT / WHERE?
  4. Q4What do #> and #>> do (path access by array of keys)?
  5. Q5What does the @> containment operator test?
  6. Q6What does the ? operator test (top-level key existence)?
  7. Q7What does jsonb_array_elements do to an array?
  8. Q8Difference between jsonb_array_elements and jsonb_array_elements_text.
  9. Q9What does jsonb_build_object(k1,v1,k2,v2,...) construct?
  10. Q10What does to_jsonb(value-or-row) do?
  11. Q11What does jsonb_agg(expr) aggregate?
  12. Q12What does to_jsonb of a whole row produce?
  13. Q13Why must you cast (json ->> 'x')::numeric before doing math?
  14. Q14What is jsonb_object_keys used for?
  15. Q15What does jsonb_path_query do (concept)?
  16. Q16Why is JSONB better than text for storing semi-structured data?
  17. Q17Where does nested JSON come from in real pipelines (APIs, event streams)?
  18. Q18What does "flattening" nested JSON into rows mean?
  19. Q19Why might you GIN-index a JSONB column (Topic 20 tie-in, concept)?
  20. Q20Name RetailMart's one real JSONB column and how we simulate the rest.
  21. Q21What does jsonb_build_array(...) build?
  22. Q22Difference between a JSON object {} and a JSON array [].
  23. Q23How do NULLs appear in JSON (SQL NULL vs JSON null)?
  24. Q24What do jsonb_each / jsonb_each_text return?
  25. Q25Why does ->> on a missing key return NULL instead of erroring?

EXTRACT & BUILD BASICS

  1. Q26From audit.procedure_calls: extract input_params->>'id' as text.
  2. Q27Cast that extracted id to integer; return call_id + id.
  3. Q28Count procedure_calls where input_params ? 'id' (key present).
  4. Q29Join the extracted id back to products.products on product_id.
  5. Q30Build a JSON per order: jsonb_build_object('order_id',..,'net_total',..,'status',..).
  6. Q31Build to_jsonb(o.*) for the first 10 orders.
  7. Q32From a built order JSON, extract ->>'status'.
  8. Q33Build an api-request JSON from audit.api_requests (method, status_code, latency, user_agent).
  9. Q34From that built JSON, extract method and status_code back out.
  10. Q35Cast a built JSON field (->>'net_total')::numeric and SUM it.
  11. Q36Build a customer JSON {id, name, tier} from customers.customers.
  12. Q37Extract tier from the built customer JSON and group counts.
  13. Q38Build a product JSON {prod_id, price, cost} and compute margin from extracted fields.
  14. Q39Build a store JSON with a nested address object (jsonb_build_object inside).
  15. Q40Use ->> to pull a field and filter rows where it equals a value.
  16. Q41Build a review JSON {rating, date} and extract rating::int.
  17. Q42Build jsonb with a NULL value; observe the resulting JSON null.
  18. Q43COALESCE an extracted ->> value with a default.
  19. Q44Build order JSON and extract via path #>>'{status}'.
  20. Q45Count distinct extracted ->>'method' from built api JSON.
  21. Q46Build a ticket JSON {priority, subject}; group by extracted priority.
  22. Q47Extract two fields and concatenate into one label.
  23. Q48Build an employee JSON {name, dept}; filter by extracted dept.
  24. Q49Round-trip: row -> to_jsonb -> ->> a column -> compare to the original column.
  25. Q50Build JSON and check optional-key existence with ? .

ARRAYS & NESTING

  1. Q51Aggregate order_items into a jsonb array per order (jsonb_agg).
  2. Q52Build a nested order document {order_id, items:[...]} (jsonb_build_object + jsonb_agg).
  3. Q53Unnest that items array with jsonb_array_elements (one row per item).
  4. Q54Count items per order via jsonb_array_length on the built array.
  5. Q55Build a customer document with an orders array.
  6. Q56Extract the first array element with ->0.
  7. Q57jsonb_array_elements_text on a built array of labels/tags.
  8. Q58Group by a nested field after unnesting (e.g. item prod_id).
  9. Q59Build {region, stores:[...]} nested document per region.
  10. Q60SUM a numeric field across unnested array elements.
  11. Q61Build a per-product review array; compute avg rating from unnested.
  12. Q62Use #> to navigate into a nested object path (returns jsonb).
  13. Q63Use #>> to navigate into a nested path (returns text).
  14. Q64Build a 2-level nested doc (region->store->count); read a deep path.
  15. Q65Filter built docs with @> containment (e.g. contains {"status":"Delivered"}).
  16. Q66Check key existence with ? across built documents.
  17. Q67List keys of a built order object with jsonb_object_keys.
  18. Q68Build an array of distinct payment modes as jsonb.
  19. Q69Unnest a built monthly-revenue array into rows.
  20. Q70Build {customer, order_count, total}; extract numeric for sorting.
  21. Q71Aggregate the top-5 products per category into a jsonb array.
  22. Q72Build nested {brand, products:[{prod_id,price}]}; unnest two levels.
  23. Q73jsonb_agg with ORDER BY inside (an ordered array).
  24. Q74Build a jsonb array of order dates per customer.
  25. Q75Count keys in a built object (jsonb_object_keys + count).

COMBINED / PIPELINES

  1. Q76Build a per-order nested JSON {order, items[], payment} (API-export shape).
  2. Q77Flatten that document back into a relational row set.
  3. Q78Per customer: build a "customer-360" JSON (profile + order count + latest order, Topic 22).
  4. Q79Aggregate orders into a jsonb array per customer; keep only those with >5 orders.
  5. Q80Build api-request JSON; extract status_code; group error (>=400) vs ok.
  6. Q81Build api JSON; cast latency; compute median latency per method (Topic 22).
  7. Q82Build event JSON from web_events; bucket by extracted hour (Topic 23).
  8. Q83Round-trip pipeline: rows -> jsonb_agg -> unnest -> re-aggregate; verify totals.
  9. Q84Build monthly revenue as a jsonb object keyed by month (jsonb_object_agg).
  10. Q85Extract values from that month-keyed object for specific months.
  11. Q86Build a region->metrics nested doc with median + P95 inside (Topic 22).
  12. Q87Per category: jsonb array of monthly units (Topic 23) for a sparkline payload.
  13. Q88Build a ticket document; filter where priority is Critical via ->>.
  14. Q89Construct a JSON export of the top-10 customers by spend (API response).
  15. Q90Build a product-catalog JSON {prod_id, name, price, in_promo?} with a boolean.
  16. Q91Use @> to find built order docs containing a specific item prod_id.
  17. Q92Build nested store->employees doc; count employees via jsonb_array_length.
  18. Q93Cast and SUM a jsonb array of line amounts to reconstruct the order total.
  19. Q94Build a JSON time-series payload (date->revenue) for the last 30 days (Topic 23).
  20. Q95Per customer: latest order as JSON (DISTINCT ON, Topic 22) embedded in a profile doc.
  21. Q96Build JSON with computed margin; filter where margin < 0.2.
  22. Q97jsonb_object_agg of region->revenue; extract the max-revenue region.
  23. Q98Build an audit-change document {table, col, old, new} from record_changes.
  24. Q99From procedure_calls: extract id, join products, build enriched JSON with product name.
  25. Q100Executive JSON export: per region {median_aov, p95_aov, orders} array (Topic 22 + JSON).

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1JSONB vs JSON: which preserves key order/duplicates, which queries faster?
  2. Q2-> vs ->> vs #> vs #>> - a precise four-way comparison.
  3. Q3@> vs ? vs ?| vs ?& - the containment / key-existence family.
  4. Q4When does jsonb_array_elements multiply row counts (LATERAL cross-join semantics)?
  5. Q5Why is a ->> result always text, requiring a cast for math/sort?
  6. Q6jsonb_build_object vs to_jsonb(row) - when to use each.
  7. Q7jsonb_agg vs jsonb_object_agg - array vs keyed object.
  8. Q8GIN index on JSONB: jsonb_ops vs jsonb_path_ops (Topic 20 concept).
  9. Q9Which operators does a GIN index accelerate (@>, ?, ...)?
  10. Q10jsonb_path_query / JSONPath basics (concept).
  11. Q11Flattening strategy: LATERAL jsonb_array_elements then extract scalars.
  12. Q12NULL vs JSON 'null' vs a missing key - three distinct cases.
  13. Q13Coalescing missing JSON fields with sensible defaults.
  14. Q14Storing event payloads as JSONB: pros and cons vs wide columns.
  15. Q15Round-tripping relational <-> JSON without losing data.
  16. Q16jsonb_set / concatenation (||) for building/modifying (concept; writes -> practice).
  17. Q17Indexing an extracted scalar with an expression index on (doc->>'k') (Topic 20).
  18. Q18Why deeply nested JSON hurts clarity and performance.
  19. Q19Validating JSON shape (keys present, types correct) in SQL.
  20. Q20jsonb_strip_nulls and why it helps API payloads.
  21. Q21Aggregating many rows into a single JSON document per group.
  22. Q22Pretty-printing with jsonb_pretty for debugging.
  23. Q23Extracting JSON fields and pivoting them into columns (Topic 21).
  24. Q24Combining JSON extraction with window functions (Days 16-18).
  25. Q25When to push JSON parsing to ETL vs do it at query time.

EXTRACT, CAST & AGGREGATE

  1. Q26procedure_calls: extract id, join products, return proc_name + product name + price.
  2. Q27procedure_calls: count calls per extracted id; top 10 most-referenced ids.
  3. Q28Build api JSON; extract status_code; count by 2xx / 4xx / 5xx bands.
  4. Q29Build api JSON; median & P95 latency per method (Topic 22).
  5. Q30Build order docs; extract net_total::numeric; revenue per extracted status.
  6. Q31Build customer docs; group by extracted tier; counts + median spend.
  7. Q32Build product docs; compute margin from extracted price/cost; per category.
  8. Q33jsonb_object_agg month->revenue; extract a slice of specific months.
  9. Q34Build review docs; avg extracted rating per product; filter > 4.
  10. Q35Build event docs (Topic 23 timestamp); bucket by extracted hour.
  11. Q36Extract nested address city from a built customer doc; group counts.
  12. Q37Build ticket docs; group by extracted priority; SLA% within target.
  13. Q38Build api JSON; filter user_agent containing 'Mobile'; count.
  14. Q39Build per-store doc; extract numeric revenue; rank stores (Topic 16).
  15. Q40Build a doc with two numerics; compute their ratio; filter on threshold.
  16. Q41Extract ->>'method' and pivot counts into columns (Topic 21).
  17. Q42Build order doc with nested payment; extract #>>'{payment,mode}'.
  18. Q43jsonb_agg an ordered array of a customer's order dates; read last with ->-1.
  19. Q44Cast extracted dates; compute gaps between them (Topic 23 + JSON).
  20. Q45Build region doc with a nested KPI object; read median via path.
  21. Q46Count rows where a built doc ? 'discount' (optional key present).
  22. Q47Extract array length per built customer-orders doc; show its distribution.
  23. Q48Build doc; apply jsonb_strip_nulls; compare key counts before/after.
  24. Q49Build api JSON; group by method x status band (Topic 21 pivot).
  25. Q50Build doc; extract + COALESCE a missing field with a default.

ARRAYS, NESTING & FLATTENING

  1. Q51Nested order doc {order, items:[{prod,qty,amt}]}; unnest; revenue per prod.
  2. Q52Customer doc with orders array; unnest; count orders; verify vs base table.
  3. Q53Region->stores->orders 2-level nest; flatten to (region, store, count).
  4. Q54Top-N products per category as a jsonb array (window + jsonb_agg).
  5. Q55Unnest items; group by prod_id; total qty; compare to a direct aggregate.
  6. Q56@> containment: order docs containing item prod_id = X.
  7. Q57?& multiple required keys present across docs.
  8. Q58#> navigate to a nested items array; jsonb_array_length.
  9. Q59brand->products->reviews 3-level doc; deep path to a rating.
  10. Q60jsonb_array_elements WITH ORDINALITY to keep element position.
  11. Q61Unnest a month->value object via jsonb_each; cast; SUM.
  12. Q62Build a per-customer tag array (derived flags); unnest; count by tag.
  13. Q63Aggregate order_items to an array, then re-aggregate SUM from the array (round-trip).
  14. Q64Build a nested doc; extract a deep path #>>'{a,b,c}'.
  15. Q65Filter docs where a nested numeric (cast) exceeds a threshold.
  16. Q66jsonb_object_agg prod->qty per order; extract a specific prod's qty.
  17. Q67Build a customer-360 with arrays of orders, reviews, tickets; count each.
  18. Q68Unnest two arrays from the same doc independently (two LATERAL calls).
  19. Q69Build a jsonb array of {month, revenue} per region; unnest; MoM (Topic 18).
  20. Q70Containment query: which docs contain {"tier":"Gold"}.
  21. Q71Build a nested doc; jsonb_object_keys to list its dynamic fields.
  22. Q72Flatten api-log JSON into typed columns (the lab, via constructed JSON).
  23. Q73Unnest + pivot: items array -> one column per category (Topic 21).
  24. Q74Build doc; compute an array aggregate (avg of unnested amounts).
  25. Q75Reconstruct an order total by summing unnested item amounts; reconcile.

PIPELINES & APPLIED

  1. Q76API export: per order nested {order, items[], payment, customer}.
  2. Q77Flatten that export back; verify row counts match the base joins.
  3. Q78Customer-360 doc per customer with latest order (Topic 22) + median spend.
  4. Q79Build month-keyed revenue object per region (jsonb_object_agg); read the trend.
  5. Q80Time-series JSON payload: date->revenue last 90 days (Topic 23) as one object.
  6. Q81Error-rate API report: build api JSON; % 4xx/5xx per endpoint.
  7. Q82Latency SLO JSON: per method P50/P95/P99 (Topic 22) as a jsonb object.
  8. Q83Top-customers JSON array for an API response (rank + jsonb_agg).
  9. Q84Catalog export: per product {price, in_promo, margin} with booleans.
  10. Q85Audit-change doc from record_changes; filter price changes > 50% (cast).
  11. Q86Nested region->KPIs doc with median, P95, order count (Topic 22).
  12. Q87Funnel-ish counts as a JSON object (web_events stages).
  13. Q88Sparkline payload: per category monthly-units jsonb array (Topic 23).
  14. Q89Build doc; validate required keys (? checks); flag invalid docs.
  15. Q90Containment filter to find docs matching a complex criterion (@>).
  16. Q91Per-customer cadence doc (median gap, Topic 22+23) embedded as JSON.
  17. Q92Reshape: unpivot KPIs to long, build a {metric,value} array (Topic 21 + JSON).
  18. Q93Build api JSON; peak-hour error analysis (Topic 23 hour + JSON).
  19. Q94Export a top-10 stores leaderboard as a jsonb array with rank + median.
  20. Q95Customer segment doc: tier + RFM-ish NTILE scores (Topic 16) as JSON.
  21. Q96Build nested order doc; @> to find orders containing a returned item.
  22. Q97JSON time-bucket: 15-min slots (Topic 23) -> counts as a JSON object.
  23. Q98Reconcile: JSON-built totals vs SQL aggregates; assert equality.
  24. Q99Build a paginated API response shape {data:[...], meta:{count,page}}.
  25. Q100Exec dashboard JSON: per region {median_aov, p95, orders, latest_order} array (Topic 22+23).

Interview grade, edge cases

CONCEPTUAL

  1. Q1JSONB internals: binary storage, why @>/? are GIN-able, key-order loss.
  2. Q2jsonb_ops vs jsonb_path_ops GIN indexes - size vs operator coverage (Topic 20).
  3. Q3Expression index on (doc->>'k') for equality/range, and when the planner uses it (Topic 20).
  4. Q4JSONPath: jsonb_path_query / _first / _exists - filters, wildcards, predicates.
  5. Q5Designing a JSONB event schema: required vs optional keys, versioning.
  6. Q6Schema-on-read tradeoffs: flexibility vs validation vs performance.
  7. Q7Flattening deeply nested arrays with recursive-free LATERAL chains.
  8. Q8NULL / absent / json-null trichotomy and its effect on filters and counts.
  9. Q9Round-trip fidelity: numeric precision, key order, duplicate keys.
  10. Q10When to materialize extracted columns (Topic 25 MV) vs parse live.
  11. Q11Indexing strategy for mixed containment + scalar-range JSON queries.
  12. Q12jsonb_set / || / - / #- for transformation (concept; writes -> practice).
  13. Q13Validating payloads: key presence, type checks, enum membership in SQL.
  14. Q14Aggregating heterogeneous documents safely (defensive extraction).
  15. Q15Performance of jsonb_array_elements on large arrays (row explosion).
  16. Q16Pivoting dynamic JSON keys into columns when the key set is unknown (limits).
  17. Q17Combining JSON extraction with window analytics (Days 16-18).
  18. Q18JSON for API contracts: pagination, envelopes, error shapes.
  19. Q19Modeling event ingestion as JSONB; tolerating late/extra fields.
  20. Q20Containment vs equality for filtering nested structures.
  21. Q21Partial indexes on a JSON predicate subset (Topic 20).
  22. Q22Cost of casting in WHERE ((doc->>'x')::numeric) and index remedies.
  23. Q23jsonb_strip_nulls / canonicalization for dedup (Topic 26 preview).
  24. Q24Designing a customer-360 document: grain, arrays, refresh.
  25. Q25When NOT to use JSON (well-structured relational data).

PARSING & VALIDATION ENGINES

  1. Q26Parse the (constructed) api-request JSON into typed columns: method, status, latency, ua.
  2. Q27Validate each built doc has all required keys (?&); flag invalid ones.
  3. Q28Type-check: status_code and latency are numeric; reject bad rows.
  4. Q29Error taxonomy: classify status into 2xx/3xx/4xx/5xx from the extracted code.
  5. Q30Per endpoint: request count, error %, P95 latency (Topic 22) from built JSON.
  6. Q31User-agent class: flag Mobile/Tablet/Desktop via LIKE on extracted ua.
  7. Q32Containment audit: docs that @> a required template object.
  8. Q33JSONPath: jsonb_path_query to pull nested predicate matches.
  9. Q34Defensive extraction with COALESCE chains over #>> paths.
  10. Q35Detect schema drift: docs missing an expected key over time (Topic 23).
  11. Q36Build + validate order docs; reconcile item-sum vs net_total; flag mismatches.
  12. Q37Extract + pivot a method x status matrix (Topic 21) from built logs.
  13. Q38Median/P95 latency per method per hour (Topic 22+23) from JSON.
  14. Q39Per customer-360 doc: validate arrays are non-empty; counts.
  15. Q40Flatten nested items two levels; aggregate; compare to relational truth.
  16. Q41jsonb_each_text to enumerate a dynamic KPI object; cast; aggregate.
  17. Q42Find docs where a nested numeric path exceeds a threshold.
  18. Q43Build a versioned doc {v:2,...}; branch extraction by version.
  19. Q44Canonicalize (strip_nulls + key-sort concept) before dedup (Topic 26 preview).
  20. Q45Detect duplicate logical docs via @> in both directions.
  21. Q46Extract array WITH ORDINALITY; compute a position-aware metric.
  22. Q47Build + query a time-series JSON (date->rev); compute MoM (Topic 18).
  23. Q48Reconstruct funnel stage counts from event docs; drop-off %.
  24. Q49Validate enum: extracted status in allowed set; flag violations.
  25. Q50Per region nested KPI doc; deep-path extract; rank regions (Topic 16).

NESTED-DOC PIPELINES

  1. Q51Customer-360 builder: profile + orders[] + reviews[] + tickets[] + latest (Topic 22).
  2. Q52Flatten the customer-360 doc fully back to normalized rows; reconcile counts.
  3. Q53Order-export builder: {order, items[], payment, shipment} nested document.
  4. Q54Region rollup doc: stores[] each with a monthly-revenue[] array (Topic 23).
  5. Q55Catalog doc: category->brands[]->products[] 3 levels; deep navigate.
  6. Q56Unnest 3 levels; aggregate the leaf metric; compare to direct GROUP BY.
  7. Q57jsonb_object_agg to build a prod->qty map per order; query specific prods.
  8. Q58Build {region:{month:revenue}} nested map; extract via #> path.
  9. Q59Top-N-per-group arrays (window, Topic 16) embedded per category.
  10. Q60Time-series object per metric; unnest; gap-fill check (Topic 23).
  11. Q61Containment search across nested docs (@> with a nested object).
  12. Q62Build a paginated envelope {data:[...], meta:{...}}; slice page N.
  13. Q63Sparkline arrays per entity for a dashboard payload.
  14. Q64Merge two built docs (|| concat) and resolve key conflicts (concept).
  15. Q65Build doc; extract + pivot dynamic keys (bounded set) to columns (Topic 21).
  16. Q66Reshape unpivot -> {metric,value}[] (Topic 21), then re-pivot from JSON.
  17. Q67Nested cohort object: cohort->period->retention (Topic 23) as JSON.
  18. Q68RFM-ish scores per customer as JSON (NTILE, Topic 16) - a segment doc.
  19. Q69Build + query an inventory-snapshot doc per warehouse (latest, Topic 22).
  20. Q70Audit-trail doc array from record_changes per record; latest change highlighted.
  21. Q71Build doc with computed booleans (in_promo, is_returned); filter.
  22. Q72Deep aggregate: SUM leaf amounts across 2-level arrays; reconcile.
  23. Q73Build doc; jsonb_array_length distribution; outliers (Topic 22).
  24. Q74Construct an API error-report doc per endpoint (counts, P95, samples).
  25. Q75Customer timeline JSON (Topic 23) embedded; extract the spans.

PRODUCTION SEMI-STRUCTURED SYSTEMS

  1. Q76Customer-360 mart (one JSONB doc per customer) - full builder, validated.
  2. Q77API observability: per endpoint/method error %, P50/P95/P99 latency JSON (Topic 22).
  3. Q78Event-stream flattener: events JSON -> typed fact rows (Topic 23 buckets).
  4. Q79Order-export service: nested doc + reconciliation + envelope.
  5. Q80Region exec dashboard JSON: median/P90/P95 AOV, orders, latest (Topic 22+23).
  6. Q81Catalog API: category-tree doc with products + promo flags + margins.
  7. Q82Cohort retention JSON triangle (Topic 23) for a BI payload.
  8. Q83Audit-change monitor: > 50% price-jump detection from record_changes JSON (Topic 26 preview).
  9. Q84Time-series payload service: date->revenue 90d + moving averages (Topic 23) as JSON.
  10. Q85RFM segment export per customer (NTILE, Topic 16) as JSON.
  11. Q86Inventory freshness JSON per warehouse (latest snapshot, Topic 22).
  12. Q87Funnel analytics JSON: stage counts + drop-off (web_events).
  13. Q88Pricing drift JSON: per product latest vs median price (Topic 22).
  14. Q89Validation gateway: reject/flag docs failing key/type/enum checks.
  15. Q90Pagination + filter service over built docs (envelope + @>).
  16. Q91Heatmap JSON: weekday x hour activity (Topic 23) as a nested object.
  17. Q92SLA scorecard JSON per priority (median/P95 resolution, Topic 22).
  18. Q93Multi-metric region doc; reshape pipeline (Topic 21) JSON <-> relational.
  19. Q94Schema-versioned ingestion: branch by {v} and normalize.
  20. Q95Reconciliation suite: JSON-built totals vs relational; assert equal.
  21. Q96Customer activity unifier JSON (orders + views + tickets latest, Topic 22).
  22. Q97Export top movers JSON (WoW P95 shift, Topic 23) for alerting.
  23. Q98Build + GIN-index plan note (Days 19-20) for containment queries (concept).
  24. Q99End-to-end: rows -> nested docs -> validate -> flatten -> reconcile, one pipeline.
  25. Q100Capstone: a production customer-360 JSONB document (profile, orders[], reviews[], latest order, median/P95 spend, RFM scores) as one query, noting MV materialization (Topic 25).

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a JSONB event-ingestion + analytics layer (raw -> typed -> marts).
  2. Q2GIN jsonb_ops vs jsonb_path_ops vs expression-btree - choosing per workload (Topic 20).
  3. Q3JSONPath at scale: jsonb_path_query_array, predicates, lax vs strict.
  4. Q4Schema evolution / versioning for JSONB payloads; migration strategy.
  5. Q5Materializing extracted columns into MVs vs live parse (Topic 25) - cost model.
  6. Q6Containment-query planning + partial / expression indexes (Days 19-20).
  7. Q7Defensive parsing of untrusted / heterogeneous payloads at scale.
  8. Q8Round-trip fidelity + canonicalization for dedup (Topic 26).
  9. Q9API contracts: envelopes, pagination, error shapes, idempotency.
  10. Q10Customer-360 doc design: grain, arrays, refresh cadence, size limits.
  11. Q11Streaming late / extra fields; tolerant extraction; quarantine.
  12. Q12Cost of row-explosion (jsonb_array_elements) and how to mitigate it.
  13. Q13Hybrid relational + JSON modeling; when to normalize fields out.
  14. Q14Time-series-in-JSON vs relational fact - the tradeoffs.
  15. Q15A validation framework in SQL (keys / types / enums / ranges).
  16. Q16Index-only / containment acceleration limits for deep paths.
  17. Q17Multi-tenant JSON isolation + per-tenant schema drift.
  18. Q18Reconciliation guarantees JSON <-> relational (totals, counts).
  19. Q19Pivoting unknown / dynamic JSON keys - bounded approaches (Topic 21).
  20. Q20Security: avoiding injection when building JSON from free text.
  21. Q21Compression / TOAST behavior of large JSONB and its perf implications.
  22. Q22Backfilling / replaying event JSON idempotently (Topic 26).
  23. Q23Observability payloads (latency / error) and SLO computation (Topic 22).
  24. Q24When JSON is the wrong tool - relational-first guidance.
  25. Q25Designing a semi-structured data contract for downstream BI.

INGESTION & OBSERVABILITY ENGINES

  1. Q26API observability engine: per endpoint/method count, error %, P50/95/99, hourly trend (Topic 22+23) as JSON.
  2. Q27Event-stream flattener at scale: events -> typed facts, sessionized (Topic 23).
  3. Q28Payload validator engine: keys/types/enums/ranges; pass vs quarantine; report.
  4. Q29Schema-drift detector: per day missing / extra keys across event docs (Topic 23).
  5. Q30Error-budget engine: rolling P95 latency SLO + burn rate JSON (Topic 22+23).
  6. Q31User-agent classifier: device/browser from extracted ua (LIKE/regex, Topic 26 preview).
  7. Q32Anomaly engine: latency beyond P99 per endpoint with context (Topic 22).
  8. Q33Funnel engine from event JSON: stage counts, drop-off, time-between (Topic 23).
  9. Q34Reconciliation engine: built-JSON order totals vs relational; mismatch report.
  10. Q35Versioned-payload engine: branch v1/v2 extraction; unify into one schema.
  11. Q36Containment search engine: @> queries + index plan note (Days 19-20).
  12. Q37JSONPath query engine: predicate extraction across nested docs.
  13. Q38Time-series payload engine: per region date->rev object + moving averages (Topic 23).
  14. Q39Heatmap JSON engine: weekday x hour nested object, normalized (Topic 23).
  15. Q40Dynamic-key pivot engine: bounded jsonb_object_keys -> columns (Topic 21).
  16. Q41Canonicalize + dedup engine: strip_nulls / sort -> hash -> duplicates (Topic 26 preview).
  17. Q42Multi-metric region-doc engine: median/P90/P95/IQR (Topic 22) nested.
  18. Q43Cohort-triangle JSON engine: cohort->period->retention + LTV (Topic 23).
  19. Q44RFM segment engine: NTILE scores (Topic 16) -> segment doc + counts.
  20. Q45Audit-change engine: record_changes -> JSON; > 50% jumps; per-table summary.
  21. Q46Pagination + filter API engine: envelope, @> filter, sort, page slice.
  22. Q47Pricing-drift engine: per product latest vs median (Topic 22) JSON + alerts.
  23. Q48Inventory-freshness engine: latest snapshot per SKU JSON (Topic 22) + days-of-cover.
  24. Q49SLA scorecard engine: per priority median/P95/P99 resolution + breach % JSON.
  25. Q50Reshape engine: unpivot -> {metric,value}[] -> re-pivot (Topic 21) round-trip.

CUSTOMER-360 & MART PIPELINES

  1. Q51Customer-360 mart: profile + orders[] + reviews[] + tickets[] + payments[] + latest + median/P95 (Topic 22).
  2. Q52Validate + flatten the 360 doc fully; reconcile every array vs base tables.
  3. Q53Region rollup mart: stores[] -> monthly revenue[] -> KPIs (Topic 23) nested.
  4. Q54Catalog mart: category->brands[]->products[] with promo/margin/booleans, deep paths.
  5. Q55Order-export service: nested doc + envelope + idempotency key.
  6. Q56Cohort + LTV mart JSON: cohort->period->{retention, cum_revenue} (Topic 23).
  7. Q57Inventory mart: warehouse->SKUs[] latest snapshot + value + cover (Topic 22).
  8. Q58Activity-unifier mart: latest of orders/views/tickets/calls per customer (Topic 22).
  9. Q59Time-series mart: per region date->{rev, ma7, ma28, mom} JSON (Topic 23).
  10. Q60Funnel mart: per cohort stage counts + drop-off JSON (Topic 23).
  11. Q61Pricing-book mart: per product as-of monthly price[] (Topic 23) + drift.
  12. Q62RFM mart: per customer scores + segment + recency decile (Topic 16/22) JSON.
  13. Q63SLA mart: per region/priority median/P95 + breach % (Topic 22) nested.
  14. Q64Audit-trail mart: per record change-history[] with latest highlighted (Topic 22).
  15. Q65Heatmap mart: device->weekday->hour counts (Topic 23) 3-level nested.
  16. Q66Lifecycle mart: per customer stage timeline + as-of tier (Topic 22/23).
  17. Q67Reconciliation mart: JSON vs relational totals per region; assertions.
  18. Q68Paginated catalog API with filters (@>) + sort + envelope + meta.
  19. Q69Multi-grain rollup JSON: store->region->all via GROUPING SETS (Topic 21).
  20. Q70Anomaly mart: daily revenue P95/P99 flags (Topic 22/23) as JSON alerts.
  21. Q71Schema-versioned customer doc; migrate v1->v2 in-query (concept; writes -> practice).
  22. Q72Sparkline mart: per entity 12-month arrays (Topic 23) for the UI.
  23. Q73Validation + quarantine mart: invalid docs with reasons.
  24. Q74Build + index-plan note (Days 19-20) for the 360 mart's containment access.
  25. Q75End-to-end mart: rows -> nested -> validate -> flatten -> reconcile -> export.

PRODUCTION SEMI-STRUCTURED SYSTEMS

  1. Q76Customer-360 production mart (full doc) + MV materialization note (Topic 25).
  2. Q77API observability platform JSON: endpoints x methods, error %, P50/95/99, trends (Topic 22+23).
  3. Q78Event analytics platform: flatten -> sessionize -> funnel -> retention JSON (Topic 23).
  4. Q79Real-time-ish SLO board: rolling P95 latency + error-budget burn (Topic 22+23).
  5. Q80Pricing governance JSON: latest vs corridor (P25-P75, Topic 22) + drift alerts.
  6. Q81Inventory command JSON: per warehouse latest + cover + stockout streaks (Topic 22/23).
  7. Q82Cohort + LTV dashboard JSON: triangle + cum revenue + latest cohort (Topic 23).
  8. Q83RFM + segmentation export: per customer JSON for CRM activation (Topic 16).
  9. Q84Fraud / anomaly sweep JSON: P99 outliers + context per region (Topic 22).
  10. Q85Audit-compliance JSON: change history + > 50% jumps + actor (Topic 26 preview).
  11. Q86Funnel + drop-off JSON per channel / hour (Topic 23) for growth.
  12. Q87Region exec dashboard JSON: median/P90/P95 AOV, SLA%, latest, rank (Topic 22+23).
  13. Q88Schema-registry-style validation gateway (keys / types / enums / versions).
  14. Q89Pagination / filter / sort API over 360 docs (envelope, @>, JSONPath).
  15. Q90Heatmap product JSON: device x weekday x hour normalized (Topic 23).
  16. Q91Reconciliation suite across all marts; consistency assertions.
  17. Q92Time-series anomaly alerting JSON (WoW P95 shift, Topic 23).
  18. Q93Customer activity unifier + lifecycle-stage JSON (Topic 22/23).
  19. Q94Catalog + promo API JSON with computed fields + booleans + margins.
  20. Q95Multi-tenant-style per-region doc isolation + drift report.
  21. Q96SLA dynamic-target JSON: target = prior-quarter P90 (Topic 22) attainment.
  22. Q97Observability + business KPIs unified JSON per region.
  23. Q98Backfill / replay-safe ingestion design note + reconciliation query.
  24. Q99End-to-end platform: ingest -> validate -> flatten -> aggregate -> export, one pipeline.
  25. Q100Capstone: the production customer-360 + region-KPI JSON service (nested docs, validation, median/P95, latest, reconciliation), noting GIN index (Topic 20) + MV refresh (Topic 25).