🔎Querying JSON

Paths, text, and row grain

Engines expose path access (-> / ->> in PostgreSQL, JSON_VALUE in SQL Server, JSON_EXTRACT in MySQL). Cast extracted scalars to SQL types before comparing. Unnesting arrays changes grain, treat expanded rows like any other join fan-out.

SQL

PostgreSQL operators

SELECT instrument_id,
       settings->>'mode' AS mode,
       (settings->'lanes')::int AS lanes
FROM instrument_profiles
WHERE settings @> '{"mode":"rapid"}'::jsonb;

✓Illustrative result set

instrument_id	mode	lanes
SEQ-01	rapid	8

SQL

Flatten array with jsonb_array_elements

SELECT instrument_id, value->>'serial' AS lane_serial
FROM instrument_profiles,
     jsonb_array_elements(settings->'lane_serials') AS value;

✓Illustrative result set

instrument_id	lane_serial
SEQ-01	L-1001
SEQ-01	L-1002

Cross-product explosions

Unnesting huge arrays before restricting parents, planner processes every element.
Comparing JSON as opaque text instead of jsonb semantics.
Ignoring duplicate keys in input objects when debugging “impossible” values.

Filter parents first

Use CTEs or subqueries to filter parents, then lateral unnest only matching rows.
Document expected JSON shape; prefer SQL/JSON standard functions when porting dialects.
Test empty objects, empty arrays, and missing keys explicitly.

🧭Modeling

JSON is an escape hatch, not always the center.

When several paths become stable filters or foreign keys, promoting to relational tables usually wins clarity, constraints, and planner transparency, keep JSON for the long tail of attributes.

Lateral joins

Prefer LATERAL when you want correlated unnest per parent, it reads cleanly and keeps cardinality obvious in code review.

TL;DR

Query JSON with vendor operators (->/->> in PostgreSQL, JSON_VALUE in SQL Server, JSON_EXTRACT in MySQL) or by normalizing hot fields into typed columns. Path predicates without indexes devolve to heap scans, add expression indexes for filters you rely on in production. Keep money, identifiers, and audit columns relational for constraints and joins.