JSON vs JSONB

What Is JSON?

JSON (JavaScript Object Notation) is a lightweight, text-based data format for storing and exchanging data. It's human-readable and machine-parseable, making it the de facto "universal language" for data interchange between systems.

{
  "employee_id": "EMP-001",
  "name": "John Smith",
  "department": "Accounting",
  "skills": ["Excel", "SAP", "ERP"],
  "active": true
}

JSON uses a key-value pair structure that can be nested to multiple levels. It supports 6 basic data types:

What Is JSONB?

JSONB (JSON Binary) stores JSON data in a decomposed binary format — the same data as JSON, but stored in a way that enables much faster searching and processing.

How Do JSON and JSONB Differ?

Here's the most important comparison table:

What Are JSON and JSONB Used For?

In Saeree ERP and enterprise information systems, JSON/JSONB is used extensively:

Real SQL Examples on PostgreSQL

1. Create a Table with a JSONB Column

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    profile JSONB
);

-- Insert data
INSERT INTO employees (name, profile) VALUES
('John Smith', '{
    "department": "Accounting",
    "position": "Accountant",
    "skills": ["Excel", "SAP", "ERP"],
    "salary": 35000,
    "active": true
}'),
('Jane Doe', '{
    "department": "Procurement",
    "position": "Procurement Officer",
    "skills": ["Inventory", "ERP"],
    "salary": 30000,
    "active": true
}');

2. Extract Values from JSONB

-- Get department (returns JSON value)
SELECT name, profile->'department' AS dept
FROM employees;

-- Get department as plain text (no quotes)
SELECT name, profile->>'department' AS dept
FROM employees;

3. Search Inside JSONB

-- Find employees in the Accounting department
SELECT name FROM employees
WHERE profile->>'department' = 'Accounting';

-- Find employees with the "ERP" skill
SELECT name FROM employees
WHERE profile @> '{"skills": ["ERP"]}';

-- Find employees with salary above 32,000
SELECT name, (profile->>'salary')::int AS salary
FROM employees
WHERE (profile->>'salary')::int > 32000;

4. Update Values in JSONB

-- Change department
UPDATE employees
SET profile = jsonb_set(profile, '{department}', '"Finance"')
WHERE name = 'John Smith';

-- Add a new key
UPDATE employees
SET profile = profile || '{"phone": "02-347-7730"}'
WHERE name = 'John Smith';

-- Remove a key
UPDATE employees
SET profile = profile - 'phone'
WHERE name = 'John Smith';

5. Create Indexes for JSONB

-- GIN Index — fast search across all keys
CREATE INDEX idx_profile ON employees USING GIN (profile);

-- GIN with jsonb_path_ops — faster but only supports @>
CREATE INDEX idx_profile_path ON employees
USING GIN (profile jsonb_path_ops);

-- B-Tree index on a specific key — fastest for frequently queried keys
CREATE INDEX idx_department ON employees
((profile->>'department'));

Which PostgreSQL Versions Support JSON/JSONB?

PostgreSQL has been adding JSON capabilities since version 9.2, improving with every release:

Comparison with Other Databases

PostgreSQL isn't the only database that supports JSON — but the depth of support varies significantly:

Important Caveats When Using JSONB

While JSONB is powerful, it shouldn't be used everywhere — understand its limitations:

1. Don't replace regular columns — If data has a clear, fixed structure (name, email, date), use proper typed columns for better data validation
2. Watch the data size — Very large JSONB values can slow down backup operations
3. No foreign keys — You cannot create FK constraints referencing keys inside JSONB
4. Per-key updates — Modifying JSONB actually rewrites the entire column value (not an in-place update)

JSONB in PostgreSQL gives us the best of both worlds — the flexibility of NoSQL combined with the reliability of a relational database. In Saeree ERP, we use JSONB for workflow configuration, audit logs, and custom fields, allowing the system to stay flexible without altering table structures.

— Paitoon Butri, Server Security Specialist, Grand Linux Solution

Summary — JSON vs JSONB: Which Should You Choose?

If you're interested in an ERP system that uses PostgreSQL as its core database and takes full advantage of JSONB, schedule a demo or contact the Saeree ERP consulting team today.

References

• PostgreSQL Official Documentation — JSON Types
• PostgreSQL Official Documentation — JSON Functions and Operators
• JSON.org — Introducing JSON