Working with JSON in SQL: Store, Query, and Manipulate Semi-Structured Data

Table of Contents

1. Introduction
2. Why Use JSON in Relational Databases?
3. JSON Support Across SQL Dialects
4. Creating Tables with JSON Columns
5. Inserting JSON Data
6. Retrieving JSON Fields
7. Accessing Nested JSON Properties
8. Modifying JSON Data (JSON_SET, jsonb_set)
9. Querying JSON Conditions
10. Validating JSON Structure
11. JSON Creation Functions (JSON_OBJECT, json_build_object)
12. Aggregating JSON Data
13. Searching Within JSON Arrays
14. Indexing JSON Columns for Performance
15. Real-World Use Case: Flexible User Profiles
16. Real-World Use Case: Product Attributes and Variants
17. Pros and Cons of JSON in SQL
18. Best Practices for JSON Storage
19. JSON and Normalization — When to Avoid It
20. Summary and What’s Next

1. Introduction

JSON (JavaScript Object Notation) is widely used for handling semi-structured data. Modern relational databases like MySQL, PostgreSQL, and SQL Server now offer native JSON support, enabling you to combine the flexibility of NoSQL with the structure of SQL.

2. Why Use JSON in Relational Databases?

• Store flexible, dynamic data structures
• Avoid excessive normalization for optional or variable fields
• Work seamlessly with frontend APIs that consume or send JSON
• Support multi-valued or nested attributes

3. JSON Support Across SQL Dialects

4. Creating Tables with JSON Columns

MySQL:

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

PostgreSQL:

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

5. Inserting JSON Data

sqlCopyEditINSERT INTO users (id, profile)
VALUES (1, '{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "gaming"]
}');

Ensure valid JSON formatting — keys in double quotes, comma-separated.

6. Retrieving JSON Fields

MySQL:

sqlCopyEditSELECT profile->'$.name' AS name FROM users;

PostgreSQL:

sqlCopyEditSELECT profile->>'name' AS name FROM users;

• -> returns JSON object
• ->> returns text/string value

7. Accessing Nested JSON Properties

MySQL:

sqlCopyEditSELECT profile->'$.address.city' FROM users;

PostgreSQL:

sqlCopyEditSELECT profile->'address'->>'city' FROM users;

8. Modifying JSON Data

MySQL:

sqlCopyEditUPDATE users
SET profile = JSON_SET(profile, '$.age', 31)
WHERE id = 1;

PostgreSQL:

sqlCopyEditUPDATE users
SET profile = jsonb_set(profile, '{age}', '31', true)
WHERE id = 1;

9. Querying JSON Conditions

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_EXTRACT(profile, '$.age') > 25;

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE (profile->>'age')::int > 25;

10. Validating JSON Structure

MySQL:

sqlCopyEditSELECT JSON_VALID(profile) FROM users;

PostgreSQL: Type casting to JSONB will automatically validate the content.

11. JSON Creation Functions

MySQL:

sqlCopyEditSELECT JSON_OBJECT('name', 'John', 'age', 25);

PostgreSQL:

sqlCopyEditSELECT jsonb_build_object('name', 'John', 'age', 25);

Also supports jsonb_build_array(), json_agg(), etc.

12. Aggregating JSON Data

MySQL:

sqlCopyEditSELECT JSON_ARRAYAGG(name) FROM users;

PostgreSQL:

sqlCopyEditSELECT json_agg(name) FROM users;

Great for building APIs that return a single JSON array.

13. Searching Within JSON Arrays

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_CONTAINS(profile->'$.hobbies', '["reading"]');

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE 'reading' = ANY (jsonb_array_elements_text(profile->'hobbies'));

14. Indexing JSON Columns for Performance

PostgreSQL:

sqlCopyEditCREATE INDEX idx_profile_gin ON users USING GIN (profile);

MySQL:

sqlCopyEditCREATE INDEX idx_json_name ON users ((profile->>'$.name'));

Index only what you query often — not the entire JSON blob.

15. Real-World Use Case: Flexible User Profiles

jsonCopyEdit{
  "first_name": "Jane",
  "settings": {
    "theme": "dark",
    "notifications": true
  },
  "languages": ["English", "Hindi"]
}

Helps store optional or rarely changing user settings in one field.

16. Real-World Use Case: Product Attributes and Variants

jsonCopyEdit{
  "color": "blue",
  "size": "L",
  "tags": ["organic", "handmade"],
  "dimensions": {
    "height": 20,
    "width": 15
  }
}

Used in e-commerce platforms to handle customizable and dynamic fields without creating multiple columns.

17. Pros and Cons of JSON in SQL

✅ Pros:

• Schema flexibility
• Fast API development
• Easier integration with frontend
• Nested data support

❌ Cons:

• Harder to validate and constrain
• Joins and searches can be slower
• Limited cross-field querying
• Increases complexity when overused

18. Best Practices for JSON Storage

• Use JSON for semi-structured, optional, or nested data
• Normalize core business entities
• Index JSON expressions used in filters or joins
• Use CHECK (JSON_VALID(column)) if supported
• Avoid deeply nested structures in frequently queried data

19. JSON and Normalization — When to Avoid It

Avoid JSON when:

• You need strict relational integrity
• Data must be filtered/joined frequently
• You rely heavily on GROUP BY, JOIN, or ORDER BY on inner fields
• Field-specific indexing is critical for performance

20. Summary and What’s Next

SQL support for JSON allows you to combine structured and semi-structured data, enabling more flexibility without leaving the relational world. Use it wisely to store evolving fields, user preferences, or API-centric objects.