Advanced

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.

Est. 18–24 min Updated Suggest a fix

// 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.

💡 Note
Prerequisites: tables, constraints, 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.

PostgreSQL — define a JSONB column with a bounded document contract
CREATE TABLE delivery_orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  total_amount numeric(12, 2) NOT NULL,
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  CHECK (jsonb_typeof(attributes) = 'object')
);

INSERT INTO delivery_orders (
  order_id, customer_id, status, total_amount, attributes
)
VALUES (
  9001, 42, 'in_transit', 1249.50,
  '{"delivery":{"zone":"south","slot":"evening"},
    "partner":"courier-a"}'::jsonb
);

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.

🎯 Pro Tip
A JSON document is not a license to store arbitrary user input. Validate shape and allowed values at the application boundary, and constrain important invariants in the database where practical.

// 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.

PostgreSQL — read nested keys and filter by a JSON value
SELECT
  order_id,
  attributes -> 'delivery' AS delivery_object,
  attributes -> 'delivery' ->> 'zone' AS delivery_zone,
  attributes #>> '{delivery,slot}' AS delivery_slot
FROM delivery_orders
WHERE attributes ->> 'partner' = 'courier-a';

-- Cast extracted text when doing numeric comparisons
SELECT order_id,
       (attributes ->> 'attempt_count')::integer AS attempts
FROM delivery_orders
WHERE COALESCE((attributes ->> 'attempt_count')::integer, 0) > 1;

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.

PostgreSQL — containment and key presence
-- Orders whose document contains this nested structure
SELECT order_id
FROM delivery_orders
WHERE attributes @> '{"delivery":{"zone":"south"}}'::jsonb;

-- Top-level key exists (even if its value is JSON null)
SELECT order_id
FROM delivery_orders
WHERE attributes ? 'partner';

-- SQL NULL (missing path) is different from JSON null
SELECT order_id
FROM delivery_orders
WHERE attributes -> 'delivery' -> 'instructions' = 'null'::jsonb;

// 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.

PostgreSQL — one result row per item in a JSON array
-- Example attributes value:
-- {"items":[{"sku":"RICE-5KG","qty":2},
--           {"sku":"TEA-250G","qty":1}]}
SELECT d.order_id,
       item ->> 'sku' AS sku,
       (item ->> 'qty')::integer AS quantity
FROM delivery_orders AS d
CROSS JOIN LATERAL jsonb_array_elements(
  d.attributes -> 'items'
) AS expanded(item);

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.

PostgreSQL — set one nested value and create a missing key
UPDATE delivery_orders
SET attributes = jsonb_set(
  attributes,
  '{delivery,zone}',
  to_jsonb('central'::text),
  true
)
WHERE order_id = 9001;

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.

PostgreSQL — choose an index for the predicate you actually use
-- General-purpose index for several JSONB operators
CREATE INDEX delivery_orders_attributes_gin
  ON delivery_orders USING GIN (attributes);

-- Alternative containment-focused operator class
CREATE INDEX delivery_orders_attributes_path_gin
  ON delivery_orders USING GIN (attributes jsonb_path_ops);

-- Targeted index for a frequently filtered text path
CREATE INDEX delivery_orders_partner_idx
  ON delivery_orders ((attributes ->> 'partner'));

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

EngineTypical representationCommon query tools
PostgreSQLjson / jsonb types->, ->>, #>>, @>, jsonb_array_elements; GIN and expression indexes
MySQLJSON column typeJSON_EXTRACT, ->, ->>, JSON_TABLE for relational row projection
SQL ServerJSON text commonly held in character columnsJSON_VALUE, JSON_QUERY, OPENJSON; JSON_MODIFY updates a path
SnowflakeVARIANT semi-structured typePARSE_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.

⚠️ Important
JSON paths often return text or variant values, not the type your report expects. Cast explicitly, handle missing keys and malformed values, and test the behavior on the database engine you deploy.

// 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:

  1. Create a table with a JSONB object column, a default empty object, and a top-level object check.
  2. Extract a nested city as text, and filter for a specific partner using both a path predicate and containment.
  3. Expand an array of line items into one row per item, then state the resulting row grain.
  4. Update one nested key with jsonb_set and explain what the create-missing flag does.
  5. 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 Patterns

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...