JSON, Arrays & LATERAL Queries — SQL Course

What you'll learn

Work with semi-structured PostgreSQL values, expand nested data, aggregate JSON, and use LATERAL for per-row dependent queries. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply json vs jsonb to a realistic data question
Apply JSON operators to a realistic data question
Apply JSON aggregation to a realistic data question
Apply Arrays and unnest to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
jsonb	Binary JSON supporting indexing and rich operators	Use for variable attributes, not as a substitute for known relational structure
LATERAL	A FROM item evaluated using earlier row values	Use for top-N-per-row or set-returning dependent logic
JSON aggregation	Building structured documents from rows	Aggregate only after defining ordering and child grain

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the semi-structured SQL query question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Build ordered order documents

The lateral subquery creates one bounded, ordered items array per order without multiplying parent rows.

order_documents.sql

SELECT o.id, o.created_at, items.value AS items
FROM sales.orders AS o
CROSS JOIN LATERAL (
  SELECT jsonb_agg(jsonb_build_object(
    'productId', oi.product_id,
    'quantity', oi.quantity,
    'unitPrice', oi.unit_price
  ) ORDER BY oi.id) AS value
  FROM sales.order_items AS oi
  WHERE oi.order_id = o.id
) AS items;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Putting frequently filtered, constrained, or related facts inside arbitrary JSON weakens integrity and complicates indexing.

Independent workshop

Build a review-ready semi-structured SQL query lab against the course commerce dataset.

Your finished workshop must include:

json vs jsonb
JSON operators
JSON aggregation
Arrays and unnest
LATERAL
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

jsonb: Use for variable attributes, not as a substitute for known relational structure
LATERAL: Use for top-N-per-row or set-returning dependent logic
JSON aggregation: Aggregate only after defining ordering and child grain

Quick check

1. Which rule best applies to jsonb?

2. Which rule best applies to LATERAL?

3. Which rule best applies to JSON aggregation?

Next: Phase Project: Analytical Dashboard Queries