Working with JSON in PostgreSQL, MySQL & SQL Server

[Lomanu4]

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

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

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.