Working With JSONB in PostgreSQL From First Query to Index

JSONB is one of the reasons people stay on PostgreSQL when a project starts to look like it needs a document store. You get a column that holds an arbitrary JSON document, operators that reach inside it, indexes that make those lookups fast, and all of it in the same transaction as the rest of your tables. It is also easy to use badly. Documents pile up in a column called data, every query casts text to numbers, and a year later nobody knows what keys exist. This guide is the one I wanted when I started: a path from the first query to the index, with the traps marked where I fell into them, and with numbers from a 200,000-row table so that the advice about speed is something you can check.

It is the hub for a group of shorter articles that go deeper on each step. They are linked where they apply: JSON versus JSONB, the arrow operators, path operators, updating one key, adding and removing keys, GIN indexing, arrays inside JSONB, expanding into rows, JSON_TABLE, aggregating rows into JSON and when JSONB is the wrong choice.

What JSONB is, in one paragraph

JSONB is a column type that stores a JSON document in a decomposed binary form. Postgres parses the text on the way in, rejects it if it is not valid JSON, and stores a tree of keys and values that can be searched without being parsed again. That has consequences you can see. Whitespace is not kept. Keys come back in a normalized order, which is by length and then by bytes, not in the order you typed them. If a key appears twice, the last value wins. Numbers are stored as numeric, so 1.50 stays 1.50 and 1e2 comes back as 100. The json type (without the b) keeps the input text exactly and re-parses it on every access. I ran one document through both:

select '{"b": 1,  "a": [1, 2],  "a": 3, "c":  null}'::json  as j,
       '{"b": 1,  "a": [1, 2],  "a": 3, "c":  null}'::jsonb as jb;

                      j                      |             jb              
---------------------------------------------+-----------------------------
 {"b": 1,  "a": [1, 2],  "a": 3, "c":  null} | {"a": 3, "b": 1, "c": null}

The first column is what you typed, duplicates and all. The second is the document as Postgres understands it. That is nearly always what you want, and it is why JSONB is the default choice. The one reason to use json is when the exact text matters, for example when you must hand back byte-for-byte what a client sent. JSON versus JSONB in PostgreSQL has the full comparison, including the NUL character that jsonb refuses.

A table to work with

Every example here runs against this table. It is a catalog: a few fixed columns, and an attrs document that holds whatever a product happens to have.

create table products (
  id    serial primary key,
  name  text  not null,
  attrs jsonb not null
);

insert into products (name, attrs)
select 'Product ' || g,
  jsonb_build_object(
    'sku',      'SKU-' || lpad(g::text, 6, '0'),
    'brand',    (array['Acme','Globex','Initech','Umbrella','Hooli'])[1 + (g % 5)],
    'price',    round((random() * 500)::numeric, 2),
    'in_stock', (g % 7 <> 0),
    'tags',     (select jsonb_agg(t) from (select (array['new','sale','eco','gift','bulk','import'])[1 + ((g * k) % 6)] as t
                  from generate_series(1, 1 + g % 3) k) s),
    'specs',    jsonb_build_object('weight_g', 100 + g % 900,
                  'color', (array['red','blue','green','black'])[1 + g % 4],
                  'dims',  jsonb_build_object('w', 10 + g % 30, 'h', 5 + g % 20))
  )
from generate_series(1, 200000) g;

analyze products;

The loaded table was 60 MB including its primary key. The first row looks like this:

{"sku": "SKU-000001", "tags": ["sale", "eco"], "brand": "Globex", "price": 371.47, "specs": {"dims": {"h": 6, "w": 11}, "color": "blue", "weight_g": 101}, "in_stock": true}

Notice the key order in the output: sku, tags, brand, price. That is the length-then-bytes order, not the order in jsonb_build_object. Do not write code that depends on key order.

Reading values

There are three ways in, and the difference between them is mostly about what type comes out.

The arrow operators take a key and return a value. -> returns jsonb, and ->> returns text:

select attrs -> 'brand'  as arrow,
       attrs ->> 'brand' as arrow_text,
       pg_typeof(attrs -> 'brand')  as t1,
       pg_typeof(attrs ->> 'brand') as t2
from products where id = 1;

  arrow   | arrow_text |  t1   |  t2  
----------+------------+-------+------
 "Globex" | Globex     | jsonb | text

The first value has quotes because it is still JSON: a JSON string. The second is a plain SQL string. For comparisons and display you almost always want ->>. For chaining deeper you want ->, since you need the result to still be a document. The same operators take an integer to index into an array, and a negative integer counts from the end, so attrs -> 'tags' ->> -1 gave the last tag as text (eco). The details, including what happens with missing keys, are in querying JSONB with the arrow operators.

The path operators reach several levels in one step. #> takes a text array path and returns jsonb, and #>> returns text:

select attrs #>  '{specs,dims}'   as dims,
       attrs #>> '{specs,dims,w}' as w,
       attrs #>  '{tags,1}'       as second_tag
from products where id = 1;

       dims        | w  | second_tag 
-------------------+----+-----------
 {"h": 6, "w": 11} | 11 | "eco"

A path that does not exist returns SQL NULL, not an error. That is convenient and also hides typos: attrs #>> '{specs,dims,zzz}' is NULL, and so is the same query with dimz in place of dims. Extracting nested JSONB values with path operators covers the jsonpath functions that raise errors on request.

Since PostgreSQL 14 you can also subscript: attrs['specs']['color'] returned "blue". It reads like application code, and it returns jsonb, so add a cast or ->> if you need text. Subscripting matters even more for updates, as we will see.

The NULL that is not a NULL

JSON has its own null, and SQL has NULL, and they do not behave the same. '{"x": null}'::jsonb -> 'x' returns the JSONB value null, which is a real value that is not SQL NULL. '{"x": null}'::jsonb ->> 'x' converts it to text and returns SQL NULL. A missing key returns SQL NULL from both operators.

This produces bugs you only meet in production. A filter like where attrs -> 'discount' is null matches rows where the key is missing and misses rows where it is present with a JSON null. A filter on ->> matches both. When the difference matters, jsonb_typeof(attrs -> 'discount') returns the string 'null' for the JSON null and SQL NULL for a missing key, which is the distinction. Decide at the start whether your documents use a JSON null for "unknown", omit the key, or both. Omitting is simpler, and jsonb_strip_nulls converts the other way when you need it: jsonb_strip_nulls('{"a":1,"b":null,"c":{"d":null}}') returned {"a": 1, "c": {}}, and note that the empty object it leaves behind stays.

Filtering

Reading is for the select list. Filtering is where JSONB gets its own operators, and most of them are designed to be answered by an index.

**Containment, @>**, asks whether the left document contains the right one. attrs @> '{"brand":"Acme","in_stock":true}' matched 34,286 rows. The right-hand side is a pattern: every key and value in it must appear in the left, at the same nesting, and the left can have more. For arrays, containment means "has every element listed", in any order: attrs -> 'tags' @> '["eco"]' is true if eco is among the tags.

**Existence, ?, ?| and ?&**, ask about keys. attrs ? 'brand' is true if the top-level key exists, and on an array it asks about string elements. attrs -> 'tags' ? 'eco' matched 100,001 rows of my varied-tag data. ?| means any of the listed keys and ?& means all. These look only at the top level of whatever is on the left, so for a nested key you apply them to the nested value.

**jsonpath, @? and @@**, take a path expression. attrs @? '$.specs ? (@.weight_g > 950)' found 10,878 rows, and attrs @@ '$.price > 450 && $.in_stock == true' found 17,312. jsonpath is the one to use for comparisons and for anything inside arrays of objects.

The classic filter in application code is where attrs ->> 'brand' = 'Acme'. It works and it is fine for a few thousand rows. For large tables it is what you index with an expression index, as we will do shortly.

The cast you cannot avoid

->> returns text, so comparing numbers means casting. Leave the cast out and you compare strings:

select count(*) from products where (attrs ->> 'price')::numeric > 450;  -- 20152
select count(*) from products where  attrs ->> 'price'          > '450'; -- 44207

Both queries ran without complaint and returned different answers. Text comparison puts '51.20' after '450' because '5' sorts after '4'. This is the commonest silent bug in JSONB code, and it is a good reason to wrap fields you compare in a generated column (below). A failing cast is the other hazard: one row with "price": "free" makes the whole ::numeric query fail with invalid input syntax for type numeric. In my table that error appeared when I tried to insert such a document, because I had an expression index on the cast, which made the index a validator by accident. A cleaner way to get the same effect is a CHECK constraint.

Changing a document

You cannot assign into a JSONB value the way you assign into a Python dict. You compute a new document and store it. The tools:

• jsonb_set(target, path, new_value) replaces the value at a path.

• || merges two documents, with the right side winning on conflict. It is a shallow merge.

• - removes a key (or an array element by index or by value), and #- removes by path.

• jsonb_insert adds into arrays at a position.

• Subscript assignment, new in 14: update products set attrs['specs']['color'] = '"gold"'.

Here are the behaviors that are not obvious. Each is from a run on row 1 inside a transaction I rolled back.

jsonb_set(attrs, '{specs,color}', '"teal"') changed the color. The new value is JSONB, which is why the string carries its own quotes. Forgetting that is the first mistake: 'teal' without quotes is not valid JSON and fails.

jsonb_set(attrs, '{specs,origin}', '"DE"') added a new key, because create_missing defaults to true and only the last step of the path may be missing. Then jsonb_set(attrs, '{specs,origin,city}', '"Berlin"') changed nothing, because origin was a string and not an object. Postgres returned the document unchanged with no error and no warning. The same happened with a path whose intermediate key did not exist: jsonb_set('{"a":1}', '{b,c}', '2') returned {"a": 1}. Subscript assignment behaves differently and is friendlier here: attrs['specs']['coating']['type'] = '"matte"' created the whole coating object. Whether that is better depends on whether you would rather create or fail.

attrs || '{"specs": {"color": "pink"}}' did not change the color. It **replaced the whole specs object** with {"color": "pink"} and the dimensions and weight vanished. || is a top-level merge. If you want a deep merge, you have to write it, or use jsonb_set per key.

jsonb_set(attrs, '{price}', null) is the trap that costs people production data. The SQL NULL (not the JSON null) makes the function return SQL NULL, and the whole attrs was set to null, which tripped the not null constraint in my test. If it is possible for the new value to be SQL NULL, use jsonb_set_lax, which has an explicit policy: 'delete_key' removed the key, 'return_target' left the document alone, and 'use_json_null' stored a JSON null.

Removing is simpler: attrs - 'clearance', attrs - array['brand','sku'], attrs #- '{specs,dims,h}'. Removing a key that is not there is a no-op.

The two articles that follow these in detail are updating one key inside a JSONB column and adding and removing keys with jsonb_set and minus.

What an update costs

PostgreSQL writes a new row version for every update, and the new version carries the entire document. Updating one price in a 6 KB document rewrites 6 KB. Updating 10,000 rows of my small table (about 300 bytes each) grew the heap from 56 MB to 59 MB until vacuum could clean up. For small documents this is invisible. For documents that are large and updated often, such as a settings blob with a counter in it, the cost is real: the document is rewritten, and if it is over about 2 KB it also goes through compression and TOAST. A busy counter belongs in its own column.

Arrays and nested lists

JSON arrays of objects are where the data lives in many real documents: order lines, tags, events. The way to work with them in SQL is to unnest them into rows. jsonb_array_elements returns one row per element:

select o.id, e ->> 'sku' as sku, (e ->> 'qty')::int as qty
from orders o
cross join lateral jsonb_array_elements(o.doc -> 'items') e
order by 1, 2;

On three sample orders it returned the three item rows. The order with an empty items array disappears from a cross join, because zero elements means zero rows. If you need that order to appear with null item columns, use left join lateral ... on true, which returned the extra row 3 | NULL. with ordinality adds the position of each element.

When you want to pick columns out of each element in one go, jsonb_to_recordset does the cast and the naming at once:

select o.id, x.*
from orders o, jsonb_to_recordset(o.doc -> 'items') as x(sku text, qty int, price numeric);

It returns typed columns, and a key that is missing from an element becomes NULL, which I saw when I added a coupon column that no item had. A value of the wrong type is an error: jsonb_to_record('{"a": "oops"}') into an int column raised invalid input syntax for type integer: "oops". Two articles go through these: querying arrays inside JSONB and expanding JSONB into rows with jsonb_to_recordset.

To ask whether an array contains an object with a given property, use containment on the array: doc -> 'items' @> '[{"sku": "B2"}]' found order 1, without any unnesting, and a GIN index can answer it.

JSON_TABLE in PostgreSQL 17 and later

JSON_TABLE is the SQL-standard way to do the same unnesting, and it came to PostgreSQL in version 17. It folds the path and the column types into one clause and can nest:

select jt.*
from orders o,
json_table(o.doc, '$' columns (
  cust_id int  path '$.customer.id',
  cust    text path '$.customer.name',
  nested path '$.items[*]' columns (sku text path '$.sku', qty int path '$.qty'),
  has_note boolean exists path '$.note'
)) jt;

The output repeated the customer columns on each item row, and an order with no items kept one row with empty sku and qty. Failures are quiet by default: a text value "x" into an int column gave NULL, and with default -1 on error gave -1 for the bad row while the good row read 12. Use error on error where you want a loud failure. The full treatment is in using JSON_TABLE in PostgreSQL 17 to turn JSON into rows.

Building JSON back out

Documents come out of tables as often as they go in. The builders are jsonb_build_object, jsonb_build_array, to_jsonb(row) and the aggregates jsonb_agg and jsonb_object_agg.

select o.id,
       jsonb_build_object(
         'customer', o.doc #> '{customer,name}',
         'lines', (select coalesce(jsonb_agg(jsonb_build_object(
                     'sku', e ->> 'sku',
                     'total', (e ->> 'qty')::int * (e ->> 'price')::numeric)), '[]')
                   from jsonb_array_elements(o.doc -> 'items') e))
from orders o;

For order 1 it built {"lines": [{"sku": "A1", "total": 19.0}, {"sku": "B2", "total": 20}], "customer": "Ada"}. Two details matter. jsonb_agg over no rows returns SQL NULL, not [], so wrap it in coalesce(..., '[]'). And jsonb_agg(x order by y) is the way to get a defined order inside an array, since key order in objects does not exist but array order does. See aggregating rows into JSON with jsonb_agg for grouping and FILTER.

Indexing

Without an index, every JSONB filter is a sequential scan that reads and unpacks every document. On the 200,000-row table that took 17 ms for a lookup by SKU and 58 ms for a count by brand and stock, using three parallel workers. Those numbers are tolerable at this size and grow in a straight line with the table.

A GIN index for containment and existence

A GIN index turns each key and value in the document into index entries. The default operator class, jsonb_ops, supports @>, ?, ?|, ?&, @? and @@. A second class, jsonb_path_ops, supports only @>, @? and @@ and is smaller and faster for those:

create index products_attrs_gin  on products using gin (attrs);
create index products_attrs_path on products using gin (attrs jsonb_path_ops);

     indexrelid      | pg_size_pretty 
---------------------+----------------
 products_pkey       | 4632 kB
 products_attrs_gin  | 24 MB
 products_attrs_path | 18 MB

Both are large next to a 59 MB table, which is the price of indexing every key. The lookup attrs @> '{"sku":"SKU-123456"}' dropped from 17 ms to 0.03 ms (execution time reported by EXPLAIN ANALYZE, one row, bitmap index scan). The ? query for a key that does not exist took 0.025 ms with the default GIN index and went back to a 17 ms sequential scan when I dropped it and kept only jsonb_path_ops, because that class cannot answer ?. Pick the class from the operators your queries use.

What GIN does not do is answer queries on expressions. This one ignored the index and scanned the table, 19 ms:

select id from products where attrs ->> 'sku' = 'SKU-123456';

Index selectivity matters too. The count of attrs @> '{"brand":"Acme","in_stock":true}' matched 34,286 rows, 17% of the table, and even with the index it took 17 ms, because the bitmap scan still had to visit 7,143 heap pages. A GIN index helps selective queries and does nothing for queries that match a large share of the table. The details are in indexing JSONB with GIN and when it helps.

A btree on the expression

For one or two fields that you filter, sort or join on, a plain btree index on the expression is smaller and faster than GIN:

create index products_sku   on products ((attrs ->> 'sku'));
create index products_price on products (((attrs ->> 'price')::numeric));

The SKU lookup then used an index scan and took 0.058 ms, with an index of 6.2 MB. The numeric range query (attrs ->> 'price')::numeric between 499.9 and 500 returned 43 rows in 0.08 ms. The query has to repeat the exact expression the index was built on: attrs ->> 'price' = '10.00' did not use the numeric index and scanned the table (15 ms), since it compares text. That is another argument for generated columns, which make the expression a name.

Promoting a field with a generated column

A stored generated column computes a value from the document and keeps it in sync. You index and query the column and never repeat the expression:

alter table products add column brand text generated always as (attrs ->> 'brand') stored;
alter table products add column price numeric(8,2) generated always as ((attrs ->> 'price')::numeric) stored;
create index products_brand_price on products (brand, price);

After analyze, the query where brand = 'Acme' and price > 490 used the composite index and returned 772 rows from a bitmap scan. The index was 6.2 MB. When I updated the document with jsonb_set(attrs, '{price}', '12.5'), the generated price column followed it. Adding a stored generated column rewrites the table once, so do it in a quiet period on a big table. This is also the route out of JSONB for a field that has become important, as we will see in the last section.

There is a statistics bonus. The planner keeps no statistics inside a JSONB value, so it guesses. For attrs ->> 'brand' = 'Acme' it estimated 417 rows per worker while 13,333 came back, a factor of 32. Real columns have statistics (rows=23518 against 20,000 for the same filter in a typed copy of the table), and expression indexes get statistics for their expression. A wrong estimate that large can turn a join into a bad plan.

Constraints: JSONB with some structure

A JSONB column accepts any valid JSON, including an array or a number where you expected an object. You can add rules with CHECK constraints:

alter table products add constraint attrs_is_object check (jsonb_typeof(attrs) = 'object');
alter table products add constraint attrs_has_sku
  check (attrs ? 'sku' and jsonb_typeof(attrs -> 'sku') = 'string');

Inserting '[1,2]' and then '{"brand":"Acme"}' both failed with violates check constraint "attrs_has_sku" and showed the offending row. Constraints like these catch the mistakes that otherwise appear later as a missing field in a report. For more structure than a handful of checks, define a JSON Schema in the application and validate before writing (validating JSON with jsonschema in Python shows the approach), and keep the CHECK as the last line of defense for the two or three keys that code depends on. Adding a constraint validates all existing rows, so on a big table add it as NOT VALID first and then VALIDATE CONSTRAINT.

Storage, TOAST and large documents

A row has to fit a page of 8 KB, and PostgreSQL stores values that push a row past about 2 KB out of line in a TOAST table, compressing them first when that helps. For JSONB this matters in two ways. A large document is read and decompressed whenever you touch a key inside it, and it is rewritten whole on update.

I measured it with two kinds of rows in one table: 2,000 with a 6.4 KB document of random text and 2,000 with a tiny document. The big rows stored at 6,436 bytes on average (random hex does not compress) and the table had 256 kB of heap and 16 MB of TOAST. A filter body ->> 'id' = '5' took 7.4 ms over those 4,000 rows, against 0.23 ms for a primary key lookup on the same table. A sum of body ->> 'id' over the big rows took 7.9 ms against 0.5 ms for summing a real column. The scan is detoasting every large value to read one small key.

The practical rule: JSONB is comfortable when documents are small, a few hundred bytes to a couple of kilobytes. When a document grows to tens of kilobytes, put the big, rarely-read parts in a separate table or column and keep the frequently filtered fields where the planner can see them.

Columns or JSONB, with numbers

I built a typed copy of the 200,000-row table, with sku, brand, price, in_stock, weight_g and color as columns, to see the cost of the document model.

 jsonb_table | columns_table 
-------------+---------------
 59 MB       | 17 MB

The group by over brand with the average price took 184 ms and 139 ms on two runs against the JSONB table, and 18 ms and 17 ms against the columns, with the same answers. The typed table is a third of the size, because it stores each key name once, in the catalog, instead of once per row. Constraints are cheaper too: check (price >= 0) rejected the row with price = -1 immediately.

None of this means columns always win. JSONB has no migrations when a new attribute appears, accepts documents whose shape varies by product type, and round-trips whatever an API sends. The cost shows up when you query the data, not when you store it. A fair summary from this experiment is that JSONB made storage flexible and queries 8 to 10 times slower and the table 3.5 times bigger, on documents of 250 bytes where every row has the same keys.

A design checklist

Before you add the column, decide these:

1. Which fields are always present and always queried? Make them columns. The primary key, foreign keys, timestamps, status, and anything you join on do not belong in a document.

2. Which fields vary by row? Those are the document: optional attributes, per-type properties, integration payloads you store as received.

3. Who writes it? If many code paths write the document, add CHECK constraints, or write through one function.

4. How will you query it? If you can name the three queries, create the indexes for them (a btree on an expression or a generated column for equality and ranges, GIN for containment) and no others. A GIN on a table that is written constantly slows every insert.

5. How big can a document get? Set a limit in the application and a check (pg_column_size(attrs) < 65536) if you want the database to enforce it.

6. How will it be changed? Plan the day you rename a key. An update ... set attrs = (attrs - 'old') || jsonb_build_object('new', attrs -> 'old') over millions of rows rewrites every row, so do it in batches.

7. Keep numbers and dates honest. JSON has no date type and its numbers are doubles in many clients; see JSON dates lack a date type and how JSON handles big numbers. Store money as strings or integers of cents, and dates as ISO 8601 strings, and promote them to typed columns when you query them.

Convenient tooling helps on the way in. The JSON formatter and the JSON or CSV to SQL INSERT tool run in the browser, which is useful for shaping a test document before you put it in a table.

When JSONB is the wrong tool

Use JSONB for the part of your data that really is a document. It is the wrong tool for several things that people store in it anyway.

Relationships. If a document holds a list of ids that point to other rows, you have rebuilt a foreign key without the foreign key. There is no referential integrity, no cascade and no join the planner understands. Use a join table. This one is the commonest, and the most expensive to undo.

Fields you filter and sort on all the time. If every query includes attrs ->> 'status' = 'open', status is a column. The planner cannot estimate selectivity inside a document, and the generated-column workaround above shows how much plumbing you need to approximate a column.

Large, frequently updated blobs. The row is rewritten for every change. A counter inside a document turns each increment into a full-document write plus dead tuples to vacuum.

Data with a fixed, well-known schema. If every row has the same twelve keys, you gave up type checking, constraints, statistics and a third of the storage for flexibility you do not use.

Analytics over wide documents. Aggregating over a JSONB field scans and decodes every document, as the 8 to 10 times slowdown showed. If the job is reporting, extract the fields to a columnar format; querying JSON files with DuckDB is the same idea outside Postgres.

The articles on the JSON column type in MySQL and JSON functions in SQLite cover how those engines answer the same questions, and if you are weighing a separate document database, choosing between a JSON column and a document database takes that decision on its own. For the authoritative reference on everything above, use the PostgreSQL manual: JSON types, JSON functions and operators and GIN indexes.