Working with JSON in PostgreSQL, MySQL & SQL Server

Working with JSON in PostgreSQL, MySQL & SQL Server "Your data isn't always flat — your queries shouldn't be either." SQL databases have evolved to support semi-structured data, especially JSON, alongside traditional relational models. This hybrid approach lets you: Store rich nested data Adapt to evolving schemas Join structured and flexible data together In this article, we’ll cover: JSON column types Querying nested structures Indexing for performance Cross-database examples in PostgreSQL, MySQL, and SQL Server Define JSON Columns PostgreSQL: CREATE TABLE users ( id SERIAL PRIMARY KEY, profile JSONB ); MySQL: CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, profile JSON ); SQL Server: CREATE TABLE users ( id INT IDENTITY PRIMARY KEY, profile NVARCHAR(MAX) -- must be valid JSON ); Extracting JSON Values PostgreSQL: -- Extract scalar field SELECT profile->>'name' AS name FROM users; -- Extract nested object SELECT profile->'address'->>'city' AS city FROM users; MySQL: -- Extract with JSON_EXTRACT SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name FROM users; -- Nested access SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.address.city')) AS city FROM users; SQL Server: -- Extract scalar field SELECT JSON_VALUE(profile, '$.name') AS name FROM users; -- Extract nested object SELECT JSON_VALUE(profile, '$.address.city') AS city FROM users; Store and Retrieve Entire JSON Objects Insert full object: INSERT INTO users (profile) VALUES ('{"name": "Ada", "skills": ["SQL", "Python"]}'); Query full object: SELECT profile FROM users; Use JSON in WHERE Clauses -- Get users with SQL skill -- PostgreSQL SELECT * FROM users WHERE profile->'skills' ? 'SQL'; -- MySQL SELECT * FROM users WHERE JSON_CONTAINS(profile->'$.skills', '"SQL"'); -- SQL Server SELECT * FROM users WHERE JSON_QUERY(profile, '$.skills') LIKE '%SQL%'; Indexing JSON Data PostgreSQL (JSONB only): -- Index top-level key CREATE INDEX idx_profile_name ON users ((profile->>'name')); -- Full GIN index CREATE INDEX idx_profile_json ON users USING GIN (profile); MySQL: -- Generated column (MySQL 5.7+) ALTER TABLE users ADD name_gen VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name'))) STORED; CREATE INDEX idx_name_gen ON users(name_gen); SQL Server: -- Create computed column ALTER TABLE users ADD name AS JSON_VALUE(profile, '$.name'); CREATE INDEX idx_name ON users(name); ✅ Indexed access boosts performance in WHERE and JOIN clauses. Output JSON from SQL PostgreSQL: SELECT jsonb_build_object('id', id, 'profile', profile) FROM users; SQL Server: SELECT id, profile FROM users FOR JSON PATH; MySQL: SELECT JSON_OBJECT('id', id, 'profile', profile) FROM users; Use Cases for JSON in SQL Dynamic user profiles Event logs IoT sensor payloads Configuration blobs Integration with APIs Final Thoughts: JSON Isn’t Just for NoSQL With modern JSON support, SQL databases let you: Stay flexible Model nested or sparse data Use SQL’s power on semi-structured information “The best of both worlds: query JSON with the reliability of SQL.” #SQL #JSON #PostgreSQL #MySQL #SQLServer #SemiStructured #AdvancedSQL #DataEngineering

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSONB
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile JSON
);

CREATE TABLE users (
  id INT IDENTITY PRIMARY KEY,
  profile NVARCHAR(MAX) -- must be valid JSON
);

-- Extract scalar field
SELECT profile->>'name' AS name FROM users;

-- Extract nested object
SELECT profile->'address'->>'city' AS city FROM users;

-- Extract with JSON_EXTRACT
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name FROM users;

-- Nested access
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.address.city')) AS city FROM users;

-- Extract scalar field
SELECT JSON_VALUE(profile, '$.name') AS name FROM users;

-- Extract nested object
SELECT JSON_VALUE(profile, '$.address.city') AS city FROM users;

INSERT INTO users (profile)
VALUES ('{"name": "Ada", "skills": ["SQL", "Python"]}');

SELECT profile FROM users;

-- Get users with SQL skill
-- PostgreSQL
SELECT * FROM users WHERE profile->'skills' ? 'SQL';

-- MySQL
SELECT * FROM users WHERE JSON_CONTAINS(profile->'$.skills', '"SQL"');

-- SQL Server
SELECT * FROM users WHERE JSON_QUERY(profile, '$.skills') LIKE '%SQL%';

-- Index top-level key
CREATE INDEX idx_profile_name ON users ((profile->>'name'));

-- Full GIN index
CREATE INDEX idx_profile_json ON users USING GIN (profile);

-- Generated column (MySQL 5.7+)
ALTER TABLE users ADD name_gen VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name'))) STORED;
CREATE INDEX idx_name_gen ON users(name_gen);

-- Create computed column
ALTER TABLE users ADD name AS JSON_VALUE(profile, '$.name');
CREATE INDEX idx_name ON users(name);

SELECT jsonb_build_object('id', id, 'profile', profile) FROM users;

SELECT id, profile FROM users FOR JSON PATH;

SELECT JSON_OBJECT('id', id, 'profile', profile) FROM users;

Related Posts

I've been using Docker and specifically Docker Compose ...

May 9, 2025  0

SonicBoom Attack: Hackers Bypass Authentication and Gai...

May 6, 2025  0

Decentralized AI: The Blueprint for a Safer, Smarter, a...

May 6, 2025  0