Working with JSON in SQL
Store and query semi-structured attributes with PostgreSQL jsonb, extract scalars and arrays, update documents, and compare other database dialects.
// Part 01
Relational columns and JSON have different jobs
Relational columns are ideal for fields that drive joins, constraints, reporting, and frequent filtering. JSON is useful when an entity has a small, evolving set of optional or source-specific attributes. A delivery order, for example, might have stable columns for order ID, customer, amount, and status, plus a JSON document for a partner’s evolving delivery metadata.
JSON support does not remove the need for a schema. You still need to decide which keys are required, what types they hold, whether missing values are allowed, and which paths need indexes. Keep high-value relationships and frequently queried business fields as normal columns.
SELECT, and basic filtering. Objective: model a bounded JSON use case, query it safely, and identify when a value belongs in a typed relational column instead.// Part 02
PostgreSQL JSON and JSONB
PostgreSQL offers json and jsonb. Both validate JSON syntax on input. json preserves the input text representation, while jsonb stores a decomposed representation suited to efficient processing and indexing. For most application query workloads, jsonb is the practical default.
The check requires the top-level value to be an object; it does not validate every nested key or business rule. Use normal columns and constraints for fields that must be strongly typed and relational. JSON input must be valid JSON—single quotes are SQL string delimiters, while JSON keys and string values use double quotes.
// Part 03
Extract values and filter documents
In PostgreSQL, -> returns a JSON value, and ->> returns text. The #> and #>> operators follow a nested path. Text extraction is convenient for comparison, but cast explicitly when numeric or date semantics matter. Missing paths generally produce SQL NULL, so normal NULL rules apply.
For containment, @> asks whether the left JSONB value contains the right JSONB structure. It is often easier to read than several nested path predicates for exact key/value structure. Use jsonb_exists or the ? operator to test a top-level key. Be deliberate about whether a condition should match an absent key, JSON null, or a SQL NULL result.
// Part 04
Expand arrays into rows
When a JSON array needs analysis, expand it into relational rows. PostgreSQL’s jsonb_array_elements returns each element; use a lateral join so the function can reference the current source row. For an array of objects, extract fields from each element and cast carefully.
Array expansion changes the output grain: one order may produce several rows. If you aggregate those rows back to an order, verify that each item appears once and that the array actually exists and is an array. A defensive query may filter with jsonb_typeof(attributes -> 'items') = 'array' before expanding.
// Part 05
Update a path and index query patterns
Use jsonb_set to update a nested value without replacing the entire document. The path is a text array, and the new value must be JSONB. The final flag controls whether missing path elements should be created. Build the new JSON value with to_jsonb for a SQL text value, rather than hand-building JSON strings.
A JSONB GIN index can accelerate containment and key-existence patterns. A general GIN index supports many operators; jsonb_path_ops can be smaller and efficient for containment-focused workloads but does not support every operator supported by the default operator class. If the application repeatedly filters on one scalar path, a targeted expression index may be more appropriate.
Do not add all three indexes by default. Indexes add storage and write cost. Check representative queries with EXPLAIN (ANALYZE, BUFFERS) and compare plans before choosing. A normal typed column may be simpler and faster if a JSON path becomes a core part of the application’s data model.
// Part 06
JSON across SQL engines
| Engine | Typical representation | Common query tools |
|---|---|---|
| PostgreSQL | json / jsonb types | ->, ->>, #>>, @>, jsonb_array_elements; GIN and expression indexes |
| MySQL | JSON column type | JSON_EXTRACT, ->, ->>, JSON_TABLE for relational row projection |
| SQL Server | JSON text commonly held in character columns | JSON_VALUE, JSON_QUERY, OPENJSON; JSON_MODIFY updates a path |
| Snowflake | VARIANT semi-structured type | PARSE_JSON, colon/dot path access, FLATTEN to expand arrays |
These are conceptual equivalents, not interchangeable syntax. SQL Server’s JSON_VALUE extracts a scalar while JSON_QUERY returns an object or array; OPENJSON projects properties into rows. MySQL’s JSON_TABLE turns a document into a typed relational projection. Snowflake stores semi-structured data in VARIANT and uses FLATTEN for arrays. Verify null handling, path behavior, type conversion, and index options in your engine’s documentation.
// Part 07
Mistakes, practice, and next steps
1. Putting relational keys inside an opaque document
If a field is used for joins, foreign keys, core constraints, or frequent reporting, model it as a typed column instead of burying it in JSON.
2. Comparing text to a number without casting
Text extraction does not make the value numeric. Cast to the expected type and account for missing or invalid values.
3. Confusing JSON null, missing key, and SQL NULL
Check each state intentionally. Missing paths commonly yield SQL NULL, while an explicit JSON null remains a JSON value.
4. Indexing the entire document without a workload
Choose a general or targeted index from actual predicates, measure query plans, and account for write overhead.
Practice tasks:
- Create a table with a JSONB object column, a default empty object, and a top-level object check.
- Extract a nested city as text, and filter for a specific partner using both a path predicate and containment.
- Expand an array of line items into one row per item, then state the resulting row grain.
- Update one nested key with
jsonb_setand explain what the create-missing flag does. - Pick one predicate and propose a GIN or expression index. Explain what you would inspect in its query plan.
🎯 Key Takeaways
- ✓Use JSON for flexible attributes, not as a substitute for core relational data design.
- ✓PostgreSQL jsonb validates and stores queryable JSON in a decomposed form; -> returns JSON and ->> returns text.
- ✓Expand arrays with a set-returning function and remember that this changes result grain.
- ✓Use jsonb_set for a path update and select indexes based on the predicates you actually run.
- ✓Missing paths, JSON null, and SQL NULL are different states.
- ✓MySQL, SQL Server, and Snowflake use different JSON types, path syntax, and row-expansion functions.
Next
Next, combine ordering and window functions to detect consecutive runs, missing periods, and other classic analytical patterns.
Lesson 61 → Gaps and Islands and Other Classic PatternsHELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
