Why JSON in SQL Databases Matters in 2026
Modern applications rarely deal with perfectly structured data. All three major SQL databases now ship with mature JSON support.
PostgreSQL JSONB
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL,
preferences JSONB
);
SELECT email, preferences->>'theme' AS theme
FROM users
WHERE preferences->>'theme' = 'dark';
GIN Indexing
CREATE INDEX idx_users_preferences ON users USING GIN (preferences);
CREATE INDEX idx_users_theme ON users ((preferences->>'theme'));
MySQL JSON
CREATE TABLE api_responses (
id INT AUTO_INCREMENT PRIMARY KEY,
response_body JSON
);
SELECT endpoint, JSON_VALUE(response_body, '$.city') AS city
FROM api_responses;
Indexing via Generated Columns
ALTER TABLE api_responses
ADD COLUMN city VARCHAR(100) GENERATED ALWAYS AS (
JSON_UNQUOTE(response_body->'$.city')
) VIRTUAL;
CREATE INDEX idx_city ON api_responses (city);
SQL Server JSON
CREATE TABLE product_catalog (
id INT IDENTITY PRIMARY KEY,
attributes NVARCHAR(MAX),
CONSTRAINT chk_valid_json CHECK (ISJSON(attributes) = 1)
);
SELECT product_name, JSON_VALUE(attributes, '$.brand') AS brand
FROM product_catalog;
OPENJSON for Arrays
SELECT order_id, amount, status
FROM OPENJSON(@orders)
WITH (order_id INT '$.order_id', amount DECIMAL(10,2) '$.amount', status NVARCHAR(50) '$.status')
WHERE status = 'pending';
JSON Columns vs. Normalized Tables
Use JSON: Structure varies per row, storing data you mostly read back as-is, rapidly prototyping
Use normalized tables: Querying/filtering/joining regularly, foreign keys matter, consistent structure
Hybrid: Stable fields as columns, variable data in a JSONB column
Practical Example: Product Attributes
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price NUMERIC(10,2),
attributes JSONB
);
SELECT name, price FROM products
WHERE category = 'electronics' AND (attributes->>'ram_gb')::INT >= 16;
Generating JSON Queries with AI2SQL
AI2SQL lets you describe what you want and get the correct JSON SQL back instantly, including the right operators for your database.
Performance Summary
PostgreSQL: Use jsonb + GIN indexes
MySQL: Use generated virtual columns + indexes
SQL Server: Use CHECK (ISJSON) + computed columns
If you want to skip the syntax lookup, try AI2SQL.
