Skip to content
Katabench
Try free
8 min read The Katabench team

PostgreSQL JSONB query patterns: null, paths and GIN

A PostgreSQL JSONB query using ->> cannot tell a missing key from JSON null, and the planner's default guess for that filter was 98 times too low.

Text
 id |  as_text   |  as_jsonb  | has_key | json_type
----+------------+------------+---------+------------
  1 | (SQL NULL) | (SQL NULL) | f       | (SQL NULL)
  2 | (SQL NULL) | null       | t       | null
  3 |            | ""         | t       | string
  4 | CR-881     | "CR-881"   | t       | string
  5 | false      | false      | t       | boolean

Rows 1 and 2 hold different documents. Shipment 1 never had a customs reference, and shipment 2 had one explicitly cleared. A report that filters on the as_text column treats them as the same fact, and a test suite whose sample documents all have the key will never notice. This shipping table is the running example for this post:

SQL
CREATE TABLE shipments (id integer PRIMARY KEY, doc jsonb NOT NULL);

INSERT INTO shipments VALUES
    (1, '{"carrier": "dhl"}'),
    (2, '{"carrier": "dhl", "customs_ref": null}'),
    (3, '{"carrier": "ups", "customs_ref": ""}'),
    (4, '{"carrier": "ups", "customs_ref": "CR-881"}'),
    (5, '{"carrier": "dpd", "customs_ref": false}');

SELECT id,
       doc ->> 'customs_ref' AS as_text,
       doc -> 'customs_ref' AS as_jsonb,
       doc ? 'customs_ref' AS has_key,
       jsonb_typeof(doc -> 'customs_ref') AS json_type
FROM shipments
ORDER BY id;

Missing, JSON null and SQL NULL are three states

The PostgreSQL JSON type documentation lists JSON null with the remark that SQL NULL is a different concept. The operators make that difference visible:

  • ->> returns text. A missing key and a JSON null both become SQL NULL.
  • -> returns jsonb. A missing key is SQL NULL, while a JSON null is a non-null jsonb value that prints as null.
  • ? returns a boolean for whether the key exists at the top level, and it does not care what the value is.

Three operators, three different answers about one key

{"carrier": "dhl"}

doc ->> 'customs_ref'
SQL NULL
doc -> 'customs_ref'
SQL NULL
doc ? 'customs_ref'
false

{"customs_ref": null}

doc ->> 'customs_ref'
SQL NULL
doc -> 'customs_ref'
JSON null
doc ? 'customs_ref'
true

{"customs_ref": ""}

doc ->> 'customs_ref'
empty text
doc -> 'customs_ref'
""
doc ? 'customs_ref'
true

{"customs_ref": "CR-881"}

doc ->> 'customs_ref'
CR-881
doc -> 'customs_ref'
"CR-881"
doc ? 'customs_ref'
true
The first two documents look identical through the text operator. The jsonb operator and the existence check still tell them apart.

Filters that look interchangeable give different counts:

On five shipments, doc ->> 'customs_ref' IS NULL matched 2 rows and doc -> 'customs_ref' IS NULL matched 1.

To find the explicit clears alone, doc -> 'customs_ref' = 'null'::jsonb and doc @> '{"customs_ref": null}' both return shipment 2. Shipment 5 stays out of all of them, because its value is the boolean false, which ->> renders as the text false.

Casts fail where operators do not

Extraction is forgiving and casting is not. The manual says the extraction operators return NULL rather than failing when the JSON does not have the right structure, but a cast runs against whatever text came out. Here is a table with nested parcels, one of which has dirty data:

SQL
CREATE TABLE manifests (id integer PRIMARY KEY, doc jsonb NOT NULL);

INSERT INTO manifests VALUES
  (1, '{"parcels": [{"sku": "BATTERY-9", "hazmat": true, "kg": 4},
                    {"sku": "ANVIL", "hazmat": false, "kg": 38}]}'),
  (2, '{"parcels": [{"sku": "SOLVENT-2", "hazmat": true, "kg": 31}]}'),
  (3, '{"parcels": [{"sku": "LAMP", "kg": "n/a"}]}');

SELECT (doc -> 'parcels' -> 0 ->> 'kg')::numeric FROM manifests;
Text
ERROR:  invalid input syntax for type numeric: "n/a"

One bad document fails the whole statement, and it may be the first time anyone learns the value exists. A jsonpath comparison handles this differently. doc @? '$.parcels[*] ? (@.kg > 20)' returns true, true and false for the three rows. Comparing a string with a number evaluates to unknown, and a filter keeps only true, so jsonb_path_exists in strict mode gives the same three answers with no error. That is convenient, and it also means dirty data disappears from the result, so decide which behavior your report wants.

Conditions on the same array element

"Has a hazardous parcel over 20 kg" is a question about a single array element. Writing it as two independent tests asks a different question:

SQL
-- Two conditions, any elements: returns 1 and 2
SELECT id FROM manifests
WHERE doc @@ '$.parcels[*].hazmat == true && $.parcels[*].kg > 20';

-- One filter on each element: returns 2
SELECT id FROM manifests
WHERE doc @? '$.parcels[*] ? (@.hazmat == true && @.kg > 20)';

Manifest 1 has a light battery that is hazardous and a heavy anvil that is not, so the first query reports a hazardous heavy parcel that does not exist. The second puts both tests inside one filter, and @ then refers to the same element for each.

Containment follows the same rule for equality. @> with one object in the pattern array needs one element to match all of that object, while two objects can match different elements. On this table doc @> '{"parcels": [{"hazmat": true, "sku": "ANVIL"}]}' returns nothing, and doc @> '{"parcels": [{"hazmat": true}, {"sku": "ANVIL"}]}' returns manifest 1.

User input belongs in the vars argument, not in the path string. The JSON functions reference describes it as a JSON object whose fields are substituted into the path:

SQL
PREPARE heavy_hazmat(numeric) AS
SELECT id FROM manifests
WHERE jsonb_path_exists(
    doc,
    '$.parcels[*] ? (@.hazmat == true && @.kg > $min)',
    jsonb_build_object('min', $1));

What a GIN index can answer

The numbers in this section come from 200,000 shipment documents with one to three parcels each, in postgres:17 under Docker on a laptop, one run per query. The default jsonb_ops index came to 4,448 kB. jsonb_path_ops came to 3,120 kB, 30% smaller, and matched the manual's advice that it is usually smaller and more specific. The containment query below took 2.4 ms with jsonb_ops and 1.1 ms with jsonb_path_ops. With no index it took 67 ms across parallel workers.

The size is only half the choice, because the operator classes support different queries. Plans with jsonb_path_ops in place:

Predicate Plan Time
doc @> '{"parcels":[{"sku":"SKU-77"}]}' Bitmap index scan 1.1 ms
doc @? '$.parcels[*] ? (@.sku == "SKU-77")' Bitmap index scan 1.0 ms
Same path plus && @.kg > 30 Bitmap index scan, recheck drops 267 rows 1.2 ms
doc @? '$.parcels[*] ? (@.kg > 39)' Sequential scan 141 ms
jsonb_path_exists(doc, ...) with the same path Sequential scan 113 ms
doc ? 'nope' Sequential scan 52 ms

The table follows from one sentence in the manual: for @? and @@, a GIN index extracts clauses of the form accessors_chain == constant. An equality gives the index something to look up, and a range does not. The function form used a sequential scan even with an identical path, because the index support belongs to the operator. And ? needs jsonb_ops, as the docs state.

Since vars exists only on the function, a parameterized path cannot reach the index by itself. An indexable test on the equality part has to sit next to it and narrow the candidates first. In one such query, that took 534 candidates from a bitmap index scan down to 267 rows in 3.6 ms. If you want to see where else query shape decides the plan, reading EF Core query plans walks through the same habit from the ORM side.

The planner guesses about expressions it has no statistics for

The carrier value dhl appears in 98,000 of the 200,000 documents. Here is what the planner expected for the same filter in three setups, with parallelism off so the numbers are whole-table:

Text
Filter                                     Estimated rows   Actual rows
doc ->> 'carrier' = 'dhl', no index                 1,000        98,000
Same, with expression index on doc ->> 'carrier'   98,213        98,000
carrier = 'dhl', generated stored column           99,093        98,000

A 98-times underestimate can steer the planner to the wrong join strategy once this filter feeds a join. The fix does not have to be a schema redesign. ANALYZE gathers statistics on an expression once an index covers it, as the second row shows, and expression indexes have their own post. A generated column gets ordinary column statistics too:

SQL
ALTER TABLE doc_shipments
    ADD COLUMN carrier text GENERATED ALWAYS AS (doc ->> 'carrier') STORED;

Containment estimated well here, with doc @> '{"carrier":"dhl"}' at 97,486 rows and no extra work. It was less accurate on the rare SKU value, at 19 estimated against 534 actual rows, so check your own data with EXPLAIN ANALYZE instead of assuming either way.

Which keys should become columns

A document costs more than a column even when you never query it. Two short fields across 200,000 rows took 9,448 kB as text columns and 15 MB as JSONB, because every row repeats the key names. The manual also warns that any update takes a row-level lock on the whole row, which matters when unrelated writers touch different keys of one large document.

A column also gives you constraints. A numeric type would have rejected the "n/a" weight on the way in, NOT NULL removes the missing-or-null question for that field, and a foreign key can check what a document can only mention.

So promote a key to a column when it is always present, when you filter, join or sort on it, or when another table depends on it. Keep JSONB for the parts whose shape truly varies per row, such as carrier-specific payloads, and for data you store verbatim from someone else's API. A generated column is a good first step for a key that has already earned it, since it keeps writers unchanged.

The jsonb_path_exists filter above returned the right rows from a 113 ms sequential scan. On Katabench, database exercises grade that too, through query-plan rules on a throwaway PostgreSQL database.

Practice Databases & EF Core

More like this: EF Core practice →

Get new katas and .NET tips in your inbox

A short note when fresh kata land, plus the C# and performance tricks behind the grading. No spam, unsubscribe anytime.