Working with JSON and Semi-Structured Data
1. Storing JSON in SQL Databases
| DB | Type |
|---|---|
| Postgres | JSON (text), JSONB (binary, indexable) |
| MySQL 8 | JSON (binary) |
| SQL Server | NVARCHAR + JSON functions |
| Oracle | JSON (21c+), BLOB/CLOB earlier |
| SQLite | TEXT + JSON1 extension |
2. Querying JSON Fields
Example: PG JSONB Queries
-- access
SELECT data->>'name', data#>>'{address,city}' FROM users;
-- contains
SELECT * FROM users WHERE data @> '{"status":"active"}';
-- key exists
SELECT * FROM users WHERE data ? 'phone';
-- jsonb_path_query
SELECT jsonb_path_query(data, '$.orders[*] ? (@.total > 100)') FROM users;3. Indexing JSON Documents
| Index | Use |
|---|---|
| GIN on JSONB | Generic — supports @>, ?, ?|, ?& |
| GIN jsonb_path_ops | Smaller, only @> — faster |
| Expression index | btree on (data->>'email') |
| MySQL functional index | On JSON_EXTRACT path |
4. Validating JSON Structure
| Tool | Detail |
|---|---|
| PG CHECK constraint | CHECK (jsonb_typeof(data) = 'object') |
| pg_jsonschema / is_jsonb_valid | Extension-based schema validation |
| MySQL JSON_SCHEMA_VALID | Built-in 8.0.17+ |
| App layer | Ajv / Pydantic / Zod |
5. Modeling Hybrid Relational-JSON
| Pattern | Detail |
|---|---|
| Stable columns + JSON extras | Strict for queryable; flexible for the rest |
| Per-tenant custom fields | JSONB column with per-tenant schema |
| Avoid JSON for joins | Promote frequently-joined keys to columns |
| Versioning | schema_version inside JSON |
6. Handling XML Data
| DB | Support |
|---|---|
| Postgres | XML type, XPath, xmltable() |
| SQL Server | XML type, XQuery, typed XML |
| Oracle | XMLType, XQuery |
| Modern preference | JSON over XML for new systems |
7. Implementing JSON Path Expressions
| Expression | Meaning |
|---|---|
| $ | Root |
| $.a.b | Field navigation |
| $.items[*] | All array elements |
| $.items[0] | First element |
| $.items[*] ? (@.qty > 5) | Filter |
| $.* / $..b | Wildcard / recursive descent |
8. Converting Between JSON and Tables
Example: jsonb_to_recordset / json_table
-- Postgres
SELECT * FROM jsonb_to_recordset('[{"id":1,"name":"a"},{"id":2,"name":"b"}]')
AS x(id INT, name TEXT);
-- MySQL 8 / Oracle
SELECT * FROM JSON_TABLE('[{"id":1},{"id":2}]', '$[*]'
COLUMNS (id INT PATH '$.id')) AS jt;9. Designing Flexible Schemas
| Tip | Detail |
|---|---|
| Use JSON for variability | Optional / sparse fields |
| Promote hot fields | To real columns when stable |
| Document expected keys | Even when not enforced |
| Lint at write time | Catch typos early |
10. Optimizing JSON Query Performance
| Tip | Detail |
|---|---|
| Prefer JSONB over JSON | Parsed once, indexable |
| GIN + jsonb_path_ops | Smaller, faster for @> |
| Expression index for hot keys | btree(data->>'email') |
| Promote hot fields | Real columns + indexes |
| Avoid deep paths in WHERE | Index can't cover |