PostgreSQL JSON and JSONB

PostgreSQL provides robust support for storing, querying, and manipulating JSON data using the JSON and JSONB data types. These allow developers to handle semi-structured data efficiently within a relational database.

1. JSON vs JSONB

• JSON: Stores data as text, preserving exact formatting. Slower for queries, but keeps original input.
• JSONB: Stores data in a binary format, faster for indexing and querying, but may reorder keys.

2. Creating a Table with JSON Column

CREATE TABLE employees_info (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    data JSONB
);

3. Inserting JSON Data

INSERT INTO employees_info(name, data)
VALUES 
('John Doe', '{"department": "IT", "skills": ["SQL", "Python"], "experience": 5}'),
('Alice Smith', '{"department": "HR", "skills": ["Recruitment", "Payroll"], "experience": 7}');

4. Querying JSON Data

Use the -> operator to get JSON object fields, and ->> to get text values:

-- Get department of John Doe
SELECT data->>'department' AS department
FROM employees_info
WHERE name = 'John Doe';

-- Get skills array
SELECT data->'skills' AS skills
FROM employees_info
WHERE name = 'Alice Smith';

5. JSONB Operators and Functions

• data @> : Check if JSON contains specific key/value.
• data -> 'key': Extract JSON object.
• data ->> 'key': Extract JSON text value.
• jsonb_array_elements(data->'key'): Expand JSON array into rows.

-- Find employees with skill SQL
SELECT name
FROM employees_info
WHERE data->'skills' @> '["SQL"]';

6. Updating JSON Data

-- Update experience
UPDATE employees_info
SET data = jsonb_set(data, '{{experience}}', '6')
WHERE name = 'John Doe';

-- Add a new skill
UPDATE employees_info
SET data = jsonb_set(data, '{{skills}}', (data->'skills') || '"Django"')
WHERE name = 'John Doe';

7. Indexing JSONB Data

To improve performance, especially on large tables, create indexes on JSONB columns:

-- Create GIN index on JSONB column
CREATE INDEX idx_employees_info_data ON employees_info USING GIN (data);

8. Best Practices

• Use JSONB for most use cases because it is faster and indexable.
• Use JSON when you need to preserve exact formatting.
• Create indexes on frequently queried JSONB fields for performance.
• Keep a balance between relational and JSON structures to maintain clarity.
• Use PostgreSQL functions for JSON manipulation to reduce application-side processing.

Conclusion

PostgreSQL JSON and JSONB types allow you to combine the flexibility of NoSQL with the power of relational databases. With operators, functions, and indexing, you can store, query, and manipulate complex JSON data efficiently. In the next tutorial, we will explore PostgreSQL Indexing Techniques for optimized query performance.