Draft:JSONB

• Comment: Almost entirely cited with primary sources. mwwv ^converse∫_edits 20:26, 1 March 2025 (UTC)

---

JSONB is a data type for storing JSON data in a more efficient binary form, which appeared in the PostgreSQL DBMS starting with version 9.4.

This type of data and work with it resembles the usual JSON data type, however, its data is not stored in text, but stored in binary form for quick search on the content of such data.

JSONB application

The problem of record search performance is very relevant for relational databases used in E-commerce. E-commerce goods and their attributes are often added/changed, new types of goods are added with a new data structure, which forces the use of a special data model.

The standard data model suitable for this subject area is called EAV (Entity-Attribute-Value), the essence of which is that instead of storing the attribute value in a column, the value is stored in a row. However, the low speed of searching records in the EAV model does not allow using it in its pure form. If attribute-value pairs from the EAV model are written in the JSONB field, this will allow a quick search and this will solve the problem . That is why the relational DBMS PostgreSQL required such a data type as JSONB, which allows indexing of JSON data and effective searching in these data.

JSONB indexing

Two index options exist for JSONB , for example:

CREATE INDEX idxgin ON entity USING GIN (attribute_value);

CREATE INDEX idxginp ON entity USING GIN (attribute_value jsonb_path_ops);

where "entity" is a table of entities, and "attribute_value" is a JSONB field that contains attribute-value pairs.

The second index is much more effective for queries with the condition "attribute1" = "value1". An example of the query (the idxginp index is used):

SELECT * FROM entity WHERE attribute_value @> '{"attribute1": "value1"}';

The disadvantage of such indexing is that only @>, @? and @@ operators is supported.

Search performance

Data search performance in JSONB of ​​PostgreSQL can be compared with data search performance in NoSQL databases. For example, searching for one record from 10 millions with a JSONB field can take faster than 1 ms.

JSONB advantages and disadvantages

Using JSONB provides the following advantages:

1. The PostgreSQL relational database ensures the reliability of data storage using the ACID principles (atomicity, consistency, isolation, and durability) and at the same time it will allow to work effectively with unstructured data stored in JSONB.
2. If unstructured JSON data is required on an existing project, which usually already has a relational database such as PostgreSQL, then additional infrastructure for a NoSQL database is not needed, but JSONB must be used.
3. Data synchronization between the main relational PostgreSQL database and NoSQL database for efficient work with unstructured data is not necessary if JSONB is used. Thus, the time and costs for project development and support are reduced.

The disadvantage of JSONB is:

1. Only the @> operator ensures effective search in JSONB data.