PostgreSQL JSONB Data Type Guide

PostgreSQL has supported the JSONB data type since version 9.4. JSONB (JSON Binary) is designed for efficient storage and retrieval of JSON data within PostgreSQL databases. This document provides an overview of JSON vs JSONB and covers key aspects of working with JSONB data types.

JSON vs JSONB

Storage Format

JSON: Stores JSON data as plain text, preserving the original formatting, including whitespace and key order.
JSONB: Stores JSON data in a binary format, removing whitespace and sorting keys, resulting in more compact and efficient storage.

Size

JSON: JSON data types are generally larger in size and consume more storage space.
JSONB: JSONB data types are smaller in size, consuming less storage space.

Performance

JSON: JSON data types are less efficient for indexing and querying, leading to slower query performance, especially with large datasets.
JSONB: JSONB is optimized for indexing and querying, offering significantly faster query performance, especially for read-heavy operations.

Validation

JSON: JSON data is not rigorously validated when inserted, allowing invalid JSON to be stored in a JSON column.
JSONB: JSONB data is rigorously validated when inserted, preventing invalid JSON from being stored in a JSONB column.

JSONB Column Flexibility

Each row of a JSONB column in a PostgreSQL table can contain JSON objects with different keys, making it suitable for storing semi-structured or unstructured data where the structure of each JSON object may vary.

Creating a JSONB Column

To create a column with a JSONB data type, you can use SQL like this:

CREATE TABLE your_table_name (
    id INTEGER,
    data JSONB,
    PRIMARY KEY (id)
);

Querying JSONB Data

You can perform various operations on a column containing JSONB data type:

Using -> to get a JSON array element or JSON object field by index or key.

SELECT data::jsonb -> 'name' AS name FROM your_table_name WHERE id = 1;

Using ->> to get a JSON array element or JSON object field as text by index or key.

SELECT data::jsonb ->> 'name' AS name FROM your_table_name WHERE id = 1;

Using #> to get a JSON object at the specified path.

SELECT data::jsonb #> '{address,zip}' AS zipcode FROM your_table_name WHERE id = 1004;

Using #>> to get a JSON object at the specified path as text.

SELECT data::jsonb #>> '{address,zip}' AS zipcode FROM your_table_name WHERE id = 1004;

Using ? to check if a string exists as a top-level key within the JSON value.

SELECT data::jsonb ? 'name' AS exists FROM your_table_name WHERE id = 1004;

Using ?| to check if any of the array strings exist as top-level keys.

SELECT data::jsonb ?| array['name','address'] AS exists FROM your_table_name WHERE id = 1004;

Updating the value of a key in the JSON object.

UPDATE your_table_name
SET data = jsonb_set(data, '{address,street}', '"church street"'::jsonb, true)
WHERE id = 1004;

Deleting a key from a JSON or nested JSON object.

UPDATE your_table_name
SET data = data #- '{address,street}'
WHERE id = 1004;

Searching against a key from JSON data.

SELECT * FROM your_table_name WHERE data @> '{"address": {"zip":"38890"}}';
SELECT * FROM your_table_name WHERE data @> '{"name": "Rohit"}';

Indexing JSONB Data

JSONB provides a wide array of options for indexing JSON data. Three common types of indexes are GIN, BTREE, and HASH. The choice of index type depends on the operators and queries used in your specific use case.

GIN Indexes

GIN (Generalized Inverted Indexes) is designed for indexing composite values and is useful when searching for element values within the composite items. Two common operator classes for GIN indexes are:

jsonb_ops (default): Supports operators like ?, ?|, ?&, @>, @@, @?, and indexes each key and value in the JSONB element.
jsonb_pathops: Supports operators @>, @@, and @?, indexing only the values in the JSONB element.

To create a GIN index on a JSONB column, use SQL like this:

CREATE INDEX gin_idx_data ON your_table_name USING gin(data);

Performance Considerations

When working with JSONB data, query performance can be influenced by factors such as the choice of index, the size of the dataset, and the specific queries being executed. In some cases, a sequential scan may be more efficient than using an index, particularly when a large portion of the table matches the search criteria.

Make sure to analyze and plan your indexes based on your specific use case and query patterns to achieve optimal performance.

For more in-depth details and advanced use cases, refer to the PostgreSQL documentation and consider the specific requirements of your application.

rohitnarayan/jsonb-experiment