Comparing JSON and JSONB: Pros and Cons of Each Format

One of the key differences between JSON and JSONB is the way they store data. JSON keeps your data as plain text, while JSONB stores it in binary form. That means:

• JSON saves your data exactly as you gave it. For example, if you insert {"title": "PostgreSQL", "tags": ["database", "SQL"]}, it’ll be stored as-is.
• JSONB first parses the JSON object, strips unnecessary spaces, sorts the keys, optimizes the structure, and only then saves it in binary format.

This optimization lets JSONB work with data faster, especially when you’re running complex filter, search, or sort queries.

Read and Write Performance

JSON and JSONB have different characteristics when it comes to read and write speed:

• JSON writes to the database faster, since there’s no need to parse the data before saving. But reading and processing this data is usually slower, because PostgreSQL has to parse it every time you run a query.
• JSONB takes more time to write, since it processes the data before saving. But reading, filtering, and extracting values is way faster, making JSONB the go-to for analytics or search-heavy tasks.

Here’s a quick example:

-- Creating tables with JSON and JSONB
CREATE TABLE json_example (data JSON);
CREATE TABLE jsonb_example (data JSONB);

-- Inserting data
INSERT INTO json_example VALUES ('{"key": "value", "tags": ["json", "example"]}');
INSERT INTO jsonb_example VALUES ('{"key": "value", "tags": ["jsonb", "example"]}');

-- Filtering data
SELECT * FROM json_example WHERE data->'key' = '"value"';  -- Slower
SELECT * FROM jsonb_example WHERE data->'key' = '"value"'; -- Faster

Data Indexing

One of the biggest advantages of JSONB is index support. PostgreSQL lets you create indexes on JSONB columns using the GIN index type. This seriously speeds up searching and filtering.

Example of creating an index:

-- Index on a JSONB column
CREATE INDEX idx_jsonb_tags ON jsonb_example USING gin (data->'tags');

JSON, on the other hand, doesn’t support indexing, which makes it less suitable for complex search and filter operations.

When to Use JSON and When to Use JSONB

JSON is best when:

• You need to store data in its original, untouched form.
• You don’t plan to run a lot of filter, search, or sort queries on the JSON data.
• Your JSON data is mostly used to pass to other systems or clients without changes (like sending it to the frontend via API).

Example:

-- Storing JSON without parsing
CREATE TABLE api_responses (
    id SERIAL PRIMARY KEY,
    response JSON
);

-- Inserting data as received from API
INSERT INTO api_responses (response)
VALUES ('{"status": "success", "payload": {"id": 123, "name": "John"}}');

You should use JSONB if:

• You plan to actively filter, search, group, or sort data by values inside the JSON.
• Read performance is more important than write performance.
• You want to use indexing to speed up operations with JSON data.

Example:

-- Storing JSONB for analysis and working with nested objects
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

-- Inserting data
INSERT INTO products (details)
VALUES ('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computers"]}');

-- Finding products with the "electronics" tag
SELECT * FROM products
WHERE details @> '{"tags": ["electronics"]}';

You’ll learn more about the @> and --> operators in the next lectures :P

Examples of Working with JSON and JSONB

Let’s create two tables: one with a JSON column, the other with JSONB:

CREATE TABLE json_table (
    id SERIAL PRIMARY KEY,
    data JSON
);

CREATE TABLE jsonb_table (
    id SERIAL PRIMARY KEY,
    data JSONB
);

Inserting data is the same for both tables:

INSERT INTO json_table (data)
VALUES ('{"key": "value", "tags": ["json", "example"]}');

INSERT INTO jsonb_table (data)
VALUES ('{"key": "value", "tags": ["jsonb", "example"]}');

Now let’s try to extract rows where the key has the value value:

-- For JSON
SELECT * FROM json_table
WHERE data->>'key' = 'value';

-- For JSONB
SELECT * FROM jsonb_table
WHERE data->>'key' = 'value';

For small amounts of data, the performance difference is barely noticeable. But when you’re working with millions of rows, JSONB will show much better results, especially if you create an index:

CREATE INDEX idx_jsonb_key ON jsonb_table USING gin ((data->>'key'));

Working with nested objects and arrays is also more efficient in JSONB:

-- Extracting a value from an array
SELECT data->'tags'->>0 AS first_tag 
FROM jsonb_table;

Pros and Cons

Choosing between JSON and JSONB depends on your use case. If you just need to store data in plain text without changes, go with JSON. But if you plan to actively work with the data inside columns, filter, search, or group it, JSONB will be more efficient and convenient.

For complex apps where JSON data isn’t just “storage” but is actively used in analytics, JSONB is a must-have tool. So if you’re not sure — pick JSONB. Your future “fastest query ever” might just thank you for it!

2

Task

SQL SELF, level 33, lesson 1

Locked

Inserting and Retrieving Data from JSON and JSONB

Inserting and Retrieving Data from JSON and JSONB

Open