DEV Community

Cristian Sifuentes
Cristian Sifuentes

Posted on

Working with JSON in PostgreSQL, MySQL & SQL Server

#sql #database #json #datamodeling

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
);
Enter fullscreen mode Exit fullscreen mode

MySQL: 

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile JSON
);
Enter fullscreen mode Exit fullscreen mode

SQL Server: 

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

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Store and Retrieve Entire JSON Objects

Insert full object: 

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

Query full object: 

SELECT profile FROM users;
Enter fullscreen mode Exit fullscreen mode

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%';
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

SQL Server: 

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

✅ Indexed access boosts performance in WHERE and JOIN clauses.

Output JSON from SQL

PostgreSQL: 

SELECT jsonb_build_object('id', id, 'profile', profile) FROM users;
Enter fullscreen mode Exit fullscreen mode

SQL Server: 

SELECT id, profile FROM users FOR JSON PATH;
Enter fullscreen mode Exit fullscreen mode

MySQL: 

SELECT JSON_OBJECT('id', id, 'profile', profile) FROM users;
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)