Json and Semi Structured Data

Why this matters

Modern analytics data often arrives as JSON: web events, product attributes, API responses, and logs. As a Data Analyst, you will need to extract fields, filter by nested values, and convert arrays into rows for reporting.

• Marketing: Parse campaign parameters from events JSON.
• Product: Unnest item arrays to analyze top SKUs.
• Support: Summarize error logs by nested error codes.

Concept explained simply

JSON is a flexible container with key–value pairs (objects) and ordered lists (arrays). SQL adds functions to read inside that container.

Mental model

Imagine a labeled shoebox (the row) with smaller labeled boxes (JSON object keys). You can open the box (extract), pick specific items (filter), and lay out all items from a bag inside (unnest arrays).

Core patterns you need

-- Conceptual pattern (adjust to your SQL dialect)
SELECT order_id, extract_json_text(order_json, '$.shipping.city') AS city
FROM orders;

WHERE extract_json_text(order_json, '$.shipping.method') = 'air'

-- Turn items[] array into one row per item
SELECT order_id, item.sku, item.qty, item.price
FROM orders, LATERAL unnest_json_array(order_json, '$.items') AS item;

CAST(JSON_value AS NUMERIC) or ::numeric (engine-specific)

Worked examples

We will use a small orders table with a JSON column order_json.

order_id | customer_id | order_json
-------- | ----------- | -------------------------------------
101      | 1           | {
           "shipping": {"city": "Austin", "method": "ground"},
           "totals": {"subtotal": 120.00, "tax": 9.60},
           "coupon": null,
           "items": [
             {"sku":"A1","qty":1,"price":60},
             {"sku":"B2","qty":2,"price":30}
           ]
         }
102      | 2           | {
           "shipping": {"city": "Boston", "method": "air"},
           "totals": {"subtotal": 50.00, "tax": 4.00},
           "coupon": "WELCOME10",
           "items": [
             {"sku":"A1","qty":1,"price":50}
           ]
         }
103      | 1           | {
           "shipping": {"city": "Austin", "method": "pickup"},
           "totals": {"subtotal": 80.00, "tax": 0.00},
           "items": [
             {"sku":"C3","qty":4,"price":20}
           ]
         }

Example 1: Extract shipping city as text

-- Portable idea
SELECT order_id,
       /* extract city as text */
       JSON_VALUE(order_json, '$.shipping.city') AS city
FROM orders;

Example 2: Filter orders shipped by air

-- Portable idea
SELECT order_id
FROM orders
WHERE JSON_VALUE(order_json, '$.shipping.method') = 'air';

Example 3: Unnest items and compute line revenue

-- Portable idea: one row per item with computed revenue
SELECT o.order_id,
       i.sku,
       CAST(i.qty AS NUMERIC) * CAST(i.price AS NUMERIC) AS line_revenue
FROM orders o
JOIN LATERAL JSON_TABLE(o.order_json, '$.items[*]'
  COLUMNS (
    sku   VARCHAR(50) PATH '$.sku',
    qty   DECIMAL(10,2) PATH '$.qty',
    price DECIMAL(10,2) PATH '$.price'
  )
) AS i ON TRUE;  -- For engines without JSON_TABLE, see alternatives below

Example 4: Aggregate by nested key (city)

SELECT JSON_VALUE(order_json, '$.shipping.city') AS city,
       COUNT(*) AS orders,
       SUM(CAST(JSON_VALUE(order_json, '$.totals.subtotal') AS NUMERIC)) AS subtotal_sum
FROM orders
GROUP BY city
ORDER BY orders DESC;

Practice: Exercises

Use the sample orders table above. Solve using your SQL engine of choice. Prefer native JSON functions, and cast types where needed.

Exercise 1 — Extract a nested field

Task: Return order_id and shipping city as plain text.

• Output columns: order_id, city
• Expected order: by order_id ascending

Exercise 2 — Unnest and aggregate

Task: Unnest items and compute total quantity and revenue per sku.

• Output columns: sku, total_qty, revenue
• Sort by sku ascending
• Revenue = SUM(qty * price)

• Checklist before you run:
  • Used engine-appropriate function for extraction
  • Handled arrays with UNNEST/JSON_TABLE/FLATTEN/json_each
  • Casted numbers for arithmetic
  • Sorted as requested

Common mistakes and self-check

• Forgetting to unquote JSON strings: If you see quotes in results or NULLs in comparisons, use the text extraction function (e.g., ->> in Postgres, JSON_VALUE in BigQuery, JSON_UNQUOTE in MySQL).
• Treating numbers as text: Cast before arithmetic; otherwise concatenation or errors may occur.
• Incorrect JSON path: Start with $. for the root, use dot notation for nested keys, and [*] for arrays.
• Dropping rows during joins: When unnesting, prefer CROSS JOIN/LATERAL; avoid INNER JOIN with conditions that filter out legitimate rows prematurely.
• Confusing json vs jsonb (Postgres): Prefer jsonb for indexing and operators; functions differ slightly.

Who this is for

• Data Analysts who work with event logs, product catalogs, or API feeds
• SQL users moving beyond strictly tabular data

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY
• Basic understanding of data types and casting

Learning path

1. Read JSON basics (objects vs arrays)
2. Practice scalar extraction
3. Learn to filter by nested values
4. Unnest arrays into rows
5. Aggregate and join unnested data back to facts

Practical projects

• Event analytics: Parse UTM parameters from event properties and build a campaign performance table by source/medium.
• Catalog attributes: Explode product attributes JSON into a key–value table and find the most common color/size combinations.
• Error monitoring: Extract error.code and error.message from logs and produce a weekly top-errors report.

Mini challenge

Create a query that returns, for each city, the top SKU by revenue using the sample orders data. Steps:

• Unnest items
• Compute revenue per order per sku
• Aggregate per city and sku
• Use a window function (ROW_NUMBER by city order by revenue desc) to pick the top

Next steps

• Learn to index JSON fields (engine-specific) for performance
• Standardize JSON parsing UDFs or views for your team
• Move to more complex nesting and arrays-of-arrays

Quick Test is available to everyone. Log in to save your progress and resume later.