SQL 94 🛢️ Working with JSON Data in Relational Databases - KandZ

⏱️ Estimated read: 12 min | |

Relational databases were built for structured, tabular data. JSON is semi-structured, schema-flexible, and often nested. For years, the two existed in separate worlds: you either modeled everything relationally or you stored JSON as an opaque string in a text column and parsed it in application code. That changed when every major engine introduced native JSON support. Today, PostgreSQL, MySQL, SQL Server, and Oracle all store JSON in validated, optimized formats, query it with path expressions, and index it for performance.

The reason this matters is not that JSON replaces relational modeling. It does not. The reason is that some data genuinely does not fit a fixed schema: user preferences that vary by feature, product attributes that differ by category, event payloads from external APIs, or configuration blobs that change faster than you want to run migrations. Native JSON support lets you store that data alongside your relational tables, query it with SQL, and join it to structured data when needed. The trade-off is that JSON queries are often slower than column queries, indexing requires deliberate effort, and the flexibility can become a trap if you use JSON for data that should have been normalized.

Key point: Native JSON types store validated, binary-encoded documents that can be queried with path expressions and indexed; use JSON for genuinely schema-flexible data, but normalize anything you need to filter, join, or aggregate frequently.

Why native JSON support exists

The text-blob problem. Before native JSON types, the only way to store JSON was as TEXT, VARCHAR, or NVARCHAR(MAX). The database did not validate it, did not understand its structure, and could not index it. Every query that needed a value from inside the document had to retrieve the entire string, parse it in the application, and filter there. This was slow and made JSON data a second-class citizen .

The validation problem. A native JSON type validates on insert. If you try to store malformed JSON, the database rejects it. This catches errors at write time rather than at read time, when the data has already been consumed by downstream systems .

The indexing problem. Native JSON types support specialized indexes—GIN indexes in PostgreSQL, functional indexes on generated columns in MySQL, computed columns in SQL Server—that make path queries fast. Without these, every query would scan the entire table .

The query problem. Native support means you can use SQL to extract, filter, and transform JSON data without leaving the database. JSON_VALUE in SQL Server, ->> in MySQL and PostgreSQL, and json_value in Oracle all extract scalar values from JSON paths. JSON_TABLE and OPENJSON shred arrays into rows, letting you join JSON data to relational tables .

The storage trade-off. JSON documents are not free. They consume more space than equivalent relational columns, especially when keys are repeated across rows. Updates to a JSON document lock the entire row, so very large documents can cause contention . The rule is to keep JSON documents small and atomic—store the flexible attributes, not the entire record.

a. PostgreSQL: `json` vs `jsonb`

PostgreSQL offers two JSON types: json and jsonb. Both accept the same input and validate it, but they store it differently. json stores an exact copy of the input text, preserving whitespace, key order, and duplicate keys. jsonb stores a decomposed binary representation that does not preserve whitespace or key order but supports indexing and is faster to query .

For almost all applications, jsonb is the correct choice. The only reason to use json is if you need to preserve the exact input formatting, such as when you are storing a document for legal or audit purposes and the byte-for-byte representation matters .

Querying jsonb uses operators: -> returns a JSON value, ->> returns text. A subscripting syntax also exists: data['key'] or data['a']['b'] .

SELECT data->>'email' FROM customers WHERE data->>'status' = 'active';
SELECT data['address']['city'] FROM customers;

jsonb supports containment queries with the @> operator, which is one of its most powerful features. data @> '{"tags": ["premium"]}' returns true if the document contains that structure . This is what GIN indexes accelerate. The default GIN operator class supports key-existence and containment queries; the jsonb_path_ops operator class supports only containment but produces a smaller and faster index .

Indexing a jsonb column:

CREATE INDEX idx_data_gin ON customers USING GIN (data);
CREATE INDEX idx_data_path ON customers USING GIN (data jsonb_path_ops);

b. MySQL: `JSON` type and `JSON_TABLE`

MySQL has had a native JSON type since 5.7, stored in a binary format and validated on insert. The -> operator returns a JSON value, and ->> returns an unquoted scalar .

SELECT data->>'$.email' FROM customers;
SELECT * FROM customers WHERE data->>'$.status' = 'active';

The more performant approach for filtering is JSON_VALUE with an explicit RETURNING type, available in MySQL 8.0.21+. It returns a typed SQL scalar that pairs cleanly with functional indexes .

SELECT * FROM customers WHERE JSON_VALUE(data, '$.email' RETURNING CHAR(100)) = 'a@b.com';

JSON_TABLE is the feature that makes MySQL viable for document-shaped data. It projects a JSON array into a relational rowset, which you can join, group, and aggregate .

SELECT o.id, jt.sku, jt.qty
FROM orders o,
     JSON_TABLE(o.items, '$[*]' COLUMNS (
       sku VARCHAR(64) PATH '$.sku',
       qty INT PATH '$.qty'
     )) AS jt
WHERE jt.qty > 1;

For indexing, MySQL does not support direct indexes on JSON columns. The pattern is a generated column that extracts the path, with an index on that column:

ALTER TABLE customers
ADD COLUMN email VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.email'))) VIRTUAL,
ADD INDEX idx_email (email);

c. SQL Server and Oracle: functions and native types

SQL Server stored JSON in NVARCHAR(MAX) with a CHECK (ISJSON(data) = 1) constraint until SQL Server 2025, when a native json type became generally available. The native type uses binary storage and supports faster path access, but accepts only objects and arrays at the top level—not bare scalars .

The core query functions are JSON_VALUE for scalars and JSON_QUERY for objects or arrays .

SELECT * FROM customers WHERE JSON_VALUE(data, '$.email') = 'a@b.com';
SELECT JSON_QUERY(data, '$.address') FROM customers;

OPENJSON shreds a JSON array or object into a rowset:

SELECT o.id, j.sku, j.qty
FROM orders o
CROSS APPLY OPENJSON(o.items)
WITH (sku VARCHAR(64) '$.sku', qty INT '$.qty') AS j;

For indexing, the portable pattern is a persisted computed column with an index. SQL Server 2025 adds CREATE JSON INDEX on the native json type, but it requires the native type and can only be built offline .

Oracle’s native JSON type uses the OSON binary format. It supports SQL/JSON functions (json_value, json_query, json_table), conditions (json_exists, is json), and a dot-notation access syntax that is more concise than the function-based approach .

SELECT c.data.email FROM customers c WHERE c.data.status = 'active';
SELECT json_value(data, '$.email') FROM customers;

Oracle recommends the native JSON type over VARCHAR2 or CLOB for JSON columns. If textual storage is used, an is json check constraint should enforce validity .

Complete Example Session

The following session builds a product catalog in PostgreSQL, stores flexible attributes in jsonb, queries them, and creates a GIN index.

-- ============================================
-- PART 1: CREATE TABLE WITH JSONB COLUMN
-- ============================================
CREATE TABLE products (
  id serial PRIMARY KEY,
  name text NOT NULL,
  price numeric NOT NULL,
  attributes jsonb NOT NULL DEFAULT '{}'
);

-- ============================================
-- PART 2: INSERT DOCUMENTS WITH DIFFERENT SHAPES
-- ============================================
INSERT INTO products (name, price, attributes) VALUES
('Laptop', 999.00, '{"color": "silver", "ram_gb": 16, "screen": "14in"}'),
('T-Shirt', 25.00, '{"color": "blue", "size": "M", "material": "cotton"}'),
('Phone', 699.00, '{"color": "black", "storage_gb": 128, "5g": true}');

-- ============================================
-- PART 3: EXTRACT VALUES WITH OPERATORS
-- ============================================
SELECT name, attributes->>'color' AS color FROM products;
-- Returns color for all products

SELECT name, (attributes->>'ram_gb')::int AS ram FROM products
WHERE attributes ? 'ram_gb';
-- Returns only products with ram_gb key

-- ============================================
-- PART 4: CONTAINMENT QUERY
-- ============================================
SELECT name FROM products
WHERE attributes @> '{"color": "blue"}';
-- Returns T-Shirt

-- ============================================
-- PART 5: SUBSCRIPTING SYNTAX
-- ============================================
SELECT name, attributes['color'] FROM products;

-- ============================================
-- PART 6: UPDATE A JSON FIELD
-- ============================================
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"red"')
WHERE name = 'T-Shirt';

-- ============================================
-- PART 7: CREATE GIN INDEX
-- ============================================
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- ============================================
-- PART 8: VERIFY INDEX USAGE
-- ============================================
EXPLAIN ANALYZE
SELECT name FROM products WHERE attributes @> '{"color": "red"}';
-- Expect Bitmap Index Scan on idx_products_attrs

-- ============================================
-- PART 9: JOIN JSON ARRAY TO ROWS
-- ============================================
INSERT INTO products (name, price, attributes) VALUES
('Bundle', 1299.00, '{"items": [{"sku": "L1", "qty": 1}, {"sku": "P1", "qty": 1}]}');

SELECT p.name, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM products p,
     jsonb_array_elements(p.attributes->'items') AS item
WHERE p.name = 'Bundle';

-- ============================================
-- PART 10: CLEANUP
-- ============================================
DROP TABLE products;

The ten parts cover table creation, inserting heterogeneous documents, extraction with operators, containment queries, subscripting, updates, GIN indexing, query plan verification, array unnesting, and cleanup. What the session demonstrates is that jsonb is not a text blob—it is queryable, indexable, and joinable data.

Quick Reference

JSON Type by Engine

Engine	Type	Storage	Validation
PostgreSQL	`json`, `jsonb`	Text (`json`), binary (`jsonb`)	Yes
MySQL	`JSON`	Binary	Yes
SQL Server	`NVARCHAR(MAX)` + `ISJSON`, native `json` (2025+)	Text or binary	Constraint or native
Oracle	`JSON`	OSON binary	Yes

Extraction Syntax

Engine	Scalar Extract	Object/Array Extract
PostgreSQL	`->>`	`->`
MySQL	`->>`	`->`
SQL Server	`JSON_VALUE`	`JSON_QUERY`
Oracle	`json_value` or dot notation	`json_query`

Indexing

Engine	Index Method
PostgreSQL	GIN on `jsonb`
MySQL	Generated column + index
SQL Server	Computed column + index; native JSON index (2025+)
Oracle	Functional index on `json_value`

When to Use JSON vs Relational

Use JSON	Use Columns
Varying attributes per row	Fixed, queryable fields
External API payloads	Frequently filtered/joined data
Sparse data with many nulls	Data requiring strict integrity

Best Practices

✅ Do This:

ALTER TABLE t ADD COLUMN attrs jsonb DEFAULT '{}';          -- ✅ jsonb over json
CREATE INDEX idx ON t USING GIN (attrs);                    -- ✅ index containment
SELECT attrs->>'key' FROM t WHERE attrs @> '{"k":"v"}';     -- ✅ containment query
ADD COLUMN email AS (JSON_UNQUOTE(JSON_EXTRACT(data,'$.e'))) -- ✅ generated column

❌ Don’t Do This:

-- store everything in one giant JSON document              -- ❌ lock contention
-- query without an index on the path                       -- ❌ full table scan
-- use json instead of jsonb in PostgreSQL                  -- ❌ no indexing
-- put frequently joined data only in JSON                  -- ❌ slow joins

Common Pitfalls

Pitfall	Why It Happens	Fix
Slow JSON queries	No index on the path	GIN or generated column index
`json` type cannot be indexed	PostgreSQL `json` has no operators	Use `jsonb`
MySQL `->` returns quoted string	`->` returns JSON, not text	Use `->>` or `JSON_VALUE`
SQL Server `JSON_VALUE` returns NULL	Path wrong or value is object/array	Use `JSON_QUERY` for objects
Large document lock contention	Document too large	Split into smaller atomic documents

Real-World Examples

1. PostgreSQL containment query

SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

2. MySQL JSON_TABLE

SELECT o.id, jt.sku FROM orders o, JSON_TABLE(o.items, '$[*]' COLUMNS (sku VARCHAR(20) PATH '$.sku')) jt;

3. SQL Server OPENJSON

SELECT * FROM OPENJSON(@json) WITH (name VARCHAR(50) '$.name');

4. Oracle dot notation

SELECT c.data.address.city FROM customers c;

5. PostgreSQL GIN index

CREATE INDEX idx ON t USING GIN (data jsonb_path_ops);

6. MySQL generated column index

ALTER TABLE t ADD COLUMN email VARCHAR(255) AS (data->>'$.email') VIRTUAL, ADD INDEX (email);

7. SQL Server computed column

ALTER TABLE t ADD email AS JSON_VALUE(data, '$.email'); CREATE INDEX idx ON t(email);

8. Update JSON field

UPDATE t SET data = jsonb_set(data, '{status}', '"active"');

9. Check key existence

SELECT * FROM t WHERE data ? 'phone';

10. Unnest JSON array

SELECT value FROM jsonb_array_elements((SELECT data->'tags' FROM t WHERE id = 1));

Visual

JSON Storage by Engine

┌──────────────────────────────────────────────┐
│  PostgreSQL                                  │
│  json  → exact text copy, no indexing        │
│  jsonb → binary, indexable, canonical        │
│                                              │
│  MySQL                                       │
│  JSON → binary, validated, JSON_TABLE        │
│                                              │
│  SQL Server                                  │
│  NVARCHAR(MAX) + ISJSON → text, no native    │
│  json (2025+) → binary, native index         │
│                                              │
│  Oracle                                      │
│  JSON → OSON binary, dot notation            │
└──────────────────────────────────────────────┘

Query Flow with Index

┌──────────────────────────────────────────────┐
│  SELECT * FROM t WHERE data @> '{"a": 1}'    │
│         │                                    │
│         ▼                                    │
│  GIN index on data                           │
│         │                                    │
│         ▼                                    │
│  Index scan finds matching documents         │
│         │                                    │
│         ▼                                    │
│  Rows returned without full table scan       │
└──────────────────────────────────────────────┘

When to Normalize

┌──────────────────────────────────────────────┐
│  QUERY FREQUENCY                             │
│  ├─ Often filtered/joined → normalize column │
│  └─ Rarely queried → JSON is fine            │
│                                              │
│  SCHEMA STABILITY                            │
│  ├─ Fixed → relational column                │
│  └─ Varies per row → JSON                    │
│                                              │
│  DATA SIZE                                   │
│  ├─ Small atomic attributes → JSON           │
│  └─ Large document → split or normalize      │
└──────────────────────────────────────────────┘

JSON_TABLE / OPENJSON Shredding

┌──────────────────────────────────────────────┐
│  JSON: {"items": [{"sku":"A","qty":1},       │
│                    {"sku":"B","qty":2}]}     │
│         │                                    │
│         ▼                                    │
│  JSON_TABLE / OPENJSON                       │
│         │                                    │
│         ▼                                    │
│  ┌─────┬─────┐                               │
│  │ sku │ qty │                               │
│  ├─────┼─────┤                               │
│  │ A   │ 1   │                               │
│  │ B   │ 2   │                               │
│  └─────┴─────┘                               │
│  Now joinable to relational tables           │
└──────────────────────────────────────────────┘

Summary

Item	Value
PostgreSQL types	`json` (text), `jsonb` (binary, indexable)
MySQL type	`JSON` (binary)
SQL Server (pre-2025)	`NVARCHAR(MAX)` + `ISJSON`
SQL Server 2025+	Native `json` type
Oracle type	`JSON` (OSON binary)
PostgreSQL extract	`->`, `->>`, subscripting
MySQL extract	`->`, `->>`, `JSON_VALUE`
SQL Server extract	`JSON_VALUE`, `JSON_QUERY`
Oracle extract	`json_value`, dot notation
PostgreSQL index	GIN on `jsonb`
MySQL index	Generated column + index
SQL Server index	Computed column + index
Array to rows	`jsonb_array_elements`, `JSON_TABLE`, `OPENJSON`

Key takeaways:

Use jsonb in PostgreSQL, not json. json preserves formatting but cannot be indexed; jsonb is almost always the right choice .
JSON is not a replacement for relational modeling. Normalize data you filter, join, or aggregate frequently. Use JSON for genuinely variable, sparse, or external data.
Indexing requires deliberate effort. PostgreSQL uses GIN; MySQL and SQL Server use generated/computed columns. Without an index, path queries scan the table.
JSON_TABLE and OPENJSON turn arrays into rows. This is what makes JSON joinable to relational data .
Validation happens at insert. A native JSON type rejects malformed documents; a text column does not unless you add a constraint .
Updates lock the entire row. Keep JSON documents small and atomic to avoid contention .

Remember: Relational databases and JSON are not opposites. Native JSON support lets you store schema-flexible data alongside structured tables, query it with SQL, and index it for performance. The key is discipline: use jsonb in PostgreSQL, index the paths you query, shred arrays with JSON_TABLE or OPENJSON when you need to join them, and normalize anything that becomes a frequent filter or join condition. JSON is a tool for flexibility, not an excuse to abandon schema design.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!