Working with JSON in SQL: Storing, Querying, and Manipulating JSON Data

Table of Contents

1. Introduction
2. Why Use JSON in SQL Databases?
3. Storing JSON Data in Tables
4. JSON Data Types and Columns
5. Basic JSON Syntax
6. Inserting JSON Values
7. Retrieving JSON Data
8. Accessing JSON Fields (->, ->>, JSON_EXTRACT)
9. Modifying JSON Fields
10. Querying with JSON Conditions
11. Validating JSON with IS JSON, JSON_VALID()
12. Using JSON_OBJECT, JSON_ARRAY
13. JSON Aggregation Functions
14. Searching Inside JSON Arrays
15. Indexing JSON Columns
16. Real-World Example: Storing User Profiles
17. Real-World Example: Product Attributes
18. Limitations and Considerations
19. Best Practices for JSON in SQL
20. Summary and What’s Next

1. Introduction

JSON (JavaScript Object Notation) has become a popular format for storing semi-structured data. Many modern SQL databases now support native JSON operations, allowing developers to store, query, and manipulate JSON just like regular tabular data.

2. Why Use JSON in SQL Databases?

• Store flexible, dynamic schemas
• Embed nested or hierarchical data structures
• Exchange data easily with APIs and applications
• Avoid joining too many lookup tables for small, infrequent fields

3. Storing JSON Data in Tables

You can store JSON in:

• TEXT or VARCHAR columns (basic usage)
• JSON or JSONB (native types in PostgreSQL, MySQL 5.7+)

Example:

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

4. JSON Data Types and Columns

5. Basic JSON Syntax

jsonCopyEdit{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "hiking"],
  "address": { "city": "Delhi", "zip": "110001" }
}

6. Inserting JSON Values

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

Ensure valid JSON format and escape special characters if needed.

7. Retrieving JSON Data

sqlCopyEditSELECT profile FROM users WHERE id = 1;

Returns JSON string. To extract values, use access operators or functions.

8. Accessing JSON Fields

MySQL:

sqlCopyEdit-- Get field
SELECT profile->'$.name' FROM users;

-- Get scalar (text)
SELECT profile->>'$.name' FROM users;

PostgreSQL:

sqlCopyEdit-- Get JSON object
SELECT profile->'name' FROM users;

-- Get text
SELECT profile->>'name' FROM users;

9. Modifying JSON Fields

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;

10. Querying with JSON Conditions

MySQL:

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

PostgreSQL:

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

11. Validating JSON with IS JSON, JSON_VALID()

MySQL:

sqlCopyEditSELECT JSON_VALID('{"a":1}') AS is_valid; -- returns 1

You can also enforce JSON validity at the schema level with CHECK (JSON_VALID(column)).

12. Using JSON_OBJECT, JSON_ARRAY

MySQL:

sqlCopyEditSELECT JSON_OBJECT('name', 'Alice', 'age', 30);
SELECT JSON_ARRAY('reading', 'hiking');

PostgreSQL:

sqlCopyEditSELECT jsonb_build_object('name', 'Alice', 'age', 30);
SELECT jsonb_build_array('reading', 'hiking');

13. JSON Aggregation Functions

MySQL:

sqlCopyEditSELECT JSON_ARRAYAGG(name) FROM users;

PostgreSQL:

sqlCopyEditSELECT json_agg(name) FROM users;

Useful for returning arrays of values in a single JSON result.

14. Searching Inside 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'));

15. Indexing JSON Columns

• MySQL: Functional indexes on extracted fields

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

• PostgreSQL: GIN indexes on JSONB for containment searches

sqlCopyEditCREATE INDEX idx_profile ON users USING GIN (profile jsonb_path_ops);

16. Real-World Example: Storing User Profiles

sqlCopyEdit{
  "first_name": "John",
  "last_name": "Doe",
  "settings": {
    "theme": "dark",
    "notifications": true
  }
}

This eliminates the need for multiple settings columns in the table.

17. Real-World Example: Product Attributes

jsonCopyEdit{
  "color": "red",
  "size": "L",
  "features": ["waterproof", "lightweight"]
}

Used in e-commerce systems for products with dynamic attributes.

18. Limitations and Considerations

• JSON data is not normalized — harder to join
• Slower to query than structured columns unless indexed
• Constraints are harder to enforce (e.g., mandatory fields)
• Can lead to schema drift if not managed carefully

19. Best Practices for JSON in SQL

• Use JSON for flexible or semi-structured data
• Always validate JSON inputs
• Combine JSON with relational columns when appropriate
• Create indexes on frequently queried fields
• Avoid overloading logic inside JSON — keep it readable and minimal

20. Summary and What’s Next

JSON support in SQL lets you handle semi-structured, flexible data inside a relational schema. With native functions and indexing, it’s now easier than ever to integrate JSON workflows into traditional SQL applications.