Postgres – JSON and JSONB

Postgres has two JSON types. json stores the text you gave it, exactly, including whitespace and duplicate keys. jsonb parses it into a binary form: keys are sorted, duplicates dropped, whitespace gone — and it is the only one you can index usefully.

Use jsonb. The only reason for json is needing the document back byte-for-byte as it arrived.

Getting values out

SELECT '{"guests": 4, "pets": true, "arrival": {"time": "15:00", "flight": "NZ102"}}'::jsonb AS doc,
       '{"guests": 4}'::jsonb -> 'guests'       AS arrow,        -- 4      (jsonb)
       '{"guests": 4}'::jsonb ->> 'guests'      AS double_arrow, -- "4"    (text)
       '{"a": {"b": {"c": 1}}}'::jsonb #> '{a,b}'   AS path,
       '{"a": {"b": {"c": 1}}}'::jsonb #>> '{a,b,c}' AS path_text;

The one-arrow versus two-arrow distinction is where the time goes. -> on a number gives you jsonb, and comparing that to an SQL integer needs a cast; ->> gives text, and comparing that to a number needs a cast the other way. Decide at the end of the chain what type you actually want:

SELECT ('{"guests": 4}'::jsonb ->> 'guests')::int > 2 AS more_than_two;

Searching

SELECT '{"pets": true, "guests": 4}'::jsonb @> '{"pets": true}'::jsonb  AS contains,
       '{"pets": true}'::jsonb ? 'pets'                                 AS has_key,
       '{"a": 1, "b": 2}'::jsonb ?| array['b','z']                      AS has_any,
       '{"a": 1, "b": 2}'::jsonb ?& array['a','b']                      AS has_all;

@> is the important one. "Contains this structure", at any depth, and it is the operator a GIN index can answer. Reach for it rather than pulling a key out with ->> and comparing, because the second form cannot use the index.

Indexing

Without an index, every jsonb query reads every row and parses every document. There are two kinds worth knowing and they are not interchangeable.

-- 1. the whole document, for @> and ? queries
CREATE INDEX ix_outbox_payload ON outbox USING gin (payload);

-- 2. smaller and faster, but ONLY supports @>
CREATE INDEX ix_outbox_payload_ops ON outbox USING gin (payload jsonb_path_ops);

-- 3. one key, as if it were a column — a plain b-tree
CREATE INDEX ix_outbox_topic ON outbox ((payload ->> 'bookingId'));

The third is the one people miss. If you always query the same key, an expression index on that key is far smaller than a GIN index over the whole document and supports ranges and sorting, which GIN does not.

The query has to match the index's shape exactly. An index on (payload ->> 'bookingId') is used by WHERE payload ->> 'bookingId' = '42' and not by WHERE (payload ->> 'bookingId')::int = 42, because the cast makes it a different expression.

What jsonb changes about your data on the way in

Because jsonb parses rather than stores text, a document does not come back the way it went in:

SELECT '{"b": 1, "a": 2, "b": 3}'::json  AS as_json,
       '{"b": 1, "a": 2, "b": 3}'::jsonb AS as_jsonb;

         as_json          |     as_jsonb
--------------------------+------------------
 {"b": 1, "a": 2, "b": 3} | {"a": 2, "b": 3}

Keys sorted, whitespace normalised, and the duplicate b resolved to the last one. All three are usually improvements, and together they are why jsonb can be compared and indexed at all while json cannot.

Numbers are the corner worth knowing. jsonb stores them as numeric, so a large integer keeps its precision — better than JavaScript manages — but trailing zeros are part of a numeric, so 1.10 is rendered back as 1.10:

SELECT '{"x": 1.10}'::jsonb                            AS printed,
       '{"x": 1.10}'::jsonb = '{"x": 1.1}'::jsonb      AS equal_as_jsonb,
       '{"x": 1.10}'::jsonb::text = '{"x": 1.1}'::jsonb::text AS equal_as_text;

   printed   | equal_as_jsonb | equal_as_text
-------------+----------------+---------------
 {"x": 1.10} | t              | f

The two documents are equal, and their texts are not. Compare jsonb as jsonb — casting to text to check whether something changed is a bug that only shows up on the rows where it matters.

Modifying a document

SELECT jsonb_set('{"guests": 4, "pets": true}'::jsonb, '{guests}', '6')          AS updated,
       '{"guests": 4}'::jsonb || '{"pets": false}'::jsonb                        AS merged,
       '{"guests": 4, "pets": true}'::jsonb - 'pets'                             AS key_removed,
       jsonb_set('{"a": {}}'::jsonb, '{a,b}', '"new"', true)                     AS created;

jsonb_set's fourth argument decides whether a missing key is created; it defaults to true. Without it, setting a path that does not exist silently returns the document unchanged — no error, no new key.

Every one of these returns a new document. Updating one key means rewriting the whole column, and therefore the whole row. On a large document updated often, that is the cost that makes jsonb the wrong choice.

Turning JSON into rows, and back

-- expand an array into rows
SELECT value ->> 'name' AS guest, (value ->> 'age')::int AS age
FROM   jsonb_array_elements('[{"name":"Ana","age":34},{"name":"Ben","age":9}]'::jsonb);

-- build JSON from rows: a property and its bookings in one round trip
SELECT jsonb_build_object(
           'id',    p.id,
           'title', p.title,
           'stays', (SELECT jsonb_agg(jsonb_build_object('checkIn', b.check_in, 'total', b.total)
                                      ORDER BY b.check_in)
                     FROM   bookings b WHERE b.property_id = p.id AND b.check_in > DATE '2025-01-01')
       ) AS document
FROM   properties p
WHERE  p.id = 42;

The second shape is worth knowing well. It replaces an N+1 query — fetch the parent, then loop fetching children — with one statement returning one document, assembled by the database.

jsonb_path_query

SQL/JSON path expressions, standardised in SQL:2016, for anything a chain of arrows gets unwieldy for:

SELECT jsonb_path_query_array(
           '{"guests":[{"name":"Ana","age":34},{"name":"Ben","age":9}]}'::jsonb,
           '$.guests[*] ? (@.age >= 18).name') AS adults;

@@ tests a path predicate and is GIN-indexable, which makes it the right tool when a containment check is not expressive enough.

When jsonb is the wrong answer

This is the section that matters. jsonb is excellent for data that is genuinely document-shaped — a webhook payload, an audit record, per-tenant settings, an outbox message — where the shape varies and you mostly read the whole thing.

The booking schema uses it in exactly one place: the outbox table's payload. That is the right sort of use. Every message on that table has a different shape depending on its topic, nothing joins to the inside of a payload, each row is written once and read once, and the alternative would be a column per field of every event type the system will ever publish. Everything else in that schema — prices, dates, statuses, guest counts — is a column, and none of it would be better as JSON.

It is the wrong choice when any of these are true:

• Every row has the key. Then it is a column, and a column gets a type, a NOT NULL, a check constraint, a default and a cheap b-tree index. jsonb gives you none of those.
• You join on it, or sort by it, or aggregate it. Every one of those needs a cast per row unless you have built exactly the right expression index.
• It has a foreign key in it. A propertyId inside a document references nothing. Nothing stops it pointing at a property that no longer exists.
• It is updated frequently. Every change rewrites the whole document.

The middle path is the one most schemas should take: promote the keys you query to real columns, keep the rest in a jsonb column beside them. Postgres 12 makes that cheap, because a generated column can extract the key and stay in step automatically:

ALTER TABLE outbox
    ADD COLUMN booking_id bigint
    GENERATED ALWAYS AS ((payload ->> 'bookingId')::bigint) STORED;

Now it is an ordinary column — indexable, sortable, and typed — computed from the document you already store, with no application code to keep them agreeing.