DB Exercises
0
← Exercises/
Business Analytics•4 of 14
PrevNext
Business AnalyticsAdvancedPRO

Querying Nested JSONB Metadata with #>> Path Extraction

Scenario

Cloud infrastructure error logs store diagnostic traces in deeply nested JSONB structures. SREs need to extract the microservice name and root error code from the JSON path `{error, details, code}`.

Task
Query `service_incidents` to extract fields using PostgreSQL JSONB path operators: 1. `incident_id` 2. `incident_data#>>'{service, name}'` AS `service_name` 3. `incident_data#>>'{error, code}'` AS `error_code` 4. `(incident_data#>>'{error, http_status}')::int` AS `http_status` Filter for incidents where `incident_data#>>'{error, severity}' = 'FATAL'`. Order results by `incident_id` ascending.
Expected Output Format

Extracted service error metrics for all fatal incidents.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Querying Nested JSONB Metadata with #>> Path Extraction” SQL Problem

Business Scenario

Cloud infrastructure error logs store diagnostic traces in deeply nested JSONB structures. SREs need to extract the microservice name and root error code from the JSON path `{error, details, code}`.

Expected Output & Criteria

Extracted service error metrics for all fatal incidents.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE service_incidents (
        incident_id INT PRIMARY KEY,
        created_at TIMESTAMP NOT NULL,
        incident_data JSONB NOT NULL
      );

Target SQL Clauses & Operators

#>>WHEREORDER BY

Want more Business Analytics practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Business Analytics Track→