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.
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 | booleanRows 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:
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;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 JSONnullboth become SQLNULL.->returnsjsonb. A missing key is SQLNULL, while a JSONnullis a non-nulljsonbvalue that prints asnull.?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
Filters that look interchangeable give different counts:
On five shipments,
doc ->> 'customs_ref' IS NULLmatched 2 rows anddoc -> 'customs_ref' IS NULLmatched 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.
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:
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;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.
"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:
-- 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:
PREPARE heavy_hazmat(numeric) AS
SELECT id FROM manifests
WHERE jsonb_path_exists(
doc,
'$.parcels[*] ? (@.hazmat == true && @.kg > $min)',
jsonb_build_object('min', $1));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 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:
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,000A 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:
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.
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
- Open in the editor: Filter Before You Fetch
EF Core and SQL Easy Free, no account needed
Filter Before You Fetch
Filter measurements on the database, not after loading the whole table.
- Open the Pro kata: Absent, Null, or Empty?
EF Core and SQL Easy Katabench Pro
Absent, Null, or Empty?
Distinguish a missing JSON key, explicit JSON null, an empty string, and every other value before grouping the audit.
More like this: EF Core practice →