JSONB in PostgreSQL: Operators, SQL/JSON Functions, GIN Indexes and When Not to Use It

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02, right after reloading the schema.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, receives book data from a supplier, and the supplier's service sometimes sends the same key twice. We cast the same text to PostgreSQL's two JSON types and checked how much space a small document takes:

SELECT '{"title": "Dune", "pages": 412, "pages": 896}'::json AS as_json,
       '{"title": "Dune", "pages": 412, "pages": 896}'::jsonb AS as_jsonb;

SELECT pg_column_size('{"format": "ebook", "pages": 412, "language": "en"}'::json) AS json_bytes,
       pg_column_size('{"format": "ebook", "pages": 412, "language": "en"}'::jsonb) AS jsonb_bytes;

Which one keeps both pages keys and which one drops one? The one that drops a key, which does it keep: the first or the last? If "binary" makes you think "smaller", how many bytes does jsonb take?

                    as_json                    |            as_jsonb
-----------------------------------------------+---------------------------------
 {"title": "Dune", "pages": 412, "pages": 896} | {"pages": 896, "title": "Dune"}

 json_bytes | jsonb_bytes
------------+-------------
         55 |          67

json stored the text as it was: both keys, the whitespace and the order are all there. jsonb parsed the text into its own structure: it kept the last of the duplicate keys and arranged the keys in its own order. And for the small document it is larger. JSONB exists for querying and indexing, not for saving disk space. In this post we look at operators, SQL/JSON functions, GIN indexes and the cases where JSONB is the wrong choice, using a JSONB column we add to the books table.

json or jsonb?

json only validates the input and stores it as text; it is parsed again on every read. jsonb is parsed once on write; key order, whitespace and duplicate keys are not preserved, but it is fast to read, has containment operators and can be indexed. jsonb is the right choice in almost every case. The only real use of json is where the incoming text must be kept byte for byte: a webhook body whose signature will be verified, or a request stored as is for auditing.

The native json type that came with SQL Server 2025 also stores data in a binary format and guarantees validity through the type itself; we saw it in the SQL Server 2025 post of the SQL Temelleri series. The differences are on the index side: at the time of writing CREATE JSON INDEX is still in preview in SQL Server 2025, while PostgreSQL's GIN index has been used in production for years.

books.attributes in Bookstore

Information that varies with a book's format is a good fit for JSONB: the page count of a printed book, the dimensions of a hardcover, the file formats and DRM status of an e-book, the duration and narrator of an audiobook. A separate column for each means a table full of NULLs in most rows, and separate tables mean a few joins on every screen. This post's script adds an attributes column with deterministic content to books:

docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/05-jsonb.sql

SELECT book_id, title, attributes FROM books WHERE book_id IN (1, 6, 8, 9) ORDER BY book_id;

 book_id |     title     |                                                 attributes
---------+---------------+------------------------------------------------------------------------------------------------------------
       1 | Emma #1       | {"pages": 388, "format": "paperback", "language": "en"}
       6 | Persuasion #6 | {"pages": 121, "format": "hardcover", "language": "en", "dimensions": {"width_mm": 151, "height_mm": 231}}
       8 | Moby-Dick #8  | {"drm": true, "pages": 302, "format": "ebook", "language": "en", "file_formats": ["epub", "pdf"]}
       9 | Jane Eyre #9  | {"format": "audiobook", "language": "en", "narrator": "Linus Torvalds", "duration_minutes": 531}

Of the 40,000 books, 20,000 are paperbacks, 8,000 e-books, 8,000 hardcovers and 4,000 audiobooks. Fields that every book has and that are queried often, such as price, title and author, stay as columns; we come back to why that split matters in the pitfalls section.

Operators: ->, ->>, #>>, @>, ? and @?

SELECT attributes -> 'pages' AS pages_jsonb, pg_typeof(attributes -> 'pages') AS t1,
       attributes ->> 'pages' AS pages_text, pg_typeof(attributes ->> 'pages') AS t2,
       attributes #>> '{dimensions,width_mm}' AS width
FROM books WHERE book_id = 6;

 pages_jsonb |  t1   | pages_text |  t2  | width
-------------+-------+------------+------+-------
 121         | jsonb | 121        | text | 151

-> returns a value as jsonb and ->> as text; for nested paths there are #> and #>>. The real power is in the filtering operators:

SELECT count(*) AS turkish_ebooks FROM books WHERE attributes @> '{"format": "ebook", "language": "tr"}';
SELECT count(*) AS with_pdf FROM books WHERE attributes -> 'file_formats' ? 'pdf';
SELECT count(*) AS has_narrator FROM books WHERE attributes ? 'narrator';
SELECT count(*) AS long_books FROM books WHERE (attributes ->> 'pages')::int > 800;
SELECT count(*) AS long_books_path FROM books WHERE attributes @? '$.pages ? (@ > 800)';

 turkish_ebooks
----------------
           1610

 with_pdf
----------
     2592

 has_narrator
--------------
         4000

 long_books
------------
        931

 long_books_path
-----------------
             931

@> (containment) asks "is the document on the right contained in the one on the left" and compares several fields at once. ? checks for the presence of a key, or of a string in an array. @? checks whether a jsonpath expression produces a result; jsonpath is the path language the SQL standard defines for JSON, and we use it in the SQL/JSON functions below as well. The last two queries return the same result; we see the difference in the indexing section.

Changing a Document: jsonb_set, || and -

BEGIN;
UPDATE books SET attributes = jsonb_set(attributes, '{pages}', '420') WHERE book_id = 1
RETURNING old.attributes AS before, new.attributes AS after;
UPDATE books SET attributes = attributes || '{"signed": true}' WHERE book_id = 1 RETURNING new.attributes;
UPDATE books SET attributes = attributes - 'signed' WHERE book_id = 1 RETURNING new.attributes;
ROLLBACK;

                         before                          |                          after
---------------------------------------------------------+---------------------------------------------------------
 {"pages": 388, "format": "paperback", "language": "en"} | {"pages": 420, "format": "paperback", "language": "en"}

                               attributes
-------------------------------------------------------------------------
 {"pages": 420, "format": "paperback", "signed": true, "language": "en"}

                       attributes
---------------------------------------------------------
 {"pages": 420, "format": "paperback", "language": "en"}

jsonb_set replaces the value at a path, || merges two documents (the right side wins on the same key), - removes a key. Although the syntax looks like "change only pages", PostgreSQL produces a new document every time and rewrites the whole row; that's how a row update works under MVCC anyway, and we measure the cost for large documents in the production section.

SQL/JSON: JSON_VALUE, JSON_QUERY, JSON_EXISTS and JSON_TABLE

Besides PostgreSQL's own operators there are the SQL/JSON functions of the SQL standard: JSON_EXISTS, JSON_VALUE and JSON_QUERY arrived in 17. The names will be familiar to anyone coming from SQL Server:

SELECT JSON_VALUE(attributes, '$.pages' RETURNING int) AS pages,
       JSON_QUERY(attributes, '$.file_formats') AS formats,
       JSON_EXISTS(attributes, '$.drm ? (@ == true)') AS has_drm,
       JSON_VALUE(attributes, '$.narrator' DEFAULT 'n/a' ON EMPTY) AS narrator
FROM books WHERE book_id IN (8, 9) ORDER BY book_id;

 pages |     formats     | has_drm |    narrator
-------+-----------------+---------+----------------
   302 | ["epub", "pdf"] | t       | n/a
       |                 | f       | Linus Torvalds

JSON_VALUE returns a scalar converted to the requested type, JSON_QUERY returns an object or array, JSON_EXISTS a boolean. Error handling follows the standard too: the default "lax" mode returns NULL instead of an error for a value that can't be converted.

SELECT JSON_VALUE('{"pages": "many"}'::jsonb, '$.pages' RETURNING int) AS pages;
SELECT JSON_VALUE('{"pages": "many"}'::jsonb, '$.pages' RETURNING int ERROR ON ERROR) AS pages;
SELECT ('{"pages": "many"}'::jsonb ->> 'pages')::int AS pages;

 pages
-------


ERROR:  invalid input syntax for type integer: "many"
ERROR:  invalid input syntax for type integer: "many"

The default behavior helps if you don't want a single broken document to bring down a whole report query; in a query that checks data quality you want to see the error with ERROR ON ERROR. ->> with a cast always raises the error.

JSON_TABLE, added in 17, opens a document up into rows and columns; it is the standard counterpart of SQL Server's OPENJSON ... WITH. With NESTED PATH, array elements become separate rows:

SELECT b.book_id, b.title, jt.*
FROM books AS b,
     JSON_TABLE(b.attributes, '$'
         COLUMNS (
             format  text PATH '$.format',
             pages   int  PATH '$.pages',
             minutes int  PATH '$.duration_minutes',
             NESTED PATH '$.file_formats[*]' COLUMNS (file_format text PATH '$')
         )) AS jt
WHERE b.book_id BETWEEN 6 AND 9
ORDER BY b.book_id;

 book_id |        title        |  format   | pages | minutes | file_format
---------+---------------------+-----------+-------+---------+-------------
       6 | Persuasion #6       | hardcover |   121 |         |
       7 | The Time Machine #7 | ebook     |   694 |         | epub
       8 | Moby-Dick #8        | ebook     |   302 |         | epub
       8 | Moby-Dick #8        | ebook     |   302 |         | pdf
       9 | Jane Eyre #9        | audiobook |       |     531 |

Indexing: GIN jsonb_ops, jsonb_path_ops and Expression Indexes

JSONB has two GIN operator classes. The default jsonb_ops indexes every key and value separately; jsonb_path_ops only stores a hash of each "path plus value" pair. We built both on the same column and compared their sizes:

CREATE INDEX ix_books_attributes_gin ON books USING gin (attributes);
CREATE INDEX ix_books_attributes_path ON books USING gin (attributes jsonb_path_ops);

       indexrelname       |  size
--------------------------+--------
 ix_books_attributes_gin  | 832 kB
 ix_books_attributes_path | 584 kB

Then, keeping only one at a time, we looked at the plans of the same queries. With jsonb_ops:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM books WHERE attributes @> '{"format": "ebook", "language": "tr"}';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM books WHERE attributes ? 'narrator';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM books WHERE attributes ->> 'format' = 'audiobook';

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=1610.00 loops=1)
         Recheck Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
         Heap Blocks: exact=792
         ->  Bitmap Index Scan on ix_books_attributes_gin (actual rows=1610.00 loops=1)
               Index Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
               Index Searches: 1

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=4000.00 loops=1)
         Recheck Cond: (attributes ? 'narrator'::text)
         Heap Blocks: exact=923
         ->  Bitmap Index Scan on ix_books_attributes_gin (actual rows=4000.00 loops=1)
               Index Cond: (attributes ? 'narrator'::text)
               Index Searches: 1

 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on books (actual rows=4000.00 loops=1)
         Filter: ((attributes ->> 'format'::text) = 'audiobook'::text)
         Rows Removed by Filter: 36000

With jsonb_path_ops the same containment query still used the index, but the ? query fell back to a Seq Scan:

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=1610.00 loops=1)
         Recheck Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
         Heap Blocks: exact=792
         ->  Bitmap Index Scan on ix_books_attributes_path (actual rows=1610.00 loops=1)
               Index Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
               Index Searches: 1

 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on books (actual rows=4000.00 loops=1)
         Filter: (attributes ? 'narrator'::text)
         Rows Removed by Filter: 36000

In short: GIN works by operator, not by column. Three rules are enough:

When queries filter one field by equality or range, the best option is a B-tree index on that expression:

CREATE INDEX ix_books_format ON books ((attributes ->> 'format'));
CREATE INDEX ix_books_pages ON books (((attributes ->> 'pages')::int));

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=931.00 loops=1)
         Recheck Cond: (((attributes ->> 'pages'::text))::integer > 800)
         Heap Blocks: exact=572
         ->  Bitmap Index Scan on ix_books_pages (actual rows=931.00 loops=1)
               Index Cond: (((attributes ->> 'pages'::text))::integer > 800)
               Index Searches: 1

  indexrelname   |  size
-----------------+--------
 ix_books_format | 296 kB
 ix_books_pages  | 296 kB

For an expression index to match a query, the expression in the query has to be exactly the one in the index definition: an index defined on (attributes ->> 'pages')::int doesn't help the query attributes @? '$.pages ? (@ > 800)'. A general comparison of index types is the subject of the next post.

In Production

Large documents: once a row exceeds about 2 KB, large values are compressed first and moved to the TOAST table if needed. With JSONB, updating part of a document means rewriting the whole value. On a document with 2,000 reviews we incremented a single counter and compared the WAL it produced with incrementing the same counter in a normal column:

CREATE TABLE book_pages (book_id int PRIMARY KEY, view_count int NOT NULL DEFAULT 0, doc jsonb NOT NULL);
INSERT INTO book_pages (book_id, doc)
SELECT 1, jsonb_build_object(
    'view_count', 0,
    'reviews', (SELECT jsonb_agg(jsonb_build_object('customer_id', g, 'rating', 1 + g % 5,
                       'text', 'Review ' || g || ' ' || md5(g::text) || md5((g * 7)::text)))
                FROM generate_series(1, 2000) AS g));
SELECT pg_column_size(doc) AS stored_bytes, octet_length(doc::text) AS text_bytes,
       pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_size
FROM book_pages, pg_class WHERE pg_class.oid = 'book_pages'::regclass;

SELECT pg_current_wal_lsn() AS l0 \gset
UPDATE book_pages SET doc = jsonb_set(doc, '{view_count}', to_jsonb((doc ->> 'view_count')::int + 1)) WHERE book_id = 1;
SELECT pg_current_wal_lsn() AS l1 \gset
UPDATE book_pages SET view_count = view_count + 1 WHERE book_id = 1;
SELECT pg_current_wal_lsn() AS l2 \gset
SELECT pg_size_pretty(pg_wal_lsn_diff(:'l1', :'l0')) AS wal_jsonb_set,
       pg_size_pretty(pg_wal_lsn_diff(:'l2', :'l1')) AS wal_plain_column;
SELECT pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_size_after FROM pg_class WHERE oid = 'book_pages'::regclass;

 stored_bytes | text_bytes | toast_size
--------------+------------+------------
       155392 |     245816 | 160 kB

 wal_jsonb_set | wal_plain_column
---------------+------------------
 175 kB        | 168 bytes

 toast_size_after
------------------
 312 kB

Changing one number produced 175 kB of WAL and the TOAST table grew from 160 kB to 312 kB (the old version stays until VACUUM); the same job in a normal column took 168 bytes. Don't put frequently changing fields (counters, status, last seen time) inside a large document; keep them as columns.

Statistics: the planner doesn't know what is inside a JSONB column. For attributes ->> 'format' = 'audiobook' it estimated 200 rows (the default 0.5% selectivity) against an actual 4,000:

EXPLAIN SELECT count(*) FROM books WHERE attributes ->> 'format' = 'audiobook';
CREATE STATISTICS st_books_format ON (attributes ->> 'format') FROM books;
ANALYZE books;
EXPLAIN SELECT count(*) FROM books WHERE attributes ->> 'format' = 'audiobook';

   ->  Seq Scan on books  (cost=0.00..1966.00 rows=200 width=0)
...
   ->  Seq Scan on books  (cost=0.00..1966.00 rows=3915 width=0)

With statistics on the expression (since PostgreSQL 14 CREATE STATISTICS accepts expressions), the estimate moved close to reality; since ANALYZE samples, the number changes slightly every time. An expression index collects the same statistics automatically. This gap leads to the wrong join algorithm when the JSONB field takes part in joins with other tables; we read EXPLAIN in detail in post 07.

Schema validation: PostgreSQL has no built-in JSON Schema validator. Check required fields and types with constraints such as CHECK (attributes ? 'format' AND jsonb_typeof(attributes -> 'pages') IN ('number', 'null')) or in the application layer; otherwise one day "pages": "many" arrives.

Common Pitfalls

Putting everything into JSONB: moving fields such as price, stock or author, which every row has, which take part in joins and which want foreign keys, into the document means giving up the constraints, types and statistics the relational model provides. It is the JSON version of the EAV problem we covered in the modeling anti-patterns post of the Data Modeling ve Normalizasyon series. The rule: information whose structure varies from row to row, that is rarely filtered and has no meaning on its own goes into JSONB; everything else becomes a column.

Numeric comparison with ->>: ->> returns text, and text comparison is alphabetical.

SELECT '{"pages": 90}'::jsonb ->> 'pages' > '800' AS text_compare,
       ('{"pages": 90}'::jsonb ->> 'pages')::int > 800 AS int_compare;
SELECT count(*) AS pages_as_string FROM books WHERE attributes @> '{"pages": "388"}';
SELECT count(*) AS pages_as_number FROM books WHERE attributes @> '{"pages": 388}';

 text_compare | int_compare
--------------+-------------
 t            | f

 pages_as_string
-----------------
               0

 pages_as_number
-----------------
              56

As text, "90" is greater than "800". Types matter in containment as well: in JSON 388 is a number and "388" a string; an application that sends the parameter as a string gets zero rows without an error.

Mixing up JSON null and SQL NULL: in the document {"narrator": null}, -> returns JSON null, not SQL NULL, while ->> returns SQL NULL. "The key is missing" and "the key exists and its value is null" are different cases; say which one you mean explicitly with ?.

A Small Experiment

★☆☆ Using JSON_TABLE, write a query that computes the average page count per format and the total duration of audiobooks in hours.

★★☆ Write the query that finds Turkish e-books in three ways: with @>, with two ->> equalities and with JSON_VALUE. First predict which index each one can use, then verify with EXPLAIN.

★★★ Add a CHECK constraint to the attributes column that makes the format key mandatory and one of four values, and pages, if present, a positive number. Add it with NOT VALID, count with a query how many of the existing 40,000 rows break the rule, then run VALIDATE CONSTRAINT.

In the next post we put all index types side by side: B-tree and 18's skip scan, GIN, GiST, SP-GiST, BRIN, Hash, partial and expression indexes, CREATE INDEX CONCURRENTLY and finding unused indexes.